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.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

4

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
 
Answered 05/29/2013 by: dugullett
Red Belt

  • Vision should have been Visio, typo on my part.
    • 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%'
  • 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
    • 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
  • 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
Please log in to comment

Answers

0

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.

Answered 05/29/2013 by: adamscottmartin
Yellow Belt

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment
Answer this question or Comment on this question for clarity