User Profile Size PowerShell Audit

Find User Profile Size Details from Computers with Powershell in Your Network

Excessive user profiles on devices can cause storage issues when there are many unique users using a public device often. To manage these devices, knowing how much space these profiles take up is important to decide how to proceed and ensure that devices do not run out of storage. Aside from running disk space audits with Lansweeper, this report will give you a more precise view of the size of your user profiles.

This report is based on the use case covered in the Pro Tips #35 blog post. It requires configuration before it can be used effectively.
Windows 7 is currently not supported due to PowerShell script issues.

Updates:
2022-11-21: Excluded Windows 7 due to issues

User Profile Size Query

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
SubQuery1.ProfileName,
Cast(SubQuery1.ProfileSize / 1024 / 1024 As numeric) As ProfileSizeInMB,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.Version,
Case
When TsysLastscan.Lasttime < GetDate() - 1 Then
'Last registry scan more than 24 hours ago! Information may not be up-to-date. Try rescanning this machine.'
End As Comment,
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
TsysLastscan.Lasttime As LastRegistryScan,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value,
SubString(tblRegistry.Value, CharIndex('FolderName=', tblRegistry.Value)
+ Len('FolderName='), CharIndex(';', tblRegistry.Value) -
CharIndex('FolderName=', tblRegistry.Value) - Len('FolderName=')) As
ProfileName,
Convert(bigint, SubString(tblRegistry.Value, CharIndex('Size=', tblRegistry.Value) +
Len('Size='), CharIndex('}', tblRegistry.Value) -
CharIndex('Size=', tblRegistry.Value) - Len('Size='))) As
ProfileSize
From tblRegistry
Where tblRegistry.Regkey Like '%System\UserProfiles') SubQuery1 On SubQuery1.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'registry' and SubQuery1.ProfileName is not null
and tsysOS.OSname not like 'Win 7'
Order By tblAssets.Domain,
tblAssets.AssetName,
ProfileSizeInMB desc

Show

Hide