cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
SimplyClueless
Engaged Sweeper III
Ok- I have another one- director asked me for a report that-

I need a simple report (XLS) that lists Branch (including SPSC, MSC, and SSC) and number of OS and Office Licenses at each like:

Branch OS Office
branchname 15 12



"branch" in this context translates to "ip location"

A report that shows number of OS and Office licenses- sorted by ip location... does that make sense? not sure where to start
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
In order to generate a report you need to know what exactly you need to count.

Do you need to count different operating systems or just one number for any?

tblAssets.OScode contains an entry if the asset is a computer and has an Operating system. It is linked to tsysOS where you find the name of the OS.

tblSoftware contains scanned software installations on your network.

Or would you like to count distinct product keys? In that case you need to use tblSerialnumber, which contains each scanned Product Key and the AssetID on which it was scanned. You need to use a subquery to get distinct productkeys with join to tsysIPlocations and then count the ProductKeys.
The following report will count distinct OS product keys based on IP location of the assets they have been scanned from:


Select Top 1000000 OSKeys.IPLocation,
Count(OSKeys.ProductKey) As [OS Keys]
From (Select Distinct Top 1000000 tblSerialnumber.ProductKey,
tsysIPLocations.IPLocation
From tblSerialnumber
Inner Join tblAssets On tblAssets.AssetID = tblSerialnumber.AssetID
Inner Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Where tblSerialnumber.Product Like '%Windows%') OSKeys
Group By OSKeys.IPLocation

View solution in original post

5 REPLIES 5
Daniel_B
Lansweeper Alumni
We have answered your question about software installation count in this topic.
SimplyClueless
Engaged Sweeper III
Thanks very much for that- it is perfect; and now that this is done I am being asked for a list of actual installs- wishing I would have been given all the details in advance; I posted again here
---
Hello group- I have another software query I am trying to perfect that my admins are hounding me for; thanks as always for help- previously I was asked for a report for licenses per ip location for both MS office and MS Windows products. Now predictably; I am being asked for a count of all installations of Windows Operating system and Microsoft Office- per ip location.

Trying to make a software report that will elegantly push out to Excel that looks like :

Branch Windows OS Installs Office installs
Contoso HQ 11 12


hope that makes sense- thanks in advance
---

sorry for the spam
Daniel_B
Lansweeper Alumni
In that case you need two subqueries. The following report should give you the result you are heading for:


Select Top 1000000 OSKeys.IPLocation,
OSKeys.CountOSKeys As [OS Keys],
OfficeKeys.CountOfficeKeys As [Office Keys]
From (Select Distinct Top 1000000 Count(tblSerialnumber.ProductKey) As
CountOSKeys,
tsysIPLocations.IPLocation
From tblSerialnumber
Inner Join tblAssets On tblAssets.AssetID = tblSerialnumber.AssetID
Inner Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Where tblSerialnumber.Product Like '%Windows%'
Group By tsysIPLocations.IPLocation) OSKeys
Left Join (Select Distinct Top 1000000 Count(tblSerialnumber.ProductKey) As
CountOfficeKeys,
tsysIPLocations.IPLocation
From tblSerialnumber
Inner Join tblAssets On tblAssets.AssetID = tblSerialnumber.AssetID
Inner Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Where tblSerialnumber.Product Like '%Office%'
Group By tsysIPLocations.IPLocation) OfficeKeys On OSKeys.IPLocation =
OfficeKeys.IPLocation
Order By OSKeys.IPLocation
SimplyClueless
Engaged Sweeper III
This query provided is almost perfect- I think- I wonder is there a good way to add MS OFFICE license keys as well? not sure if I need a join or what-
Daniel_B
Lansweeper Alumni
In order to generate a report you need to know what exactly you need to count.

Do you need to count different operating systems or just one number for any?

tblAssets.OScode contains an entry if the asset is a computer and has an Operating system. It is linked to tsysOS where you find the name of the OS.

tblSoftware contains scanned software installations on your network.

Or would you like to count distinct product keys? In that case you need to use tblSerialnumber, which contains each scanned Product Key and the AssetID on which it was scanned. You need to use a subquery to get distinct productkeys with join to tsysIPlocations and then count the ProductKeys.
The following report will count distinct OS product keys based on IP location of the assets they have been scanned from:


Select Top 1000000 OSKeys.IPLocation,
Count(OSKeys.ProductKey) As [OS Keys]
From (Select Distinct Top 1000000 tblSerialnumber.ProductKey,
tsysIPLocations.IPLocation
From tblSerialnumber
Inner Join tblAssets On tblAssets.AssetID = tblSerialnumber.AssetID
Inner Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Where tblSerialnumber.Product Like '%Windows%') OSKeys
Group By OSKeys.IPLocation