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 Monitor history showing only last monitors
by  cross_eur   Go to last post Go to first unread
Last post: 7/23/2021 6:06:51 PM(UTC)
Lansweeper Report Login time reduces number of computers by 300
by  cross_eur  
Go to last post Go to first unread
Last post: 7/23/2021 6:05:42 PM(UTC)
Lansweeper Merging 2 reports
by  Apaulcolypse   Go to last post Go to first unread
Last post: 7/22/2021 10:02:59 PM(UTC)
Lansweeper HELP - Add Registry Key Values to Asset Report
by  Apaulcolypse  
Go to last post Go to first unread
Last post: 7/22/2021 9:26:43 PM(UTC)
Lansweeper Can I request a custom report here?
by  Brian G   Go to last post Go to first unread
Last post: 7/22/2021 7:20:56 PM(UTC)
Lansweeper List all users with E-mail address
by  Brandon  
Go to last post Go to first unread
Last post: 7/21/2021 7:06:36 PM(UTC)
Lansweeper Identifying users of Windows legacy authentication
by  Baronet   Go to last post Go to first unread
Last post: 7/21/2021 5:26:38 PM(UTC)
Lansweeper Windows Version different between reports
by  RC62N  
Go to last post Go to first unread
Last post: 7/21/2021 3:27:04 PM(UTC)