‎08-09-2017 01:32 PM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now