Need help creating a software report
I am trying to create a report with the following information and am having a difficult time getting it to work. Any help would be greatly appreciated.
Total # Windows OS Systems
Total # MS Office by office version…
Total # Project
Total # Vision
Exclude systems with these products that have not reported in the last 30 days.
Answers (2)
This will probably require some tweaking, but is this something like what you are looking for? I wasn't for sure on the Vision part that will need to be adjusted so that you don't get everything with Vision in the title.
SELECT DISPLAY_NAME, DISPLAY_VERSION, GROUP_CONCAT(DISTINCT M.NAME ORDER BY M.NAME SEPARATOR '\n') AS MACHINE_NAME FROM SOFTWARE S LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID) LEFT JOIN MACHINE M ON (M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID) WHERE (NOT S.IS_PATCH) AND M.OS_NAME LIKE '%WINDOWS%' AND LAST_SYNC > DATE_SUB(NOW(),INTERVAL 30 DAY) AND (DISPLAY_NAME LIKE 'MICROSOFT OFFICE 2%' OR DISPLAY_NAME LIKE 'MICROSOFT OFFICE PROJECT%' OR DISPLAY_NAME LIKE '%VISION%') GROUP BY S.DISPLAY_NAME ORDER BY S.DISPLAY_NAME, S.DISPLAY_VERSION
Comments:
-
Vision should have been Visio, typo on my part. - jparkins 11 years ago
-
You had me wondering. I thought I might have missed a new Office product.
Change the Vision line to this.
OR DISPLAY_NAME LIKE 'MICROSOFT OFFICE VISIO%' - dugullett 11 years ago
-
This works to get exactly what I am looking for with one exception. I either need the MACHINE_NAME column to just provide the total count for that version. Or I need the MACHINE_NAME not to group all of the machines so I can get a count of the number of versions for each software title that is installed. Make sense?
SELECT DISPLAY_NAME,
GROUP_CONCAT(DISTINCT M.NAME ORDER BY M.NAME SEPARATOR '\n') AS MACHINE_NAME
FROM SOFTWARE S
LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID)
LEFT JOIN MACHINE M ON (M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID)
WHERE (NOT S.IS_PATCH)
AND M.OS_NAME LIKE '%WINDOWS%'
AND LAST_SYNC > DATE_SUB(NOW(),INTERVAL 30 DAY)
AND (DISPLAY_NAME LIKE 'MICROSOFT OFFICE PROFESSIONAL%'
OR DISPLAY_NAME LIKE 'MICROSOFT OFFICE PREMIUM%'
OR DISPLAY_NAME LIKE 'MICROSOFT PROJECT PROJECT PROFESSIONAL%'
OR DISPLAY_NAME LIKE '%MICROSOFT VISIO PROFESSIONAL%')
GROUP BY S.DISPLAY_NAME
ORDER BY S.DISPLAY_NAME, S.DISPLAY_VERSION - jparkins 11 years ago-
Since there's always more than one way I'll provide you with both. The first is more than likely what you are looking for.
SELECT DISPLAY_NAME, DISPLAY_VERSION,
COUNT(M.NAME) AS 'Machine Count'
FROM SOFTWARE S
LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID)
LEFT JOIN MACHINE M ON (M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID)
WHERE (NOT S.IS_PATCH)
AND M.OS_NAME LIKE '%WINDOWS%'
AND LAST_SYNC > DATE_SUB(NOW(),INTERVAL 30 DAY)
AND (DISPLAY_NAME LIKE 'MICROSOFT OFFICE 2010%'
OR DISPLAY_NAME LIKE 'MICROSOFT OFFICE PROJECT%'
OR DISPLAY_NAME LIKE 'MICROSOFT OFFICE VISIO%'
)
GROUP BY S.DISPLAY_NAME,S.DISPLAY_VERSION
ORDER BY S.DISPLAY_NAME, S.DISPLAY_VERSION
This one is broke out, and still includes that machine names if needed.
SELECT DISPLAY_NAME, DISPLAY_VERSION,
GROUP_CONCAT(DISTINCT M.NAME ORDER BY M.NAME SEPARATOR '\n') AS MACHINE_NAME
FROM SOFTWARE S
LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID)
LEFT JOIN MACHINE M ON (M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID)
WHERE (NOT S.IS_PATCH)
AND M.OS_NAME LIKE '%WINDOWS%'
AND LAST_SYNC > DATE_SUB(NOW(),INTERVAL 30 DAY)
AND (DISPLAY_NAME LIKE 'MICROSOFT OFFICE 2010%'
OR DISPLAY_NAME LIKE 'MICROSOFT OFFICE PROJECT%'
OR DISPLAY_NAME LIKE 'MICROSOFT OFFICE VISIO%')
GROUP BY S.DISPLAY_NAME,M.NAME
ORDER BY S.DISPLAY_NAME, S.DISPLAY_VERSION - dugullett 11 years ago
-
Made a couple small tweaks to better fit my environment. But this was exactly what I was looking for. THANK YOU
SELECT DISPLAY_NAME,
COUNT(M.NAME) AS 'Machine Count'
FROM SOFTWARE S
LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID)
LEFT JOIN MACHINE M ON (M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID)
WHERE (NOT S.IS_PATCH)
AND M.OS_NAME LIKE '%WINDOWS%'
AND LAST_SYNC > DATE_SUB(NOW(),INTERVAL 30 DAY)
AND (DISPLAY_NAME LIKE 'MICROSOFT OFFICE PROFESSIONAL%'
OR DISPLAY_NAME LIKE 'MICROSOFT PROJECT PROFESSIONAL%'
OR DISPLAY_NAME LIKE 'MICROSOFT VISIO PROFESSIONAL%'
OR DISPLAY_NAME LIKE 'MICROSOFT VISIO PREMIUM%'
)
GROUP BY S.DISPLAY_NAME
ORDER BY S.DISPLAY_NAME, S.DISPLAY_VERSION - jparkins 11 years ago
Personally I would do a separate report for each item. This would be very easy to do using the reports wizard. Doing one large report with all these things would be nearly impossible and would waste tons of your time versus doing them separate.
1. Software titles contains "Microsoft Windows", group by version.
2. Same as #1 but use "Microsoft Office" and some other conditions to filter out add-on software that contain that string - may take some work depending on how much other related software is in your environment.
3 and 4. Also same method as #1. Should be much easier to do this one since it is very specific software.
Now for the 30-day check-in limit. You will have to add a sub-topic for the report in the wizard to contain the Computer sub-topic. Then for filters, set the condition Last Sync is within 30 days.
If you absolutely must have one report will need someone else to chime in, my Kace-fu isn't that strong.
