→ 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: 
saziz
Engaged Sweeper
Someone created an report that we use to import assets into our CMDB. The report is created with the query below.
I have some assets that do not show up in this report but appear in Lansweeper when I type the asset name.
How do I make those show up in this report? What am i missing in this query that is excluding those assets?



Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.IPAddress,
tblAssetCustom.Warrantydate,
tblAssetCustom.Serialnumber
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

4 REPLIES 4
cjcox
Engaged Sweeper III
Perhaps not all assets are Windows? Take a look at: https://www.lansweeper.com/forum/yaf_postst16421_A-Better-OS-Report.aspx and see if that works better... feel free to adjust to your needs.
saziz
Engaged Sweeper
cjcox wrote:
Perhaps not all assets are Windows? Take a look at: https://www.lansweeper.com/forum/yaf_postst16421_A-Better-OS-Report.aspx and see if that works better... feel free to adjust to your needs.


They're all windows servers with Windows OS
saziz
Engaged Sweeper
Didn't work.
jutley
Engaged Sweeper III
Try fiddling around with the "Where" clause. tblAssetCustom.State = 1 is all active items.
tblComputersystem.Domainrole > 1 is going to show servers. See list of DomainRoles below.

DomainRole

Value Meaning
0 (0x0) Standalone Workstation
1 (0x1) Member Workstation
2 (0x2) Standalone Server
3 (0x3) Member Server
4 (0x4) Backup Domain Controller
5 (0x5) Primary Domain Controller

Assuming you are only wanting to see active assets, I would start with replacing the 2nd to the last line with:

Where tblAssetCustom.State = 1