/build/static/layout/Breadcrumb_cap_w.png

How to Show How Many Office Installs and What Version of Office per Site

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

Comments

  • This is awesome. I'm using it now. Thanks for your help with this.

    1 thing I can contribute that maybe someone else won't know. For Break on Columns: Location - when you use that in your K1000 it must be in all caps - LOCATION or your report will error out.

    Thanks again Erik,
    Matt - it_matt 9 years ago
    • Thanks for the tip on the Break on Columns, good to call out. I'm glad to hear it helped you! :) - erik.ragan 9 years ago
  • I know this is an older post, but this is so close to what I need it's painful! Can someone help me mod this to include specific device names, not just all devices? - grumpyguy6 6 years ago
This post is locked
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ