Notification

Icon
Error

Full asset report with and without ad relationship

Posted: Friday, June 18, 2021 8:28:24 PM(UTC)
teddyh2o

teddyh2o

Member Original PosterPosts: 3
0
Like
I am trying to get a full report that shows assets with and without ad relationship.
My query currently just shows the assets that have a relation to tblAssetUserRelations.
If there isn't any relationship, i want show NULL if there isn't any.

here is my query.
Quote:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As AssetType,
tblAssetUserRelations.Username,
tblAssets.Scanserver
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetUserRelations On
tblAssets.AssetID = tblAssetUserRelations.AssetID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.Assettype = -1
Order By tblAssets.AssetName
Andy.S
#1Andy.S Member Posts: 97  
posted: 6/21/2021 10:45:13 AM(UTC)
Hi,

Is this what your after :

Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  Case
    When tblAssetUserRelations.Username Is Null Then 'No'
    Else 'Yes'
  End As Relationship,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssetUserRelations.Username,
  tblAssets.Scanserver
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblAssetUserRelations On
    tblAssets.AssetID = tblAssetUserRelations.AssetID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.Assettype = -1
Order By tblAssets.AssetName

Active Discussions

Lansweeper Device Duplicates
by  Ian.Prentice   Go to last post Go to first unread
Last post: 7/23/2021 11:33:00 AM(UTC)
Lansweeper Anti-Virus on Mac
by  Ian.Prentice  
Go to last post Go to first unread
Last post: 7/23/2021 9:04:13 AM(UTC)
Lansweeper Computers Listed as Users
by  td1020   Go to last post Go to first unread
Last post: 7/22/2021 5:16:19 PM(UTC)
Lansweeper Can not see second server in error
by  Jürgen  
Go to last post Go to first unread
Last post: 7/22/2021 3:47:19 PM(UTC)
Lansweeper OU missing
by  Fred   Go to last post Go to first unread
Last post: 7/22/2021 2:08:05 PM(UTC)
Lansweeper Certificate status & end dates
by  VysJamesk  
Go to last post Go to first unread
Last post: 7/22/2021 1:07:02 PM(UTC)
Lansweeper NIST 800-171 Compliance
by  rader  
Go to last post Go to first unread
Last post: 7/20/2021 10:13:17 PM(UTC)