→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jeost
Engaged Sweeper
Hi, Im having trouble makeing a report to gather som information. Im trying to make a reprot that shows AssetName AssetTypename, Memory, NrProcessors, Processor ,Mac ,Location, Manufacturer,username, and userdepartment. It is the user depart men i am haven problems getting to work.

This is what i got so far:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssets.Memory,
tblAssets.NrProcessors,
tblAssets.Processor,
tblAssets.Mac,
tblAssetCustom.Location,
tblAssetCustom.Manufacturer,
tblAssets.Username
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1

Regradt Jeost
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
The user department is scanned from Active Directory and stored in tblADusers. You need to add this table to your report, join it through the columns Username and Userdomain to tblAssets and select the user department to be listed in your report.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssets.Memory,
tblAssets.NrProcessors,
tblAssets.Processor,
tblAssets.Mac,
tblAssetCustom.Location,
tblAssetCustom.Manufacturer,
tblAssets.Username,
tblADusers.Department
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblAssetCustom.State = 1

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
The user department is scanned from Active Directory and stored in tblADusers. You need to add this table to your report, join it through the columns Username and Userdomain to tblAssets and select the user department to be listed in your report.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssets.Memory,
tblAssets.NrProcessors,
tblAssets.Processor,
tblAssets.Mac,
tblAssetCustom.Location,
tblAssetCustom.Manufacturer,
tblAssets.Username,
tblADusers.Department
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblAssetCustom.State = 1