Query for OS and Office

[SimplyClueless]

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

[Daniel_B]

We have answered your question about software installation count in this topic.

[SimplyClueless]

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]

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]

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 · Accepted solution]

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