Windows: Shares and their share permissions (Built-in)

[Nick_VDB]

Added in v.6.0.100

The report below will give back Windows computers with their shares and share permissions.

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.
  
• The asset has share information
  

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,
  tblSharesUni.Caption As ShareCaption,
  tblSharesUni.Name As ShareName,
  tblSharesUni.Path,
  Case tblSharesUni.Type When 0 Then 'Disk Drive' When 1 Then 'Print Queue'
    When 2 Then 'Device' When 3 Then 'IPC'
    When 2147483648 Then 'Disk Drive Admin'
    When 2147483649 Then 'Print Queue Admin' When 2147483650 Then 'Device Admin'
    When 2147483651 Then 'IPC Admin' End As Type,
  tblSharePermissions.trustee,
  Case tblSharePermissions.readAccess When 1 Then 'Yes' When 0 Then 'No'
  End As [Read],
  Case tblSharePermissions.writeAccess When 1 Then 'Yes' When 0 Then 'No'
  End As Write,
  Case tblSharePermissions.fullAccess When 1 Then 'Yes' When 0 Then 'No'
  End As [Full],
  Case tblSharePermissions.denyAccess When 1 Then 'Yes' When 0 Then 'No'
  End As Denied,
  tblSharePermissions.lastChanged
From tblAssets
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  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
  Inner Join tblShares On tblAssets.AssetID = tblShares.AssetID
  Inner Join tblSharesUni On tblSharesUni.ShareUniqueID =
    tblShares.ShareUniqueID
  Inner Join tblSharePermissions
    On tblShares.ShareID = tblSharePermissions.ShareID
Where tblState.Statename = 'Active'
Order By tblAssets.Domain,
  tblAssets.AssetName,
  ShareCaption,
  tblSharePermissions.trustee