Custom Software License Report

[solaadmin]

I am looking for a custom report that will display the same information as the "Software License Compliance" report but want it to include all the server/desktops names that have the software installed based with the license key beside it. The example below is something similar to what I am looking for.

For Example:

Microsoft Office Home and Business 2010
(ComputerName) (KeyLicenseNumber)
(ComputerName) (KeyLicenseNumber)
(ComputerName) (KeyLicenseNumber)

Windows Server 2008 R2
(ServerName) (KeyLicenseNumber)
(ServerName) (KeyLicenseNumber)
(ServerName) (KeyLicenseNumber)
(ServerName) (KeyLicenseNumber)

The "Software License Compliance" report already has all the software I am looking to report on, just need to elaborate on the output and criteria.

Thanks!

[solaadmin]

Awesome!

Thanks for the quick response and help!

Cheers!

Luc

[RCorbeil]

You only listed Microsoft Office and Windows Server in your example. I included those in the query. You can expand on that list with more names in the WHERE clause.

Where
  tblAssetCustom.State = 1
  AND (    tblSerialnumber.Product LIKE 'Microsoft Office%'
        OR tblSerialnumber.Product LIKE 'Windows Server%'
        OR tblSerialnumber.Product LIKE 'another product name%'
        OR tblSerialnumber.Product LIKE 'something else%'
        OR tblSerialnumber.Product LIKE 'yet another product%'
      )

If you want to match the product name exactly, replace LIKE with = and remove the % (the wildcard). e.g.

OR tblSerialnumber.Product = 'Microsoft Office Home and Business 2010'

If you want to match anywhere within the product name, surround your match text with %, e.g.

OR tblSerialnumber.Product LIKE '%Home and Business%'

[solaadmin]

This is brilliant but not quite there yet. What about all the other software licenses I identified in the compliance report. Is there away to include those in the same report?

[RCorbeil · Accepted solution]

Something like this?

Select Top 1000000
  tblSerialnumber.Product,
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblSerialnumber.ProductKey,
  tsysOS.Image As icon
From
  tblAssets
  Inner Join tblSerialnumber On tblAssets.AssetID = tblSerialnumber.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where
  tblAssetCustom.State = 1
  AND (    tblSerialnumber.Product LIKE 'Microsoft Office%'
        OR tblSerialnumber.Product LIKE 'Windows Server%'
      )
Order By
  tblSerialnumber.Product,
  tblAssets.AssetUnique,
  tblSerialnumber.ProductKey

A minor variation on License: Software licensekey overview.

The "reports" that LANSweeper generates are just tabular views of data, so you're not going to get things grouped under headings the way you illustrate. You can export the LANSweeper report to Excel and massage it there for appearance.