I need help with reporting/SQL query for machines and day they were patched
I have been combing through IT Ninja and other forums for help with these specific things:
- Report to generate results by Machine Name
- Date patched
I am not currently using patch management yet, but it should still identify patches that have been manually applied through Windows Update, right? Also, will the report be able to pick up the date the machine was patched via Windows Update or does it pick up the date when Microsoft released the patch? I am trying to figure out the reporting and I am not very good with this yet. Any help is greatly appreciated.
Thank you,
J.R.
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
dugullett
10 years ago
The best way to start would be to create your patch labels, and then run a detect only against the machines you want. You will then be able to report on the detect date, and the patch release date. For me it's easier that way.
Comments:
-
Awesome. I will give that a whirl and let you know what I find!! Thank you very much!! - J.R. Colby 10 years ago
-
Another question........do you use one of the baked in reports or do you use the Wizard to create one? Again, I seem to pick the wrong fields to get what I am going for. - J.R. Colby 10 years ago
-
I generally create my own. There are a few examples on here, but they do rely on the "detect" status. If you need anything created let me know. - dugullett 10 years ago
-
Really? That would be awesome. I need to learn SQL a little better than what I know and familarize myself with the Kbox's db structure. What I want to do is look at my Remote users (RMT label) and check to see if patches were applied on a certain day. I did grab some SQL queries from off of here, but they didn't pull the results I was hoping for. Any help would be super.
Thank you dugullett!! - J.R. Colby 10 years ago-
Take a look at this, and let me know what changes you need. This is dependent on a "detect" schedule being ran. Unfortunately it will not show the date it was installed, but the date it was detected.
This will get you all the machines in your label, that have a status of "patched", and patches have been released in the past month.
SELECT PP.TITLE as 'Patch Title', P.STATUS_DT AS 'DETECT DATE',
M.NAME
FROM PATCHLINK_MACHINE_STATUS P
LEFT JOIN MACHINE M ON P.MACHINE_ID=M.ID
LEFT JOIN KBSYS.PATCHLINK_PATCH PP ON P.PATCHUID=PP.UID
LEFT JOIN MACHINE_LABEL_JT MLJT ON MLJT.MACHINE_ID = M.ID
LEFT JOIN LABEL L ON L.ID = MLJT.LABEL_ID
WHERE P.STATUS ='PATCHED'
AND L.NAME = 'RMT LABEL'
AND PP.RELEASEDATE > DATE_SUB(NOW(), INTERVAL 1 MONTH)
ORDER BY PP.TITLE, M.NAME - dugullett 10 years ago
