Custom report - adding OS output

[alexb]

Hi,

Fairly new to this, but loving the product.
I have written a custom report but struggling to get OS in the report after model, I want it to show "Windows 7" and so on....

Can anyone help? report below:

Select Top 1000000 tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Processor,
tblAssetCustom.PurchaseDate,
tblOperatingsystem.InstallDate,
tblAssetCustom.Warrantydate,
tblAssetCustom.Serialnumber,
tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.State = 1

[RCorbeil]

The query builder is smart enough to automatically associate most of the tables you're likely to work with. It doesn't happen to do so with tSysOS.

• Add tSysOS to the query designer and it will show up without any links.
  
• Scroll down tblAssets until you see the OSCode field.
  
• Click on that and drag-and-drop to the OSCode field in tSysOS and a link should be established.
  

Down in the code window, you should see "Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode" appear.

As to the Office version, that has been addressed here.

[alexb]

Amazing thanks, adding tblOperatingSystem.Caption worked a treat. I dont know how to manually link though - it's not the end of the world.

Also, do you know the variable for Office version - if that exists.

Thanks again

[RCorbeil · Accepted solution]

You've got most of what you need. Just add tblOperatingSystem.Caption to your selected fields.

Note that this will provide a "long name" for the operating system, e.g. "Microsoft Windows 7 Enterprise", "Microsoft Windows 7 Professional", "Microsoft Windows Server 2012 R2 Datacenter". If you would prefer a shorter, less precise name, e.g. "Win 7", "Win 2012 R2", drop tblOperatingSystem from your query and add tSysOS. You'll need to manually link tSysOS.OSCode to tblAssets.OSCode. Once that's done, add tSysOS.OSName to your query.