cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
solaadmin
Engaged Sweeper
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!
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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.

View solution in original post

4 REPLIES 4
solaadmin
Engaged Sweeper
Awesome!

Thanks for the quick response and help!

Cheers!

Luc
RCorbeil
Honored Sweeper II
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
Engaged Sweeper
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
Honored Sweeper II
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.