Windows: Duplicate SID (Built-in)

[Daniel_B]

Old name: Computer: Duplicate SID (Built-in)

The report below lists Windows based assets with duplicate user profile SIDs

The report will only list assets that meet all of the following criteria:

• The asset state is set to "active".
  
• The asset has been successfully scanned at least once.
  
• The asset is a Windows computer.
  
• Duplicate user profile SIDs configured.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  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.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblUsers.SID,
  Duplicates.Total
From tblAssets
  Inner Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
  Inner Join (Select tblUsers.SID,
    Count(tblUsers.UserID) As Total
  From tblAssets
    Inner Join tblUsers On tblAssets.AssetID = tblUsers.AssetID
    Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
    Inner Join tblState On tblState.State = tblAssetCustom.State
  Where tblState.Statename = 'Active'
  Group By tblUsers.SID
  Having tblUsers.SID Like '%-500' And Count(tblUsers.UserID) > 1) Duplicates
    On tblUsers.SID = Duplicates.SID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblState.Statename = 'Active'
Order By Duplicates.Total Desc,
  tblUsers.SID,
  tblAssets.Domain,
  tblAssets.AssetName