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) 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:
Please log in to answer
Posted by:
ondrar
7 years ago
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
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
/*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
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