Office 365: User Management using Powershell

In this article we will discuss some of the useful PowerShell Commands for managing Users in Office 365 (O365)

If you want to follow along then the prerequisite for this article are:

  • Having a O365 Account created
  • PowerShell for Office 365 configured

If the above prerequisites does not meet then I would recommend you to read one of my earlier article “Office 365: How to Configure PowerShell for O365” to get to know the steps of installing prerequisites.

  • Login to O365 Portal

1

  • Click on the Admin Center Tile

2

  • Click on “Active Users” navigation link under “Users” in the left navigation

Currently we can see only one Active User “Prashant Bansal”.

Since this is a free trail account so we cannot have more than one user here, but if you go for a paid plan then you can get multiple users as well.

3

Now in the following sections we will be looking into some of the useful operation and corresponding PowerShell commands regarding User Management

Export All Users

Command

Get-MsolUser

Usage

This command returns all the active users present in your O365 Account

Output

4

5

Export All Users with matching filter

Let’s see some of the properties of the User Profile available for every User added. Based on these properties we can export filtered set of Users. Lets’ check for the User properties first –

  • Select the User
  • Click Edit on the Profile Card on the right

6

  • Expand Additional Details
  • I have updated the User properties and now going to filter the Users based on “Job Title” property

7

Command

Get-MsolUser -Title “SharePoint Technical Consultant”

Usage

This command export all the Users where “Job Title = SharePoint Technical Consultant”

Output

8

 Export All Users with matching filter and selected properties

Command

Get-MsolUser -Title “SharePoint Technical Consultant” | Select DisplayName

Usage

This command export only “Display Names” for all the Users where “Job Title = SharePoint Technical Consultant”

Output:

9

Export All Users with matching filter and selected properties to a file

Command

Get-MsolUser -Title “SharePoint Technical Consultant” | Select DisplayName > “C:\SharePoint-Online-User-Data.txt”

Usage

This command export only “Display Names” for all the Users where “Job Title = SharePoint Technical Consultant” to a Text File at the specified path

Output

10

Create New Users

Command

New-MsolUser -UserPrincipalName “spdev002@spdevs001.onmicrosoft.com” -DisplayName “Prashant Bansal New User” -LastName “New User” -LicenseAssignment “spdevs001:DEVELOPERPACK” -UsageLocation “IN”

Usage

This command Adds a new User to the O365 Account specified by parameter “UserPrincipalName” and sets its basic properties like “Display Name, Last Name” and so on. It also assigns the license to the user specified by the parameter “LicenseAssignment”.

It is important to note that passing location information as specified by the parameter “UsageLocation” is mandatory as this is used by Microsoft for tracking the location where the service is consumed. This valid value for this parameter could be a two letter country code like for India it should be “IN”

If I execute this command I would get the following error since a free trail O365 account grant me a Single User license only.

If I am having proper license supporting multiple Users, this command would add a new User to my O365 Account.

Output

11

Modify Existing Users

Command

Set-MsolUser -UserPrincipalName “spdev001@spdevs001.onmicrosoft.com” -LastName “Mohan Bansal” -DisplayName “Prashant Mohan Bansal”

Usage

Using this command we can modify an existing User in our O365 Account specified by parameter “UserPrincipalName” and providing its properties like “Display Name, Last Name” and so on that needs to be updated.

Output

12

Once update is performed it might take a couple of minutes to take effect in O365 Account.

User Properties before update command executed:

13

User Properties after update command executed:

14

15

That all for this article on User Management using PowerShell for O365.

All of the above Operations can be automated and using same PowerShell Development paradigms as we followed for On Premise SharePoint Implementations.

Hope you find it helpful.

Advertisements

SharePoint 2013: How to access Host Web from SharePoint App/Add-in

In this article we will discuss another important implementation showing how we can access the Host Web Elements from with the App Executing (App Web) with in Host Web.

To perform this demo we would follow a Use Case where user of my App needs to access data from Lists present in the Hosting Web or may be saving there settings information into the Hosting Web to support Backup/Restore features.

Consider a List “Categories” which is present in Hosting Web “Development”

1

If we look inside of Categories List we can see data corresponding to each category

2

So our Hosting Web is ready with the data and now it is time to start with developing a new App that will be hosted by our Hosting Web.

  • Launch Visual Studio
  • Select “App for SharePoint” Template to create a new App Project
  • Give the Project a suitable name
  • Click OK

3

 

  • Since we are developing SharePoint Hosted App Select “SharePoint-Hosted” as the hosting model
  • Click Finish

4

Wait till Visual Studio configure the App Project for you

5

Once the Project is ready we can see the default.aspx launched by default

6

On inspecting Solution Explorer we can see all the artifacts which are part of this project.

Out of all “App.js” and “App.css” under Script and Content directories respectively deserves special attention.

  • “App.js” is the JS file where we are going to put all our business logic.
  • “App.css” is the CSS file where we are going to put all our business specific branding.

7

In Step 1 Below we can see SharePoint JS Libraries are added by default to the project by SharePoint App Project Template

In Step 2 we can see the reference of “App.css” file added to the default.aspx by default

In Step 3 we can see the reference of “App.js” file added to the default.aspx by default

So we are all set focus on our business logic directly. All thanks to SharePoint App Project Templates. J

8

Let’s start with default.aspx to add some UI elements to showcase an intuitive UI to our end users

In Step 1 & 2 we are adding HTML & CSS to default.aspx so nothing fancy about it

In Step 3 we are adding a HTML button and applying CSS to it. We will be hooking up the click event to this button later on in our JS file

9

 

Once we got the UI ready we can deploy the App clicking the Start button in Visual Studio Toolbar

10

It is worth to look for Output Window to watch out the progress of the Deployment Process

11

Provide credentials when asked for

12

 

 

And here are we with default.aspx Page of our App.

Step 1 represents the App URL in action under a separate App Domain.

Step2 represents the results panel where we will show the output of App logic execution

Step 3 represents the HTML button element that will trigger the business logic execution

So far so good!!!

13

Now let’s add the code to “App.js” File

In Step 1 we are hooking up the Click Event Handler to our HTML Button. The handler function “callToHostWeb” will get execute on button click.

In Step 2 we are reading a Query String Token “SPHostUrl”, this token will provide us with the Host Web Url.

In Step 3 we are calling “AppContextSite” method by passing the “App Context” and “Host Web Url “ to initialize the “Host Web Context”

In Step 4,5,6 we are performing usual JSOM operations of reading List Data from Host Web

In Step 7 we are building a message to display in the Result Panel of our UI only of the previous operations in steps 1-6 completed successfully

14

Step 8 represents the helper method to retrieve the Query String parameters out of the Requested URL

15

Once we are done with the code we can deploy the App again and see what happened

16

Ooops!!! Got an exception??

But why now and why not earlier?

This is because this time we are trying to access the Host Web for which the App does not have the required permissions.

So in order to fix this issue we need to grant atleast Read permission to the Host Web.

Grant App Permissions
  • Go back to Visual Studio
  • Locate the “AppManifest.xml” in Solution Explorer

17

  • Click on Permissions Tab

18

  • Choose Site “Collection” from Scope Dropdown and “Read” from Permission dropdown

19

Now deploy the App again

Click “Trust It” when asked for

20

Now click on “Call to Host Web” Button to get all the Category Names read from Categories List from the Host Web

21

  • We can also verify the deployment by Navigating to the Host Web in “Apps in Testing” Library

22

 

 

Based on this simple walkthrough we can conclude how simple it is to interact with the Host Webs from within the SharePoint Apps (Add-ins).

You will find this a very common requirement to communicate for an App with its Hosting Web for one or the other reasons. And now you know how simple it is to deal with it. J

Hope you find it helpful.

 

 

SharePoint 2013: How to Configure Data Connection with SQL Server using Excel PowerPivot Plugin

As promised in my previous article on SharePoint 2013 : How to enable PowerPivot Excel Plugin for Excel 2013 I am here with a new article that shows a demonstration on leveraging Excel PowerPivot Plugin in order to prepare the reports consuming data from SQL Server.

  • Launch Excel 2013
  • Select Blank Workbook

1

  • Select POWERPIVOT Tab
  • Select Manage

2

  • Click on “Get External Data” Dropdown Ribbon Button

3

There we can see all the Data Sources that can be used to connect with

  • Select “From Database” to setup a connection with SQL Server

4

  • Select SQL Server Instance Name

Wait for further processing

5

  • Select the Database Name

6

  • Test the connection
  • If the connection Succeed, Click Next

7

In the next Step you can choose either of the two options

  • Select Tables or View from the Look List
  • Write down your own Query to execute directly and fetch the result set in the form of table

Though as a part of good practice it would always be a good idea to proceed with option 1 as we are going to do it here after

  • Select Option 1 as shown below:

8

  • Select the required Tables from the Look up Window to participate in the read operation
  • Then Click Finish

9

  • If all goes fine we can see the Success Screen
  • Click Close Button

10

 

And we can get the selected Tables exported to the Excel, presenting data to be consumed as needed then after.

11

  • Now Choose “PivotTable” from “PivotTable” Dropdown Ribbon Menu

12

Provide the Data Range to be consumed by Pivot Table

13

And sure enough you will see the Pivot Table with Fields available for the selection

We can include different Operations as needed afterwards

14

For instance we can provide Data Slicers to the Users to allow them getting filtered Views of the data as needed

  • In order to Add Slicer, click on Analyze Tab
  • Then Click Insert Slicer

15

  • Choose a relevant Filter Parameter or Slicer based on which you like to allow Users to Filter the data.

Here we are choosing “StateProvinceName” as Slicer

16

  • Go to Slicer Settings to provide an appropriate name to the Slicer
  • Right Click on Slicer
  • Select Slicer Settings from the Context Menu

17

  • Specify the Caption for the Slicer

18

  • Once this is all done we would be having a fully functional PowerPivot Report to be published for the Users
  • Go To File Menu
  • Select Save As
  • Click on Browse Button

19

  • Specify the Url of Document Library where you want to Publish this file

20

  • Browse the Document Library
  • Specify the Name of the File to be Published

And Save it

21

  • Browse the Document Library from the Browser and see if the file is published properly

22

  • Click on the Report and let it run in Browser

And sure enough we will see the List of Cities with a “State Province Name” Filter (Slicer) in action.

23

This is no code implementation of quite an effective Reporting Solution backed up by rich PowerPivot Functionality.

Hope you find it helpful.

SharePoint 2013: Important SQL Queries on SharePoint Content Databases – Part I

Since it is not recommended to undergo direct execution of SQL Queries on SharePoint Content Database so I would strictly recommend you to first go through one of my earlier articles “Executing Direct SQL Queries on SharePoint Content Databases: Is it a good idea???” talking about recommended practices & consequences of direct execution.

With the assumption that you already aware of the consequences of executing the direct SQL queries on SharePoint Content Databases, I would like to present the following important queries that can be used to retrieve SharePoint Vitals directly from the Content Databases, which would otherwise be very difficult to get from SharePoint Programming APIs.

These Queries are tested with SharePoint 2013 and few of them might not work with the earlier Versions of SharePoint due to change in Database Schema.

Returns Total Number of Site Collections in a Web Application

Query

SELECT COUNT(*) AS ‘TOTAL SITE COLLECTION’ FROM SITES

Output

1

Returns Root Site Title for each Site Collection available in WebApplication

Query

SELECT TITLE AS ‘ROOT WEB TITLE’, SITES.ROOTWEBID, SITES.ID AS ‘SITE COLLECTION ID’ FROM WEBS INNER JOIN SITES ON WEBS.ID = SITES.ROOTWEBID

Output

2

Returns Total Web Sites in Web Application

Query

SELECT COUNT(*) As “Webs Count” FROM WEBS 

Output

3

Returns Site Title and Site Id

Query

SELECT TITLE AS ‘SITE TITLE’,FULLURL, SITEID AS ‘SITE COLLECTION ID’ FROM WEBS ORDER BY SITEID 

Output

4

Returns Total number of Web Sites under each Site Collection

Query

SELECT SITEID, COUNT(*) AS ‘TOTAL SUB SITES’ FROM WEBS INNER JOIN SITES ON SITES.ID = WEBS.SITEID GROUP BY SITEID

Output

5

Returns Total number of Web Sites under each Site Collection for ‘Doc Lib’ and ‘Form Lib’

Query

SELECT “TEMPLATE TYPE” = CASE WHEN [LISTS].[TP_SERVERTEMPLATE] = 101 THEN ‘DOC LIB’ WHEN [LISTS].[TP_SERVERTEMPLATE] = 115 THEN ‘FORM LIB’ ELSE ‘UNKNOWN’ END, “LIST URL” = ‘http://win-etmg052h5r/’ + CASE WHEN [WEBS].[FULLURL]=” THEN [WEBS].[FULLURL] + [LISTS].[TP_TITLE] ELSE [WEBS].[FULLURL] + ‘/’ + [LISTS].[TP_TITLE] END, “TEMPLATE URL” = ‘http://win-etmg052h5r/’ + [DOCS].[DIRNAME] + ‘/’ + [DOCS].[LEAFNAME] FROM [LISTS] LEFT OUTER JOIN [DOCS] ON [LISTS].[TP_TEMPLATE]=[DOCS].[ID], [WEBS] WHERE ([LISTS].[TP_SERVERTEMPLATE] = 101 OR [LISTS].[TP_SERVERTEMPLATE] = 115) AND [LISTS].[TP_WEBID]=[WEBS].[ID] ORDER BY “LIST URL”  

Output

 6

Returns count of documents from site collection

Query

SELECT SUM(ITEMCOUNT) AS [TOTAL ITEM] FROM LISTS INNER JOIN WEBS ON LISTS.TP_WEBID = WEBS.ID INNER JOIN ALLLISTSAUX ON LISTS.TP_ID = ALLLISTSAUX.LISTID WHERE TP_SERVERTEMPLATE = 101

Output

7

Returns Items Count for each Document Library (101)

Query

SELECT CASE WHEN WEBS.FULLURL = ” THEN ‘PORTAL SITE’ ELSE WEBS.FULLURL END AS [SITE RELATIVE URL], WEBS.TITLE AS [SITE TITLE],LISTS.TP_TITLE AS TITLE,TP_DESCRIPTION AS DESCRIPTION,ITEMCOUNT AS [TOTAL ITEM] FROM LISTS INNER JOIN WEBS ON LISTS.TP_WEBID = WEBS.ID INNER JOIN ALLLISTSAUX ON LISTS.TP_ID = ALLLISTSAUX.LISTID WHERE TP_SERVERTEMPLATE = 101 ORDER BY [SITE RELATIVE URL] 

Output

8

Returns all the top level site collections

Query

SELECT SiteId AS SiteGuid, Id AS WebGuid, FullUrl AS Url, Title, Author, TimeCreated FROM dbo.Webs WHERE (ParentWebId IS NULL) 

Output

9

Returns all the child sites in a site collection 

Query

SELECT SiteId AS SiteGuid, Id AS WebGuid, FullUrl AS Url, Title, Author, TimeCreated FROM dbo.Webs WHERE (NOT (ParentWebId IS NULL))

Output

10

Returns all the SharePoint groups in a site collection

Query

SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, dbo.Groups.ID AS Expr1,dbo.Groups.Title AS Expr2, dbo.Groups.Description FROM dbo.Groups INNER JOIN dbo.Webs ON dbo.Groups.SiteId = dbo.Webs.SiteId 

Output

11

Returns all the users in a site collection

Query

SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, bo.UserInfo.tp_ID, dbo.UserInfo.tp_DomainGroup, dbo.UserInfo.tp_SiteAdmin, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Email FROM dbo.UserInfo INNER JOIN dbo.Webs ON dbo.UserInfo.tp_SiteID = dbo.Webs.SiteId 

Output

12

Returns all the members of the SharePoint Groups

Query

SELECT dbo.Groups.ID, dbo.Groups.Title, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login FROM dbo.GroupMembership INNER JOIN dbo.Groups ON dbo.GroupMembership.SiteId = dbo.Groups.SiteId INNER JOIN dbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID

Output

13

Returns all the sites where a specific feature is activated

Query

SELECT dbo.Webs.Id AS WebGuid, dbo.Webs.Title AS WebTitle, dbo.Webs.FullUrl AS WebUrl, dbo.Features.FeatureId,dbo.Features.TimeActivated FROM dbo.Features INNER JOIN dbo.Webs ON dbo.Features.SiteId = dbo.Webs.SiteId AND dbo.Features.WebId = dbo.Webs.Id WHERE (dbo.Features.FeatureId = ’00BFEA71-D1CE-42de-9C63-A44004CE0104′)  

Output

14

Returns all the users assigned to roles

Query

SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle,dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login FROM dbo.RoleAssignment INNER JOIN dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN dbo.UserInfo ON dbo.RoleAssignment.PrincipalId = dbo.UserInfo.tp_ID

Output

15

Returns all the SharePoint groups assigned to roles

Query

SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle, dbo.Groups.Title AS GroupName FROM dbo.RoleAssignment INNER JOIN dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN dbo.Groups ON dbo.RoleAssignment.SiteId = dbo.Groups.SiteId AND dbo.RoleAssignment.PrincipalId = dbo.Groups.ID

Output

16

Returns all the users assigned to roles

Query

SELECT DISTINCT CASE WHEN PATINDEX(‘%\%’, FullUrl) > 0 THEN LEFT(FullUrl, PATINDEX(‘%\%’, FullUrl) – 1) ELSE FullUrl END AS [Site], Webs.Title, Webs.FullUrl,Perms.ScopeUrl,UserInfo.tp_Login As Account, CASE WHEN UserInfo.tp_DomainGroup>0 THEN NULL ELSE UserInfo.tp_Title END AS Username, CASE WHEN UserInfo.tp_DomainGroup>0 THEN UserInfo.tp_Login ELSE NULL END AS [AD Group], NULL AS [SharePoint Group], Roles.Title AS RoleTitle, Roles.PermMask FROM dbo.RoleAssignment INNER JOIN dbo.UserInfo ON RoleAssignment.SiteId = UserInfo.tp_SiteID AND UserInfo.tp_ID = RoleAssignment.PrincipalId INNER JOIN dbo.Perms ON Perms.SiteId = RoleAssignment.SiteId AND Perms.ScopeId = RoleAssignment.ScopeId INNER JOIN dbo.Roles ON RoleAssignment.SiteId = Roles.SiteId AND RoleAssignment.RoleId = Roles.RoleId INNER JOIN dbo.Webs ON Roles.SiteId = Webs.SiteId AND Roles.WebId = Webs.Id WHERE Roles.Type<>1 AND tp_Deleted=0

Output

17

Returns all the SharePoint groups assigned to roles

Query

SELECT DISTINCT CASE WHEN PATINDEX(‘%\%’, FullUrl) > 0 THEN LEFT(FullUrl, PATINDEX(‘%\%’, FullUrl) – 1) ELSE FullUrl END AS [Site], Webs.Title, Webs.FullUrl, Perms.ScopeUrl, UserInfo.tp_Login As Account, CASE WHEN UserInfo.tp_DomainGroup>0 THEN NULL ELSE UserInfo.tp_Title END AS Username, CASE WHEN UserInfo.tp_DomainGroup>0 THEN UserInfo.tp_Login ELSE NULL END AS [AD Group], Groups.Title AS [SharePoint Group], Roles.Title AS RoleTitle, Roles.PermMask FROM dbo.RoleAssignment INNER JOIN dbo.Roles ON RoleAssignment.SiteId = Roles.SiteId AND RoleAssignment.RoleId = Roles.RoleId INNER JOIN dbo.Perms ON Perms.SiteId = RoleAssignment.SiteId AND Perms.ScopeId = RoleAssignment.ScopeId INNER JOIN dbo.Webs ON Roles.SiteId = Webs.SiteId AND Roles.WebId = Webs.Id INNER JOIN dbo.Groups ON RoleAssignment.SiteId = Groups.SiteId AND RoleAssignment.PrincipalId = Groups.ID INNER JOIN dbo.GroupMembership ON GroupMembership.SiteId = Groups.SiteId AND GroupMembership.GroupId = Groups.ID INNER JOIN dbo.UserInfo ON GroupMembership.SiteId = UserInfo.tp_SiteID AND GroupMembership.MemberId = UserInfo.tp_ID WHERE Roles.Type<>1 AND tp_Deleted=0

Output

18

Returns all document from all lists availabe in WebApplication

Query

SELECT AllDocs.Leafname AS FileName, AllDOcs.Dirname AS “Folder Path”, AllLists.tp_Title AS “List Title”, Webs.Title AS “Web Title” FROM AllDocs JOIN AllLists ON AllLists.tp_Id=AllDocs.ListId JOIN Webs ON Webs.Id=AllLists.tp_WebId ORDER BY webs.title

Output

19

Returns master pages in Web Application for all Web Sites

Query

SELECT AllDocs.Leafname AS FileName, AllDocs.Dirname AS “Folder Path”, AllLists.tp_Title AS “List Title”, Webs.Title AS “Web Title” FROM AllDocs JOIN AllLists ON AllLists.tp_Id=AllDocs.ListId JOIN Webs ON Webs.Id=AllLists.tp_WebId WHERE AllDocs.Extension = ‘master’ ORDER BY Webs.Title

Output

20

Returns Top 100 Documents that is versioned based on doc size

Query

SELECT TOP 100 Webs.FullUrl As SiteUrl, Webs.Title ‘Document/List Library Title’, DirName + ‘/’ + LeafName AS ‘Document Name’, COUNT(AllDocversions.UIVersion)AS ‘Total Version’, SUM(CAST((CAST(CAST(AllDocversions.Size as decimal(10,2))/1024 As decimal(10,2))/1024) AS Decimal(10,2)) ) AS ‘Total Document Size (MB)’, CAST((CAST(CAST(AVG(AllDocversions.Size) as decimal(10,2))/1024 As decimal(10,2))/1024) AS Decimal(10,2))   AS ‘Avg Document Size (MB)’ FROM Docs INNER JOIN AllDocversions ON Docs.Id = AllDocversions.Id INNER JOIN Webs On Docs.WebId = Webs.Id INNER JOIN Sites ON Webs.SiteId = SItes.Id WHERE Docs.Type <> 1 AND (LeafName NOT LIKE ‘%.stp’) AND (LeafName NOT LIKE ‘%.aspx’) AND (LeafName NOT LIKE ‘%.xfp’) AND (LeafName NOT LIKE ‘%.dwp’) AND (LeafName NOT LIKE ‘%template%’) AND (LeafName NOT LIKE ‘%.inf’) AND (LeafName NOT LIKE ‘%.css’) GROUP BY Webs.FullUrl, Webs.Title, DirName + ‘/’ + LeafName ORDER BY ‘Total Version’ desc, ‘Total Document Size (MB)’ desc 

Output

21

Returns the Document List Name, File Name, URL, and the Content (Binary Format)

Query

SELECT AllLists.tp_Title AS “List Name”, AllDocs.LeafName AS “File Name”, AllDocs.DirName AS “URL”, DocStreams.Content AS “Document Contnt (Binary)” FROM AllDocs JOIN DocStreams ON AllDocs.Id=DocStreams.DocId JOIN AllLists ON AllLists.tp_id = AllDocs.ListId  

Output

22

Returns Documents By Age

Query

SELECT Webs.FullUrl AS SiteUrl, Webs.Title AS [Title], DirName + ‘/’ + LeafName AS [Document Name], Docs.TimeCreated FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id INNER JOIN Sites ON Webs.SiteId = Sites.Id WHERE Docs.Type <> 1 AND (LeafName IS NOT NULL) AND (LeafName <> ”) AND (LeafName NOT LIKE ‘%.stp’) AND (LeafName NOT LIKE ‘%.aspx’) AND (LeafName NOT LIKE ‘%.xfp’) AND (LeafName NOT LIKE ‘%.dwp’) AND (LeafName NOT LIKE ‘%template%’) AND (LeafName NOT LIKE ‘%.inf’) AND (LeafName NOT LIKE ‘%.css’) ORDER BY Docs.TimeCreated DESC

Output

23

Returns Total # of Documents

Query

SELECT COUNT(*) As “Total Number of Documents” FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id INNER JOIN Sites ON Webs.SiteId = Sites.Id WHERE Docs.Type <> 1 AND (LeafName IS NOT NULL) AND (LeafName <> ”) AND (LeafName NOT LIKE ‘%.stp’) AND (LeafName NOT LIKE ‘%.aspx’) AND (LeafName NOT LIKE ‘%.xfp’) AND (LeafName NOT LIKE ‘%.dwp’) AND (LeafName NOT LIKE ‘%template%’) AND (LeafName NOT LIKE ‘%.inf’) AND (LeafName NOT LIKE ‘%.css’)

Output

24

Returns Total Size of All Content

Query

SELECT SUM(CAST((CAST(CAST(Size AS DECIMAL(10,2))/1024 AS DECIMAL(10,2))/1024) AS DECIMAL(10,2))) AS “Total Size in MB” FROM Docs INNER JOIN Webs ON Docs.WebId = Webs.Id INNER JOIN Sites ON Webs.SiteId = Sites.Id WHERE Docs.Type <> 1 AND (LeafName IS NOT NULL) AND (LeafName NOT LIKE ‘%.stp’) AND (LeafName NOT LIKE ‘%.aspx’) AND (LeafName NOT LIKE ‘%.xfp’) AND (LeafName NOT LIKE ‘%.dwp’) AND (LeafName NOT LIKE ‘%template%’) AND (LeafName NOT LIKE ‘%.inf’) AND (LeafName NOT LIKE ‘%.css’)  

Output

25

In the upcoming articles in this series I will try to add some useful queries that can retrieve some of the other pieces of vital information out of SharePoint.

Hope you find it helpful.