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!
Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • 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.
Please log in to comment

Answer Chosen by the Author

0
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

Answered 04/27/2015 by: gmanweiler
White Belt

Please log in to comment

Answers

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

 

 

Answered 02/27/2015 by: Badger
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity