Archive for the ‘SQL Queries’ Category

Four ways to Get Report of SharePoint Folders and Files for a Library

Wednesday, February 11th, 2015

Version: 1.0

Scenario

You might have many layers of folders insides folders and files insides those folders in a SharePoint Library. How to get a quick report of folders and files for a single Document Library?

There are different ways to get that information:

a. Using Generate File Plan Report Feature of Document Library
b. Using Mapped Drive with Dir Command
c. Using SQL Query
d. Using Power Shell

a. Using Generate File Plan Report Feature of Document Library

SharePoint has a feature in Document Library called ‘Generate File Plan Report’. Follow the steps below:

1. Go to a document library for which you need to get the list of folders and files
2. Go to Library > Library Settings
3. Click on ‘Generate File Plan Report‘ under Permissions and Management

SharePoint Document Library - Generate File Plan Report
SharePoint Document Library – Generate File Plan Report

5. On next page, Save location field will default to the same library. That is the place where the system will generate and save the report file. Click Ok
6. Report will be generated and you will see a link on next page. Click on the page to open the report in MS Excel

SharePoint Document Library - Click the link to review report
SharePoint Document Library – Click the link to review report

7. Click OK and you will back to the Document Library. You can locate the file in the same library or to a place you picked in step 5

Example Report File

b. Using Mapped Drive with Dir Command

The above report doesn’t show you the folders and files that are inside those folders. It just reports the Total # of items at each folder layer.

To get the full hierarchy of folders including files inside each level of hierarchy, you need to switch back to basics. My 1992 study of  Peter Norton’s Dos Guide came to rescue from here.

1. Copy the URL http://sharepoint.svlab.com/sites/biz1/project1/ProjectsDocLib/                                [notice I removed .aspx view file and form folder that contains the view pages]
2. Map an available drive letter on your PC say W: and use the above URL
3. Open a command prompt and go to W: drive
4. Use the command
dir /s > c:FilesFolders.txt

You can add /b switch to get rid of file / folder details and just get the files / folder names.
dir /s /b > c:FileFolders.txt

5. Open the txt file and you will see the folders and files in hierarchical structure
6. Make sure to disconnect the W: drive.

Navigating using Windows Explorer with SharePoint is not the fastest way to work with SharePoint.

c. Using SQL Query

You can use the SQL query to get the list of files and folders. Run at your own risk in a pre-production system.

— Query to list Files and Folders within Sites
— By Faisal Masood
SELECT 
Webs.Title AS ‘WebApp’,
AllLists.tp_Title AS ‘ListName’,
AllDocs.DirName AS ‘URL’,
AllDocs.LeafName AS ‘FileName’,
AllDocs.ExtensionForFile AS ‘FileType’,
AllDocs.TimeCreated
FROM AllDocs 
JOIN AllLists
ON AllLists.tp_ID = AllDocs.ListId
JOIN AllDocStreams
ON AllDocStreams.Id = AllDocs.Id
JOIN Webs
ON Webs.Id = AllLists.tp_WebId
WHERE — Webs.Title LIKE ‘%SharePoint Company Sitet%’ AND
AllDocs.DirName like ‘documents%’
–AND AllDocs.TimeCreated > ‘2010-05-10 00:00:00.000’
ORDER BY Webs.Title

c. Using Power Shell

PowerShell might be the best option to get customized report with selective information. The PowerShell code below will get show all documents under a document library names that has Documents in name and that are under site sharepoint.svlab.com site.

# List Files and Folders under a Documents Library
# By: Faisal Masood – PMP, MCSE, MCITP, MCSA
#Add SharePoint PowerShell SnapIn if not already added
 if ((Get-PSSnapin “Microsoft.SharePoint.PowerShell” -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin “Microsoft.SharePoint.PowerShell”
}

# http://sharepoint.svlab.com/
Get-SPWeb http://sharepoint.svlab.com/ |
  Select -ExpandProperty Lists |
    Where { $_.GetType().Name -eq “SPDocumentLibrary” -and
            -not $_.Hidden } |
    Select -ExpandProperty Items |
    Where { $_.Url -like “*Documents*” } |
    Select Name, {$_.File.Length}, url

Download .ps1 PowerShell Script File

Keywords:

  • get report of files and folders in SharePoint
  • report folders and files in SharePoint
  • Four ways to get Files Folder structure
  • SQL query to get Files Folder structure
  • PowerShell to Files Folder structure
Visit www.softvative.com for professional services.

MySite Deleted by MySite Cleanup Job

Wednesday, May 21st, 2014

Version 1.5

Applied To: SharePoint 2010, 2013, 2016, SharePoint Online

Situation:

Lot of users lost their SharePoint MySite. The site got deleted and they were not able to see any data in their MySite. In some situations, users were prompted for the creation of new MySite. Most of the deleted sites were for inactive users though.

MySite Cleanup Process Chart:

I created an initial version of the following MySite Cleanup Job Process for overview.

SharePoint User Profile MySite Cleanup Job Process by Softvative Inc

Cause:

SharePoint Server has a ‘My Site Cleanup Job’ as a Timer Job. The job runs every hour. You can view the job at this location:

SharePoint Central Administration website > Monitoring > Under ‘Time Jobs’ click ‘Review Job Definitions‘ > Scroll to the bottom of the page and go to next page. Look for ‘My Site Cleanup Job‘ and click on it.

SharePoint My Site Cleanup Job

The function of My Site Cleanup job is to delete the user profile and My site of users that are queued for deletion.

Conditions that will mark the account for deletion:

The user profile and My site will be marked for deletion if any of the following conditions is met.

a. account is deleted in AD (Active Directory)
b. account is disabled in AD
c. SharePoint Profile Sync connection is modified with additional filters, deleted, recreated (e.g filter like exclude user with Department = Terminated)
d. account is moved to an AD OU that is not part of SharePoint Profile Sync

Test Scenario:

I used six test accounts with few of those having managers listed in AD account. The SharePoint Profile Sync added those to the SharePoint profile. Later I logged in as those test users and created their my sites by clicking on the link. Alternatively click username drop down on top right, choose My Site from dropdown. Then click on ‘My Content‘ link on top left.

http://mySiteHost.domain.com/_layouts/MySite.aspx

Then I deleted one, disabled two and moved to a different OU one account. That left the two out of six test accounts as untouched in AD.

Subsequent user profile run marked the accounts based on those conditions with bDeleted =1 in SQL tables. That in layman terms – the site is queued for deletion.

I got following email notification for one of the test account that was setup as my Direct Report in AD accounts properties.

The My Site of Test User 5 is scheduled for deletion in 14 days. As their manager you are now the temporary owner of their site. This temporary ownership gives you access to the site to copy any business-related information you might need. To access the site use this URL: http://mysiteHost.domain.com/personal/testuser5

After 11th day, I got same email but with deletion in 3 days.

SharePoint MySite Cleaner job then looks at the bDeleted=1 and notifies the user’s Manager if one was listed in AD, or Secondary MySite Owner if no manager was configured. It sends second notification after 11 days. After 14 days it deletes the site. In few situations, I’ve seen users were actively working in SharePoint when their My site got deleted. That happens after changes made by AD Team or SharePoint team (conditions above – Conditions that will mark the account for deletion) but after 14 days the site will be deleted even if the changes were reverted.

 

SQL Query to check SharePoint user profiles / My Sites marked for deletion:

I then used the following SQL queries to find the status. It is not a recommended method, use it at your own risk.

1. Use the following query to check the accounts that are marked for deletion. 
— Check the Sharepoint User Profile accounts that are marked for deletion
select * from [Profile DB].[dbo].[UserProfile_Full] with (nolock)
where bDeleted =1
order by PreferredName

2. SQL Query to check the Email Notification Status sent to Manager or Secondary Mysite Owner:
–Query the MysiteDeletion Email Notification status in User Profile DB in Sharepoint
select * from [Profile DB].[dbo].[MySiteDeletionStatus] with (nolock)
–where NotificationStatus = 1 — Email sent to Manager or Seconday contact that site will be deleted in 14 days
— where NotificationStatus = 2 — Email sent to Manager or Seconday contact that site will be deleted in 3 days

3. SQL Query to find the users My Site in MySite Content DB – AllWebs Table
–SQL Queries to check the users under MySite Web App / Content DB
SELECT FullUrl, Title, RequestAccessEmail 
FROM [WSS_Content_MySites].[dbo].[AllWebs]
order by FullUrl

4. SQL Query to check the users info in MySite content DB – UserInfo Table
— Another Query to check users Info under MySite Content DB / MySite Web App
select * from [WSS_Content_MySites].[dbo].[UserInfo] with (nolock)
order by tp_Login

Recommendations:

a. Make sure to test your User Profile Synchronization Connections in pre-production first.

SharePoint Central Admin > Application Management > Manage Service Applications > Click on User Profile Synchronization service application > Click ‘Configure Synchronization Connections’ under Synchronization section. Hover mouse over the connection name, click on drop down, and choose Edit to change the selection of Active Directory OU in the sync. Choose ‘Edit Connection Filters’ from the previous connection page to review / update filters.

SharePoint User Profile Service Application – Synchronization Connection

b. Partially Disable the ‘My Site Cleanup Job’ when editing Profile Sync connection. Later make sure to enable the job. My Site Cleanup Job performs some other cleanups as well like Organization Browser list, People Picker etc.

c. Make sure the ‘Secondary MySite Owner’ account listed has an email address that is monitored by the SharePoint Team or those email get forwarded to SharePoint Team to act on it.

d. Make it as part of SharePoint Governance & training that Managers should notify SharePoint Team when they get the email notification for site deletion with in 14 days.

e. Monitor / control changes done in Active Directory that will impact SharePoint users. Example like moving AD account to a different OU that is not part of share point User Profile Sync.

SharePoint Online / OneDrive for Business

OneDrive for Business stores documents under user’s mysite (part of User Profile) in SharePoint Online (Office 365). Use the following Microsoft KB article for reference:
 
OneDrive for Business retention and deletion – https://support.microsoft.com/en-us/kb/3042522
 
Use OrphanedPersonalSitesRetentionPeriod parameter of Set-SPOTenant PowerShell cmdlet to increase the value of 30 content deletion from default value of 30 days to a higher value. Email notification will still be sent notifying contents will be deleted in 30 day and 7 days. https://technet.microsoft.com/en-us/library/fp161390.aspx
 
Use IncludeOnlyPersonalSite and Limit parameters of Get-SPODeletedSite Powershell cmdlet to get the list of Personal sites (OneDrive for Business contents) that are marked for deletion. https://technet.microsoft.com/library/fp161365.aspx
 
Use SharePoint Online eDiscovery to put a hold on deletion of Mysite (OneDrive For Business) contents. Create a new site collection using eDiscovery template in your SharePoint Online Tenant if you don’t have eDiscovery site in place.
 

Note:
I’ll cover the process on how to analyse and recover the deleted MySites in situations where bulk of Mysites got deleted. That is to see which sites had data and which ones were just not actively used MySites or deleted MySites without any documents to recover. I’ll try to cover the use of PowerShell commands Get-SPDeletedSite and Restore-SPDeletedSite.

References:
1. http://blogs.msdn.com/b/kaevans/archive/2012/06/25/top-recommendations-for-managing-the-my-site-cleanup-timer-job.aspx
2. http://blogs.technet.com/b/fromthefield/archive/2013/07/25/windows-powershell-script-to-output-site-collection-information.aspx

Search Keywords:

MySite Deleted by SharePoint User Profile MySite Cleanup Job
MySite Deleted by MySite Cleanup Job
MySite Cleanup Job
User Profile Sync removed mysite
SharePoint MySite Removed
SharePoint MySite deleted
SharePoint User Profile deleted
How A User Profile is Deleted?
How A MY Site is Deleted?
My Site deletion Mystery
Process of My Site Deletion
My Site Deletion Process
Lost MySite Data
Lost SharePoint Mysite
Onedrive for Business deletion
Prevent OneDrive for Business deletion
Retain OneDrive for Business contents
Prevent SharePoint Online user site deletion
SharePoint Online mysite deletion
SharePoint eDiscovery to prevent OneDrive Deletion

Query to List PWA Groups with Resources

Saturday, February 17th, 2007

Query to List PWA Groups with Resources

Version 3.2, Modified: 01/24/20175

Applies To: Project Server 2007, 2010, 2013


The following query gives you list of groups with users, their NT accounts, email address & some other details. I’ve used the query to get list of all users that have Project Manager / Resource Manager access or part of similar custom groups. These can be often helpful in getting the list of users that might have MS Project Professional installed on their desktops for licensing counts.

MS Project Server 2013 Version

 

Click here to download the 2013 version query.

 

MSP2013_SQLQuery_Designer_for_Resources_and_Associated_Groups

MSP2013 SQLQuery Designer for Resources and Associated Groups

 

 

MS Project Server 2010 Version

— MSPS – Query to Finds PWA Security Groups with Active Users of those groups
–use ProjectServer_Published
select sg.Wsec_GRP_Name as GroupName, –mr.Wres_ID,
mr.Res_name as ResourceName, mr.Wres_NT_Account as NTAccount, mr.Wres_email as Email,
mr.Wres_Is_Enabled as IsEnabled, mr.Wres_Can_Login as CanLogin,
mr.Wres_Last_Connect as LastConnect, mr.Wres_is_Manager as IsManager
from msp_web_security_groups sg
inner join msp_web_security_group_members sgm on sg.Wsec_grp_guid = sgm.Wsec_grp_guid
inner join msp_web_resources mr on sgm.Wres_GUID = mr.Wres_GUID
where 
sg.WSec_grp_name like ‘Project Managers’ or
sg.WSec_grp_name like ‘Resource Managers’ or
sg.WSec_grp_name like ‘IT Administrators’ AND
mr.Wres_Is_Enabled = ‘1’
order by 
sg.wsec_grp_name, mr.res_name


Find users that have Permissions or Categories directly assigned

— MSP Query to Find users that have Permissions or Categories Directly Assigned
use [ProjectServer_Published]
  select
      u.res_name,
      case when c.wsec_cat_name is null
          then ‘Permission directly assigned to user’
          else ‘Category directly assigned to user’
          end SecurityIssue,
      c.wsec_cat_name,
      f.wsec_fea_act_name_id
  from
      dbo.msp_resources u
      inner join dbo.msp_web_security_sp_cat_relations r on u.res_security_guid = r.wsec_sp_guid
      left join dbo.msp_web_security_sp_cat_permissions p on r.wsec_rel_uid = p.wsec_rel_uid
      left join msp_web_security_categories c on r.wsec_cat_uid = c.wsec_cat_uid
      left join msp_web_security_features_actions f on p.wsec_fea_act_uid = f.wsec_fea_act_uid
  where
      p.wsec_allow = 1 or p.wsec_deny = 1 or c.wsec_cat_uid is not null
  order by

      u.res_name

MS Project Server 2007 Version

— This Query give gives the list of groups with associated users. (by FM)

select sg.Wsec_Grp_ID as GroupID, sg.Wsec_GRP_Name as GroupName, mr.Wres_ID,
mr.Res_name as ResourceName, mr.Wres_NT_Account as NTAccount, mr.Wres_email as Email,
mr.Wres_Use_NT_logon as UseWinAuthentication, mr.Wres_Is_Enabled as IsEnabled, mr.Wres_Can_Login as CanLogin,
mr.Wres_Last_Connect as LastConnect, mr.Wres_is_Manager as IsManager
from msp_web_security_groups sg
inner join msp_web_security_group_members sgm on sg.Wsec_grp_guid = sgm.Wsec_grp_guid
inner join msp_web_resources mr on sgm.Wres_GUID = mr.Wres_GUID
–where sg.wsec_grp_id = 4 AND sg.WSec_grp_name like ‘Project Managers’
order by sg.wsec_grp_name, mr.res_name



Contact your DBA / Project Admin to run this query. It is not recommended to run SQL queries again production Project Server / SharePoint databases.

Few external References with related Information
  1. Create a report of Users and Security Groups