Question on Available Reports
Couple of questions regarding some reports that I need.
First, when viewing a computer in Inventory, it will list the hardware and software changes. Is there a report that I can generate that will list these? If not, can someone point me in the direction what I need to create this report?
Second, I have a custom report that lists patches installed in the past X amount of days:
SELECT PP.TITLE AS PATCH_NAME,
M.NAME AS COMPUTER_NAME,
PP.IDENTIFIER AS KB_ARTICLE
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE MS.STATUS = 'PATCHED'
AND (DEPLOY_STATUS_DT > CURDATE() - INTERVAL 7 DAY)
ORDER BY M.NAME
Will this only list the patches installed by KACE, or will it list the Windows update if performed manually?
Answers (1)
You have a couple of different options for the history of the machine. It really depends on what you are looking for. Try something like this below to see what you are looking for.
SELECT * FROM ASSET_HISTORY A WHERE NAME LIKE '<Machine_Name>%'
From there limit it to what you are looking for.
SELECT * FROM ASSET_HISTORY A WHERE CHANGE_TYPE = 'MODIFICATION' AND NAME LIKE '<Machine_Name>%'
As far as the patching report goes. That will be based off of your detect schedule. If it's in your patch label, part of a patch schedule, and is showing as "patched" after the schedule runs then it will show. The only problem is if you are not using Kace to deploy patches then there will be no DEPLOY_STATUS_DT which will more than likely return 0 results.
Comments:
-
This is an example you can use for all new software installed in the past 7 days.
SELECT NAME, TIME, VALUE1 AS Software, VALUE2 AS Version
FROM ASSET_HISTORY A
WHERE FIELD_NAME = 'SOFTWARE'
AND CHANGE_TYPE = 'ADDITION'
AND TIME > DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY NAME - dugullett 11 years ago -
Thanks! Both responses have me going in the right direction. I patched my computer using Windows Updates for 3 updates, however, the report only shows 1 software modification. The other two probably don't register as any type of modification, so that may be out the question.
However, for reporting all other modifications, I think this will work. Right now I'm selecting all the fields for the query. Where can I find the column field names to narrow the report results. Right now I get some columns such as Asset ID, User ID, etc. that I'm not interested in. - jeffwbrown 11 years ago-
The easiest way is change the query to SELECT *. I'm wondering on the patch report are the other two showing as not patched? - dugullett 11 years ago
-
I'm already using SELECT *, but I want to narrow it down to just a few columns to select but I do not know the column names as they are in the database. Where can I find these? - jeffwbrown 11 years ago
-
Sorry I misunderstood. They'll be at the top. I.e. SELECT NAME, TIME, CHANGE_TYPE. You can also download the MYSQL workbench. That will give you a lot more info. - dugullett 11 years ago
