/build/static/layout/Breadcrumb_cap_w.png

query for all installations of MS Office and usage data for each installations

we would like to optimize our MS Office suite licensing;
1) I have a sql query that shows me all of the installations of MS Office Standard 2010 (user full name, computer name, ip, location)
2) I have a sql query that shows me all of the usage stats ( total launches, hours used, last launch date) of those installations
3) I have a sql query that shows me all of the users who have multiple installations of MS Office (1 per machine)

What I need is to know is the usage stats for all machines, including NO usage at all.
We are looking especially at users with multiple installations - the usage stats will support the business case for un-installation AND/OR purchasing more licenses.

thanks!

1 Comment   [ + ] Show comment
  • Some users do have multiple machines (for instance, they travel to a different site and have a computer with an installation of MS Office there) or may log on to other machines (kiosk machines for many users use). We are not tracking the particular exe's - we just track any usage of an MS Office suite application. Volume wise, we only have ~270 installations of MS Office. - gmanweiler 9 years ago

Answers (3)

Answer Summary:
Posted by: ondrar 7 years ago
Black Belt
1
I was looking to accomplish much the same task, having a report to list computers with a piece of software installed, and a report to list metering for said software, but I could not get a combined report.  So I stared with what gmanweiler posted, but took it another step further.

It appears that in gmanweiler's environment, Office 2010 is the only software being metered, because when I ran the report as posted, I got wildly inconsistent numbers.  After some investigation, I determined that this was because it was reporting ALL metering data for a given machine, not just for the software in question.

The second challenge I had, was that I wanted to limit the report to a certain time period, since the implications of a certain piece of software being used for 100 hours a year ago are different than it being used for 100 hours a month ago, and I couldn't get that to work with this report.

So here is my take on software metering, including zeros, using sub-selects to address the issues outlined above.


SELECT 
  MACHINE.NAME AS SYSTEM_NAME, 
  BIOS_SERIAL_NUMBER as 'Service Tag', 
  USER_FULLNAME as 'User Full Name', 
  MACHINE.USER_NAME as 'Username', 
  CATALOG.SAM_CATALOG.PUBLISHER as 'Publisher',
  CATALOG.SAM_CATALOG.PRODUCT_NAME as 'Product Name',
  CATALOG.SAM_CATALOG.NAME as 'Software Name',
  IFNULL((SELECT count(SMD.ID)
   FROM SAM_METER_DATA SMD
   JOIN SAM_VIEW_TITLED_SOFTWARE SVTS on SMD.TITLED_APPLICATION_ID = SVTS.ID
   JOIN MACHINE on SMD.MACHINE_ID = MACHINE.ID
   WHERE 
    MACHINE.NAME = SYSTEM_NAME 
    and SVTS.NAME like 'Project 20%' -- Software title
    and date(START) BETWEEN DATE_SUB(NOW(),INTERVAL 90 DAY) AND NOW() -- between ## days ago and now
  GROUP BY 
   MACHINE.NAME
  ),0) as LAUNCH_COUNT,
  IFNULL((SELECT ROUND(SUM(SMD.SECONDS_USED) / 3600, 2)
   FROM SAM_METER_DATA SMD
   JOIN SAM_VIEW_TITLED_SOFTWARE SVTS on SMD.TITLED_APPLICATION_ID = SVTS.ID
   JOIN MACHINE on SMD.MACHINE_ID = MACHINE.ID
   WHERE 
    MACHINE.NAME = SYSTEM_NAME 
    and SVTS.NAME like 'Project 20%' -- Software title
    and date(START) BETWEEN DATE_SUB(NOW(),INTERVAL 90 DAY) AND NOW() -- between ## days ago and now
  GROUP BY 
   MACHINE.NAME
  ),0) as HOURS_USED
 
 FROM MACHINE  
 
 LEFT JOIN SAM_MACHINE_JT on MACHINE.ID = SAM_MACHINE_JT.MACHINE_ID  
 LEFT JOIN CATALOG.SAM_CATALOG ON CATALOG.SAM_CATALOG.ID = SAM_MACHINE_JT.SAM_CATALOG_ID 
 
 WHERE
   CATALOG.SAM_CATALOG.NAME like 'Project 20%' -- Software title

 GROUP BY CATALOG.SAM_CATALOG.NAME, MACHINE.ID 

 ORDER BY LAUNCH_COUNT ASC


Hope this helps someone.
Posted by: gmanweiler 9 years ago
White Belt
1

Top Answer

Finally, got something  working. This query reports usage for MS Office Standard 2010.

/*Table Aliases
M = MACHINE
L = LABEL
S = SOFTWARE
SMD = SAM_METER_DATA
MSJT = MACHINE_SOFTWARE_JT
MLJT = MACHINE_LABEL_JT
*/
/*returns launch stats for all users with MS Office Std 2010 installed*/
SELECT
M.USER_FULLNAME AS "User Name",
L.NAME AS "Sites",
M.NAME AS "Computer Name",
M.IP AS "IP",
/*
if user has MS OFFICE installed, but has never launched,
then will get defualt values, else will get actual formatted stats
*/
IF(SMD.START IS NULL, 'Unused', MAX(DATE_FORMAT(SMD.START, '%Y-%b-%d') ) )AS "Last Launched",
IF(SMD.LAUNCHES IS NULL, 0, SUM(SMD.LAUNCHES) ) AS "Launches",
IF(SMD.SECONDS_USED IS NULL, 0.0, ROUND(SUM(SMD.SECONDS_USED) / 3600.0, 2) ) AS "Hours Used"

FROM MACHINE AS M
/*need left join to ensure nulls inserted for users who never launched Office*/
LEFT JOIN SAM_METER_DATA AS SMD ON (SMD.MACHINE_ID = M.ID)
LEFT JOIN MACHINE_SOFTWARE_JT AS MSJT ON (MSJT.MACHINE_ID = M.ID)
LEFT JOIN SOFTWARE AS S ON (S.ID = MSJT.SOFTWARE_ID)
LEFT JOIN MACHINE_LABEL_JT AS MLJT ON (MLJT.MACHINE_ID = M.ID)
LEFT JOIN LABEL AS L ON (L.ID = MLJT.LABEL_ID)

WHERE EXISTS
  (
    /*Subquery returns machine id where MS Office 2010 STD is installed*/
    SELECT M.ID
    FROM MACHINE AS M
    LEFT JOIN MACHINE_SOFTWARE_JT AS MSJT ON (MSJT.MACHINE_ID = M.ID)
    LEFT JOIN SOFTWARE AS S ON (S.ID = MSJT.SOFTWARE_ID)
    WHERE S.DISPLAY_NAME LIKE '%Microsoft Office Standard 2010%'
  )
  /*specifies sites and MS Office 2010 Std*/
  AND (L.TYPE != 'hidden' )
  AND (L.NAME='EDM' OR L.NAME='FC' OR L.NAME='BOY' OR L.NAME='WW' OR L.NAME='WP')
  AND S.DISPLAY_NAME LIKE '%Microsoft Office Standard 2010%'

GROUP BY M.ID
ORDER BY SMD.LAUNCHES ASC, M.USER_FULLNAME, SMD.SECONDS_USED ASC

Posted by: Badger 9 years ago
Red Belt
0

Good luck with that.
Do your users swap devices????

If they do you could have people on a device who do not use a version of office, but one person who logs on (once a month, or every two months) and uses an older version for an hour.

I would suggest getting rid of the old versions (what is the range you have) and gently nudge them to a higher version.

Are you tracking the actual EXEs that people are launching?
The really tricky thing to watch for is Access. Weird things happened with MDB files through the versions. Most of the other apps cope OK with the version change and file extensions.

Your next major headache would be Excel, if you have heavy users with creative methods of writing Macros etc.

I vote for you to move forwards with the removals, keep a track of your reports, just in case.

I have done what you are doing, I did a lot less reporting, I just uninstalled the bits I didn't want. It was a relatively small environment, 2000 users, every body was panicking and had an opinion about it. I proceeded and had about 10 calls logged, fixed them nice and quickly. They were expecting about 5000 calls. Be brave. Good luck

 

 

 
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