I'm new to Kace and have been asked to come up with a report that will pull out certain software from our software inventory. Here's the criteria 

Select certain software

Need to know what user has this certain software installed and metering if possible.. Any help or direction would be much appreciated. 

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

This will work. Replace Software_Name with the name of your software.

I don't currently use metering so I can't test that part. There is a METER_COUNTER table though so it should be able to be added.

SELECT M.NAME AS SYSTEM_NAME, IP, S.DISPLAY_NAME, USER_LOGGED USER,LAST_SYNC

FROM MACHINE M

LEFT JOIN MACHINE_SOFTWARE_JT ON M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID

LEFT JOIN SOFTWARE S ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID

WHERE (S.DISPLAY_NAME like 'Software_Name%')

ORDER BY M.NAME
 
Answered 09/18/2012 by: dugullett
Red Belt

  • Great let me see if this will work.. Thanks "Dugullett"
  • Do you think this is right for the metering table
    left join METER_COUNTER on METER.ID = METER_COUNTER.METER_ID AND
    METER_COUNTER.WINDOW_BEGIN > DATE_SUB(NOW(),INTERVAL 12 MONTH)
    left join SOFTWARE ON METER.SOFTWARE_ID = SOFTWARE.ID
    where
    M.ID = METER_COUNTER.MACHINE_ID
    GROUP BY METER.PROCESS_NAME, M.ID
    • It's hard to say without it being active for me. I'm not sure if METER.SOFTWARE_ID would be accurate since metering meters the processes. It would really depend on if METER.SOFTWARE_ID matched SOFTWARE.ID.

      What are the results when you add that in?
  • mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join METER_COUNTER on METER.ID = METER_COUNTER.METER_ID AND
    ME' at line 13] in EXECUTE(
    "SELECT M.NAME AS SYSTEM_NAME, S.DISPLAY_NAME, USER_LOGGED USER,LAST_SYNC

    FROM MACHINE M

    LEFT JOIN MACHINE_SOFTWARE_JT ON M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID

    LEFT JOIN SOFTWARE S ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID

    WHERE (S.DISPLAY_NAME like 'Software_Name%')

    ORDER BY M.NAME

    left join METER_COUNTER on METER.ID = METER_COUNTER.METER_ID AND
    METER_COUNTER.WINDOW_BEGIN > DATE_SUB(NOW(),INTERVAL 12 MONTH)
    left join SOFTWARE ON METER.SOFTWARE_ID = SOFTWARE.ID
    where
    M.ID = METER_COUNTER.MACHINE_ID
    GROUP BY METER.PROCESS_NAME, M.ID LIMIT 0")
    • Try this. Again I can't test it. If you're still having trouble I might enable it, because now I'm curious.

      SELECT M.NAME AS SYSTEM_NAME, S.DISPLAY_NAME, USER_LOGGED USER,LAST_SYNC
      FROM MACHINE M
      LEFT JOIN MACHINE_SOFTWARE_JT ON M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
      LEFT JOIN SOFTWARE S ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID
      left join METER_COUNTER MC on MC.MACHINE_ID = M.ID AND
      MC.WINDOW_BEGIN > DATE_SUB(NOW(),INTERVAL 12 MONTH)
      WHERE S.DISPLAY_NAME like 'Software_Name%'
      GROUP BY M.NAME
      ORDER BY M.NAME
  • Can you recommend any books to read and get up to speed on how to create Sql scripts?
    • These two links helped me. A lot of trial and error as well. I would also follow jverbosk he posts a lot of good blogs with different queries.

      http://www.itninja.com/link/w3schools
      http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example
  • So I tried it and the results show no metering. I have computer name, software display name, user and last sync
    • Take out the AND MC.WINDOW_BEGIN > DATE_SUB(NOW(),INTERVAL 12 MONTH) and see if that changes it.
Please log in to comment

Answers

-1

Do you have metering turned on for the selected application? I believe you have to enable it per application. We use a separate package to track software usage so I don't have experience with KACE's implementation.

Answered 09/19/2012 by: chucksteel
Red Belt

  • Yes I do. I created a smart label for it.
  • I just wanted to say thanks guys.. I got it to work.....
Please log in to comment
Answer this question or Comment on this question for clarity