I received an interesting ask today... How do I show what versions of MS Office are installed at each of my sites, by site.

Using the reporting wizard in the K1000 I was able to build a report to show one site at a time using the Device and Software Catalog Discovered Suites topics, but I wanted everything in one report. I looked at what the wizard gave me and changed things a little bit and included a CASE statement in my MYSQL. Take a look, steal it, improve it. I am using IPs to define my different sites (I don't have too many subnets), if you have a large number of subnets and don't want to build a case for each one, you might want to change that bit to use label names instead (assuming you've got smart labels for all of your sites. You do, right?).

I have a couple fields commented out because they didn't give me the info I was looking for (one just showed "Office" and the other showed "2010" or "2014" etc. depending on the version.) You can add in any other fields from the machine table without major mods to the SQL. The last line of the SQL is where I define "office" as what I am looking for, you can very easily change it to almost anything else (remember I am looking at suites though so something like .net would no show up.) Just change the CASE statement for your own environment, subnets and location names, and paste into your K1000.

I am running K1000 version 6.2.

Hope this is helpful! Let me know in the comments :)

SQL:

SELECT MACHINE.NAME AS 'COMPUTER NAME', MACHINE.IP AS 'IP', SAM_VIEW_DISCOVERED_SUITES.NAME AS 'SOFTWARE',
/*
SAM_VIEW_DISCOVERED_SUITES.PRODUCT_NAME, SAM_VIEW_DISCOVERED_SUITES.MAJOR_VERSION,
*/
CASE WHEN MACHINE.IP LIKE '192.168.0.%' THEN 'OFFICE 1'
    WHEN MACHINE.IP LIKE '192.168.1.%' THEN 'OFFICE 2'
    WHEN MACHINE.IP LIKE '192.168.2.%' THEN 'OFFICE 3'
    ELSE MACHINE.IP
END AS LOCATION
FROM SAM_VIEW_DISCOVERED_SUITES 
LEFT JOIN SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE ON SAM_VIEW_DISCOVERED_SUITES.ID = SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE.ID
LEFT JOIN MACHINE ON MACHINE.ID = SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE.MACHINE_ID
WHERE SAM_VIEW_DISCOVERED_SUITES.NAME like '%office%'

Break on Columns:

Location