Here we add “spcrud.ts” library with CRUD methods to save JSON data over REST api into SharePoint Lists. SPCRUD is a Typescript library with methods to easily interact with SharePoint APIs by providing management of Digest Token, HTTP headers, and URL formatting. As developers we interact with simple TypeScript methods that have input validation which in turn execute the correct underlying HTTP call.
While reviewing the SharePoint Migration Assessment Tool (SMAT) I noticed the high performance data gathering. Running on a farm with thousands of sites the collection of data ran quickly.
Looking more closely we can see how Content Database SQL queries are used to collect a list of compatibility issues and populate the report. These are much faster than Server Object Model (SOM) front end techniques for collecting data.
Below are sample SQL queries that SMAT uses to check SharePoint content database and identify customizations that might not be compatible with Office 365.
SELECT Distinct(WebId), SiteId
FROM [dbo].[ImmedSubscriptions] with (nolock)
INNER JOIN [dbo].[AllSites] with (nolock)
ON AllSites.Id = ImmedSubscriptions.SiteId
WHERE AllSites.BitFlags & 1 = 0 AND AllSites.BitFlags & 2 = 0
Union
SELECT Distinct(WebId), SiteId
FROM [dbo].[SchedSubscriptions] with (nolock)
INNER JOIN [dbo].[AllSites] with (nolock)
ON AllSites.Id = SchedSubscriptions.SiteId
WHERE AllSites.BitFlags & 1 = 0 AND AllSites.BitFlags & 2 = 0
ORDER BY SiteId
AppsScanner
SELECT Distinct UserInfo.tp_Login AS OwnerLogin,
UserInfo.tp_Title AS OwnerTitle,
AppPackages.Title AS AppTitle,
CASE WHEN AppSourceInfo.AppSource = 1
THEN 'Marketplace'
WHEN AppSourceInfo.AppSource = 2
THEN 'CorporateCatalog'
WHEN AppSourceInfo.AppSource = 3
THEN 'DeveloperSite'
WHEN AppSourceInfo.AppSource = 4
THEN 'ObjectModel'
WHEN AppSourceInfo.AppSource = 5
THEN 'RemoteObjectModel'
END AS AppSource,
AppPackages.SiteId,
AppInstallations.Id AS AppId,
AppInstallations.WebId,
AppInstallations.LaunchUrl,
AppInstallations.CreationTime,
AppInstallations.RemoteAppUrl,
AppInstallations.SettingsPageUrl,
Webs.Title AS WebSiteTitle,
Webs.FullUrl AS WebURL,
CASE WHEN (LEN(Docs.DirName) > 0 AND LEN(Docs.LeafName) > 0)
THEN Docs.DirName+'/'+ Docs.LeafName
WHEN (LEN(Docs.DirName) = 0 AND LEN(Docs.LeafName) > 0)
THEN '/' + Docs.LeafName
ELSE 'No App Part Placed'
END AS PageURL
FROM dbo.AppPackages WITH(NOLOCK)
INNER JOIN dbo.AppInstallations WITH(NOLOCK)
ON AppPackages.SiteId = AppInstallations.SiteId
INNER JOIN dbo.AppSourceInfo WITH (NOLOCK)
ON AppPackages.PackageFingerprint = AppSourceInfo.PackageFingerprint
AND AppInstallations.SourceInfoId = AppSourceInfo.SourceInfoId
AND AppInstallations.SiteId = AppSourceInfo.SiteId
INNER JOIN dbo.Sites WITH (NOLOCK)
ON AppInstallations.SiteId = Sites.Id
INNER JOIN dbo.Webs WITH (NOLOCK)
ON Webs.Id = AppInstallations.WebId
INNER JOIN dbo.UserInfo WITH (NOLOCK)
ON UserInfo.tp_SiteID= Sites.Id
AND UserInfo.tp_ID = Sites.OwnerID
Left Outer Join AllWebParts With (NoLock)
ON AllWebParts.tp_SolutionId = AppPackages.ProductId
AND AllWebParts.tp_SiteId = AppPackages.SiteId
Left Outer Join Docs WITH (NoLock)
ON Docs.Id = AllWebParts.tp_PageUrlID
AND Docs.SiteId = AppInstallations.SiteId
AND Docs.WebId = AppInstallations.WebId
WHERE (UserInfo.tp_SiteAdmin = 1
AND Sites.Deleted= 0
AND Webs.DeleteTransactionId= 0x)
AND Sites.BitFlags & 1 = 0
AND Sites.BitFlags & 2 = 0
BrowserFileHandlingScanner
SELECT AD.SiteId, AD.DirName + N'/' + AD.LeafName as FilePath, AD.TimeCreated, AD.TimeLastModified, (UI.tp_Title + '['+UI.tp_Login+']') as Editor
FROM AllDocs AS AD with (nolock)
INNER JOIN AllSites as Sites with (nolock)
ON Sites.Id = AD.SiteId
INNER JOIN AllUserData as AUD with (nolock)
ON (AD.DocLibRowID = AUD.tp_ID
and Sites.Id = AUD.tp_SiteID and AD.ListID = AUD.tp_ListId)
INNER JOIN UserInfo as UI with (nolock)
ON (AUD.tp_Editor = UI.tp_ID
AND Sites.Id = UI.tp_SiteID)
WHERE (AD.LeafName LIKE '%.htm' or AD.LeafName LIKE '%.html')
AND AD.SetupPath IS NULL
AND AD.ListId IS NOT NULL
AND AD.IsCurrentVersion = 1
AND LEN(AD.DeleteTransactionID) <= 0
AND Sites.BitFlags & 1 = 0
AND Sites.BitFlags & 2 = 0
AND AUD.tp_IsCurrent = 1
ORDER BY SiteId
CheckedOutFilesScanner
SELECT SiteId, '/' + DirName + '/' + LeafName as FilePath
FROM [dbo].[AllDocs] AS Docs with (NOLOCK)
INNER JOIN [dbo].[AllSites] with (NOLOCK)
ON AllSites.Id = Docs.SiteId
WHERE CheckoutUserId is not null AND CheckoutExpires is null AND AllSites.BitFlags & 1 = 0 AND AllSites.BitFlags & 2 = 0
GROUP BY SiteId, '/' + DirName + '/' + LeafName
CustomizedPagesScanner
SELECT SiteId,
CASE WHEN Len(DirName) = 0
THEN '/' + LeafName
ELSE '/' + DirName + '/' + LeafName
END as FilePath
FROM [dbo].[AllDocs] with (NoLock)
INNER JOIN [dbo].[AllSites] with (nolock)
ON AllSites.Id = AllDocs.SiteId
WHERE DeleteTransactionId = 0x
AND Level = 1
AND SetupPath IS NOT NULL
AND (DocFlags & 64) <> 0
AND AllSites.BitFlags & 1 = 0
AND AllSites.BitFlags & 2 = 0
Group By SiteId, DirName, LeafName
Order By SiteId, DirName, LeafName
CustomProfilePropertyMappingScanner
SELECT Count(PV.[PropertyID]) AS CountPropertyUsed, PL.PropertyName
FROM {0}.[UserProfileValue] AS PV with(nolock)
INNER JOIN {0}.[PropertyList] AS PL with(nolock)
ON PV.PropertyID = PL.PropertyID
GROUP BY PV.PropertyID, PropertyName
SELECT PropList.PropertyName AS PropertyName,
PropList.[Length] AS PropertyLength,
PropMap.DCAttribute AS PropertyMapping
FROM {1}.[ADImportPropertyMapping] AS PropMap WITH (NOLOCK)
INNER JOIN {1}.[PropertyList] AS PropList WITH (NOLOCK)
ON PropMap.PropertyID = PropList.PropertyID
INNER JOIN [dbo].[ADImportDCMapping] AS DCMapping WITH (NOLOCK)
ON DCMapping.DCId = propmap.DCId
WHERE DCMapping.ConnectionName = '{0}'
GROUP BY DCMapping.DCName, PropMap.DCAttribute, PropList.PropertyName, PropList.[Length]
EmailEnabledListsScanner
SELECT [tp_SiteId] as SiteID,
[tp_WebId] as WebID,
[tp_ID] as ListId
FROM [AllLists] with (nolock)
INNER JOIN [AllSites] with (nolock)
ON AllLists.tp_SiteId = AllSites.Id
WHERE LEN(tp_EmailAlias) > 0
AND AllLists.tp_DeleteTransactionId = 0x
AND AllSites.BitFlags & 1 = 0
AND AllSites.BitFlags & 2 = 0
GROUP BY tp_SiteId, tp_WebId, tp_ID
EmailEnabledListsScanner2010
SELECT [SiteId] as SiteID,
[tp_WebId] as WebID,
[tp_ID] as ListId
FROM [AllLists] with (nolock)
INNER JOIN AllWebs with (nolock)
ON AllWebs.Id = alllists.tp_webid
INNER JOIN [AllSites] with (nolock)
ON AllWebs.SiteId = AllSites.Id
WHERE LEN(tp_EmailAlias) > 0
AND AllLists.tp_DeleteTransactionId = 0x
AND AllSites.BitFlags & 1 = 0
AND AllSites.BitFlags & 2 = 0
GROUP BY SiteId, tp_WebId, tp_ID
FileVersionsScanner
SELECT adv.SiteId, Count(adv.Id) as VersionCount, DirName + N'/' + LeafName as FilePath
FROM [dbo].[AllDocVersions] as adv with (nolock)
INNER JOIN [dbo].[AllDocs] as ad with (nolock)
ON ad.Id = adv.Id
INNER JOIN [dbo].[AllSites] with (nolock)
ON AllSites.ID = ad.SiteId
WHERE AllSites.BitFlags & 1 = 0
AND AllSites.BitFlags & 2 = 0
Group By adv.SiteId, adv.Id, DirName, LeafName
Having Count(adv.Id) > {0}
InfoPathScanner
SELECT SiteId, WebId, DirName, LeafName, AllDocs.Id as DocId
FROM AllDocs (nolock)
INNER JOIN AllSites with (nolock)
ON AllSites.Id = AllDocs.SiteId
WHERE Extension = 'xsn'
AND DeleteTransactionId = 0x
AND HasStream = 1
AND AllSites.BitFlags & 1 = 0
AND AllSites.BitFlags & 2 = 0
ORDER BY SiteId, WebId, DirName
IRMEnabledLibraryScanner
SELECT lists.SiteId, lists.WebId, lists.Title as ListTitle
FROM (SELECT tp_Flags & 0x8000000000 as IRMEnabled,
tp_SiteId as SiteID,
tp_WebId as WebID,
tp_Title as Title
FROM [AllLists] with (nolock)) lists
INNER JOIN [AllSites] with (nolock)
ON lists.SiteId = AllSites.Id
WHERE lists.IRMEnabled <> 0
AND AllSites.BitFlags & 1 = 0
AND AllSites.BitFlags & 2 = 0
ORDER BY SiteId
LargeExcelFilesScanner
SELECT AD.SiteId, AD.DirName + N'/' + AD.LeafName as FilePath, AD.Size / (1024 * 1024) as FileSizeInMB
FROM AllDocs AS AD with(nolock)
INNER JOIN AllSites with (nolock)
ON AllSites.Id = AD.SiteId
WHERE AD.LeafName LIKE '%.xlsx'
AND AD.Size / (1024 * 1024) > {0}
AND BitFlags &1 = 0
AND BitFlags &2 = 0
AND AD.IsCurrentVersion = 1
AND LEN(AD.DeleteTransactionID) <= 0
Group By AD.SiteId, AD.DirName, AD.LeafName, AD.Size
LargeListsScanner
SELECT AllLists.tp_SiteId as SiteId, AllLists.tp_WebId as WebId, AllListsAux.ListId
FROM [AllListsAux] with (nolock)
INNER JOIN [AllLists] with (nolock)
ON AllListsAux.ListID = AllLists.tp_ID
INNER JOIN [AllSites] with (nolock)
ON AllListsAux.SiteId = AllSites.Id
WHERE AllListsAux.ItemCount > {0}
AND AllLists.tp_DeleteTransactionId = 0x
AND AllSites.BitFlags & 1 = 0
AND AllSites.BitFlags & 2 = 0
GROUP BY tp_SiteId, tp_WebId, ListId
LargeListsScanner2010
SELECT [AllWebs].SiteId as SiteId, AllLists.tp_WebId as WebId, AllListsAux.ListId
FROM [AllListsAux] with (nolock)
INNER JOIN [AllLists] with (nolock)
ON AllListsAux.ListID = AllLists.tp_ID
INNER JOIN [AllWebs] with (nolock)
ON AllLists.tp_WebId = [AllWebs].Id
INNER JOIN [AllSites] with (nolock)
ON [AllWebs].SiteId = [AllSites].Id
WHERE AllListsAux.ItemCount > 5000
AND AllLists.tp_DeleteTransactionId = 0x
AND AllSites.BitFlags & 1 = 0
AND AllSites.BitFlags & 2 = 0
GROUP BY SiteId, tp_WebId, ListId
LargeSitesScanner
SELECT [Id] as SiteId, [DiskUsed]/Power(1024,3) as SizeInGB
FROM [dbo].[AllSites] with (nolock)
Where [DiskUsed]/Power(1024,3) > {0}
AND BitFlags & 1 = 0
AND BitFlags & 2 = 0
LockedSitesScanner
SELECT Id
FROM AllSites with (nolock)
WHERE BitFlags & 1 > 0
AND BitFlags & 2 > 0
LongODBUrlsScanner
DECLARE @Length256 AS int = 256-{0}
DECLARE @Length260 AS int = 260-{0}
DECLARE @Length270 AS int = 270-{0}
SELECT AllDocs.SiteId, DirName,
LeafName
FROM [dbo].[AllDocs] with (NoLock)
INNER JOIN [dbo].[AllSites] with (nolock)
ON AllDocs.SiteId = AllSites.Id
INNER JOIN [dbo].[AllWebs] with (nolock)
ON AllSites.RootWebId = AllWebs.Id
WHERE (LEN(DirName) > @Length256
OR LEN([DirName] + N'/' + [LeafName]) > @Length260)
AND AllSites.BitFlags & 1 = 0
AND AllSites.BitFlags & 2 = 0
AND AllWebs.WebTemplate = 21
SELECT NavNodes.SiteId,
LEN(Url) AS UrlLength,
Eid AS NodeId,
WebId
FROM [dbo].[NavNodes] with (NoLock)
INNER JOIN [dbo].[AllSites] with (nolock)
ON AllSites.Id = NavNodes.SiteID
INNER JOIN [dbo].[AllWebs] with (nolock)
ON AllSites.RootWebId = AllWebs.Id
WHERE LEN(Url) > @Length260
AND AllSites.BitFlags & 1 = 0
AND AllSites.BitFlags & 2 = 0
AND AllWebs.WebTemplate = 21
SELECT Perms.SiteId,
LEN(ScopeUrl) AS ScopeUrlLength,
ScopeUrl,
WebId
FROM [dbo].[Perms] with (NoLock)
INNER JOIN [dbo].[AllSites] with (nolock)
ON AllSites.Id = Perms.SiteID
INNER JOIN [dbo].[AllWebs] with (nolock)
ON AllSites.RootWebId = AllWebs.Id
WHERE LEN(ScopeUrl) > @Length260
AND AllSites.BitFlags & 1 = 0
AND AllSites.BitFlags & 2 = 0
AND AllWebs.WebTemplate = 21
SELECT Deps.SiteId,
LEN(Deps.FullUrl) AS FullUrlLength,
LEN(DepDesc) AS DepDescLength,
Deps.FullUrl,
DepDesc
FROM [dbo].[Deps] with (NoLock)
INNER JOIN [dbo].[AllSites] with (nolock)
ON AllSites.Id = Deps.SiteID
INNER JOIN [dbo].[AllWebs] with (nolock)
ON AllSites.RootWebId = AllWebs.Id
WHERE (LEN(Deps.FullUrl) > @Length260
OR LEN(DepDesc) > @Length270)
AND AllSites.BitFlags & 1 = 0
AND AllSites.BitFlags & 2 = 0
AND AllWebs.WebTemplate = 21
NonDefaultMasterPageScanner
SELECT w.SiteID,
w.ID as WebID,
w.Title,
w.MasterUrl,
w.CustomMasterUrl
FROM AllWebs w with (nolock)
Inner JOIN AllSites s with (nolock)
ON s.Id = w.SiteId
WHERE ((w.WebTemplate = 54 or w.WebTemplate = 21)
and (w.MasterUrl not like '%_catalogs/masterpage/mysite15.master'
or w.CustomMasterUrl not like '%_catalogs/masterpage/mysite15.master'))
or ((w.WebTemplate <> 54 and w.WebTemplate <> 21)
and (w.MasterUrl not like '%_catalogs/masterpage/seattle.master'
or w.CustomMasterUrl not like '%_catalogs/masterpage/seattle.master'))
AND s.BitFlags & 1 = 0
AND s.BitFlags & 2 = 0
SandboxSolutionScanner
SELECT DISTINCT SOLUTIONS.SITEID,
SOLUTIONS.SOLUTIONID,
SOLUTIONS.NAME,
SOLUTIONS.HASH,
SOLUTIONS.HASASSEMBLIES,
SOLUTIONS.STATUS,
SITEINFO.SITECOLLECTIONURL,
ALLDOCS.TIMECREATED,
ALLDOCS.TIMELASTWRITTEN,
USERINFO.TP_LOGIN AS 'USERLASTWRITTEN',
CASE WHEN SOLUTIONS.HASASSEMBLIES = 1 THEN 'CUSTOM CODE'
WHEN SOLUTIONS.HASASSEMBLIES = 0 AND SOLUTIONFILES.SOLUTIONFILEPATH LIKE '%Onet.xml' THEN 'SITE TEMPLATE'
ELSE 'DESIGN PACKAGE'
END AS SOLUTIONTYPE
FROM SOLUTIONS WITH (NOLOCK)
INNER JOIN (SELECT DISTINCT SITES.ID AS SITEID,
CASE
WHEN SITES.HOSTHEADER IS NOT NULL AND PARENTWEBS.FULLURL='' THEN SITES.HOSTHEADER
WHEN SITES.HOSTHEADER IS NOT NULL THEN SITES.HOSTHEADER + '/' + PARENTWEBS.FULLURL
ELSE '/' + PARENTWEBS.FULLURL END AS SITECOLLECTIONURL
FROM ALLWEBS AS CHILDWEBS WITH (NOLOCK)
INNER JOIN ALLSITES AS SITES WITH (NOLOCK) ON CHILDWEBS.SITEID = SITES.ID
INNER JOIN ALLWEBS AS PARENTWEBS WITH (NOLOCK) ON SITES.ROOTWEBID = PARENTWEBS.ID
WHERE SITES.DELETED = CONVERT(BIT, 0)
AND CHILDWEBS.DELETETRANSACTIONID = 0X
AND PARENTWEBS.DELETETRANSACTIONID = 0X
AND SITES.BITFLAGS & 1 = 0
AND SITES.BITFLAGS & 2 = 0
) AS SITEINFO
ON SITEINFO.SITEID=SOLUTIONS.SITEID AND SITEINFO.SITEID IS NOT NULL
INNER JOIN ALLDOCS ALLDOCS WITH (NOLOCK)
ON ALLDOCS.SITEID = SOLUTIONS.SITEID
AND ALLDOCS.DIRNAME = (CASE
WHEN SITEINFO.SITECOLLECTIONURL = '/' THEN '_CATALOGS/SOLUTIONS'
ELSE (SUBSTRING(SITEINFO.SITECOLLECTIONURL,2,LEN(SITEINFO.SITECOLLECTIONURL)-1)+ '/_CATALOGS/SOLUTIONS')
END)
AND ALLDOCS.LEAFNAME = SOLUTIONS.NAME
INNER JOIN ALLUSERDATA ALLUSERDATA WITH (NOLOCK)
ON ALLUSERDATA.TP_SITEID = ALLDOCS.SITEID
AND ALLUSERDATA.TP_LISTID = ALLDOCS.LISTID
AND ALLUSERDATA.TP_ID = ALLDOCS.DOCLIBROWID
INNER JOIN USERINFO USERINFO WITH (NOLOCK)
ON USERINFO.TP_SITEID = ALLUSERDATA.TP_SITEID
AND USERINFO.TP_ID = ALLUSERDATA.TP_EDITOR
LEFT OUTER JOIN SOLUTIONFILES WITH (NOLOCK)
ON SOLUTIONFILES.SOLUTIONID = SOLUTIONS.SOLUTIONID
AND SOLUTIONFILEPATH LIKE '%Onet.xml'
WHERE SOLUTIONS.SOLUTIONGALLERYITEMID > 0
ORDER BY SITEID
UnsupportedWebTemplatesScanner
SELECT [SiteId], Webs.[Id], Webs.[FullUrl], [Title], [WebTemplate]
FROM [Webs] with (nolock)
INNER JOIN [Sites] with (nolock)
ON Sites.Id = Webs.SiteId
WHERE Sites.BitFlags & 1 = 0
AND Sites.BitFlags & 2 = 0
AND WebTemplate NOT IN ({0})
ORDER BY SiteId
WorkflowRunning2010Scanner
SELECT WF.SiteId, WF.WebId, WF.ListId, WF.ItemID, WFA.Name as WorkflowName, UserInfo.tp_Login as WorkflowInitiator
FROM [dbo].[Workflow] as WF with (nolock)
Inner Join [dbo].[WorkflowAssociation] as WFA with (nolock)
On WF.TemplateId = WFA.Id
INNER JOIN[dbo].[AllSites] with(nolock)
ON AllSites.Id = WFA.SiteId
Inner Join [dbo].UserInfo with (nolock)
On WF.Author = UserInfo.tp_ID and WF.SiteId = UserInfo.tp_SiteID
Where InternalState | 2 = 2 /*InternalState is only Running*/
AND AllSites.BitFlags & 1 = 0
AND AllSites.BitFlags & 2 = 0
WorkflowRunning2013Scanner
/*Gets all the webs that have a wfsvc list that indicates the site has 2013 workflows.*/
SELECT AllLists.tp_SiteID as SiteID, AllLists.tp_WebID as WebID
FROM [AllLists] with (nolock)
INNER JOIN [AllListsAux] with (nolock)
ON AllLists.tp_ID = AllListsAux.ListID
INNER JOIN [AllSites] with (nolock)
ON AllSites.Id = AllLists.tp_SiteId
WHERE AllLists.tp_Title = 'wfsvc'
AND AllListsAux.ItemCount > 0
AND AllSites.BitFlags & 1 = 0
AND AllSites.BitFlags & 2 = 0
NOTE – PDF format updated to include both SharePoint 2013 and 2016 notes.
Ever wondered what fixes are inside of a given CU? Please see attached PDF with full detail. I wanted a new format for easy reading. Show management and make the business case for why downtime should be taken to apply CUs. Also posted at http://sharepointupdates.com/
If you found this helpful, please leave a comment.
Check out https://aad.portal.azure.com/. Microsoft Azure Activity Directory admin center is open to all users and is a great tool to browse Azure AD, lookup user details, locate groups, manage applications, monitor usage, and more.
Users with higher permissions will see more tiles including:
![shades_smile_thumb_thumb_thumb_thumb[2]](https://www.spjeff.com/wp-content/ftp_uploads/Whats-in-that-patch-SharePoint-2013--Oct_A762/shades_smile_thumb_thumb_thumb_thumb2.gif "shades_smile_thumb_thumb_thumb_thumb[2]")
