Need help with SQL Syntax - OS & License Keys

[damien_law]

Anyone can help in generating a script to pull the following information using v5 RC.

1. Hostname
2. IP
3. Domain
4. OS
5. OS License Keys
6. Office
7. Office License Keys

Thanks in advance.

[Hemoco · Accepted solution]

Please use the report below for the information you are after.

Select Top 1000000 tblAssets.AssetID,
tsysOS.Image As icon,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tsysOS.OSname,
tblOperatingsystem.Caption As FullOSName,
tblSerialnumber.Product,
tblSerialnumber.ProductID,
tblSerialnumber.ProductKey,
tblSerialnumber.Lastchanged
From tblAssets
Inner Join tblSerialnumber On tblAssets.AssetID = tblSerialnumber.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Where (tblSerialnumber.Product Like '%windows%') Or
(tblSerialnumber.Product Like '%office%')
Order By tblAssets.AssetUnique,
tblSerialnumber.Product

To use the specified report, do the following:
• Browse to the Dashboard\Reports\Report Builder section of the web console.
• Paste the SQL code we provided at the bottom of the page.
• Left-click somewhere in the upper section of the page so the code applies.
• Give the report a Title and hit the Save & Run Report button to save it.
• The report will now also be listed under Dashboard\Reports\All Reports.