→ 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: 
dbasinge
Engaged Sweeper II
I'm needing help creating a report, since the builtin one do not seem to cover this.

Need
- Name
- OS (Windows 7/8/etc, OS X, Linux)
- 32 or 64 bit OS
- Manufacturer
- Serial
- Processor
- baseboard
- Memory (total)
- IP Address
- MAC

This give me most things I need except OS Version (Windows 7, OS X 10.10, etc) and 32 or 64


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.OScode,
tblAssets.Processor,
tblAssets.Memory,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.IPAddress,
tblAssets.Mac
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1


Also most of the reports only show Windows computers in the domain, how can I add OS X and Linux computer to other reports.

Thanks,
Mike
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
You'll find the OS name in tsysOS and you can determine the bitness by examining tblComputerSystem.SystemType.

You'll need to manually link tblAssets to tsysOS on OSCode, as the report developer doesn't figure that out automatically.
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.OScode,
tblAssets.Processor,
tblAssets.Memory,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.IPAddress,
tblAssets.Mac,
tsysOS.OSname,
CASE
WHEN tblComputersystem.SystemType LIKE 'X86%' THEN '32-bit'
WHEN tblComputersystem.SystemType LIKE 'x64%' THEN '64-bit'
ELSE ''
End AS OSBitness
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
WHERE
tblAssetCustom.State = 1

View solution in original post

3 REPLIES 3
RCorbeil
Honored Sweeper II
That's the INNER JOINs killing you, then. Replace them with LEFT JOINs against tsysOS and tblComputerSystem. (You'll have to manually edit the query. The report builder defaults to INNER JOINs.)

With the change, you should see NULL results for the OSname and the OS bitness for your non-Windows machines. (Assuming OS name isn't collected for Linux and OS X. My inventory is all Windows, so I can't verify.)
dbasinge
Engaged Sweeper II
I lose all assets then except Windows machine, need to get OS X and Linux also.
RCorbeil
Honored Sweeper II
You'll find the OS name in tsysOS and you can determine the bitness by examining tblComputerSystem.SystemType.

You'll need to manually link tblAssets to tsysOS on OSCode, as the report developer doesn't figure that out automatically.
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.OScode,
tblAssets.Processor,
tblAssets.Memory,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.IPAddress,
tblAssets.Mac,
tsysOS.OSname,
CASE
WHEN tblComputersystem.SystemType LIKE 'X86%' THEN '32-bit'
WHEN tblComputersystem.SystemType LIKE 'x64%' THEN '64-bit'
ELSE ''
End AS OSBitness
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
WHERE
tblAssetCustom.State = 1