K1000 reports based on certain software
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.
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
dugullett
12 years ago
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
Comments:
-
Great let me see if this will work.. Thanks "Dugullett" - shawnvic71 12 years ago
-
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 - shawnvic71 12 years ago-
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? - dugullett 12 years ago
-
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") - shawnvic71 12 years ago-
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 - dugullett 12 years ago
-
Can you recommend any books to read and get up to speed on how to create Sql scripts? - shawnvic71 12 years ago
-
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 - dugullett 12 years ago
-
So I tried it and the results show no metering. I have computer name, software display name, user and last sync - shawnvic71 12 years ago
-
Take out the AND MC.WINDOW_BEGIN > DATE_SUB(NOW(),INTERVAL 12 MONTH) and see if that changes it. - dugullett 12 years ago
Posted by:
chucksteel
12 years ago
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.
Comments:
-
Yes I do. I created a smart label for it. - shawnvic71 12 years ago
-
I just wanted to say thanks guys.. I got it to work..... - shawnvic71 12 years ago
