Need help with a report

[dbasinge]

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

[RCorbeil]

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]

I lose all assets then except Windows machine, need to get OS X and Linux also.

[RCorbeil · Accepted solution]

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