K1000 Reporting: need report of patch compliance by device, not by patch
Hello. The built in patching reports list every single patch by compliance. These reports are huge and list literally every single thousands of patches on every device. I'm looking for something which lists each device instead of each patch, and count of patches, something that is more similar to the "Critical Patch Compliance" Widget on the K1000 homepage but a little more specific. My ultimate goal is to have a report that lists every computer and the count or the percentage of patched vs not patched critical patches. So instead of a huge report with every single patch, I'd have a nice simple report listing every one of my ~130 computers and the percentage of patched or not patched. Even better if computers could be grouped by a specific label.
SELECT PP.TITLE AS DISPLAY_NAME,
I'm not too familiar with the SQL code and for now confused as to what each item actually refers to but I'm trying. Is there a SQL legend which explains each item? Support says no.
An example of what I'm not looking for is the report "Devices compliant by patch"
SELECT PP.TITLE AS DISPLAY_NAME,
M.NAME AS ComputerName,
SYSTEM_DESCRIPTION, IP, MAC,
M.USER_LOGGED as USER_LOGGED,
CS_DOMAIN
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE MS.STATUS = 'NOTPATCHED'
ORDER BY PP.TITLE
If anybody has a report like this and can offer some help that would be great. In the meantime I'm going to play around with this report which is closer to what I am looking for:
https://support.software.dell.com/k1000-systems-management-appliance/kb/111710
Thanks.
0 Comments
[ + ] Show comments
Answers (3)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
8 years ago
Top Answer
I believe this report will capture what you want to report:
SELECT M.ID as Machine_ID, M.NAME AS Machine, M.KUID
, K.PHASE AS Phase, K.TYPE
, PSMS.PATCHED, PSMS.NOTPATCHED
, P.DESCRIPTION AS Description, P.LAST_RUN AS LAST_RUN
FROM MACHINE M
LEFT JOIN KBSYS.KONDUCTOR_TASK K ON K.KUID = M.KUID
LEFT JOIN PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS ON PSMS.MACHINE_ID = M.ID
LEFT JOIN PATCHLINK_SCHEDULE P ON P.ID = PSMS.PATCHLINK_SCHEDULE_ID
WHERE K.TYPE = 'patch-ORG1-3'
GROUP BY M.NAME
ORDER BY M.NAME
This report only includes one patching schedule (we have several) so it will need to be modified to report on your patching schedule. This line:
WHERE K.TYPE = 'patch-ORG1-3'
contains the ID of the patching schedule, in this case it is 3. You need to change the value of 3 to match the value of the patching schedule that you want to report on.
Comments:
-
Thanks Chuck. Good report. I also found the below report from this website which is basically exactly what I was looking for. It shows patched/unpatched counts plus a column for percentage patched.
http://www.itninja.com/blog/view/k1000-reports-patching-reports-for-completion-by-patch-machine-vendor-using-labels
SELECT M.NAME AS MACHINE_NAME,
USER_NAME,
OS_NAME AS OS_Name,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
+SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
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)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE PP.IMPACTID = 'Critical'
AND PPS.STATUS = 0
AND PP.IS_SUPERCEDED = 0
AND L.NAME rlike 'All Production Computers'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME - bens401 8 years ago-
Just what I was looking for. thanks - TechFreak 7 years ago
-
Chuck,
When you say "WHERE K.TYPE = 'patch-ORG1-3'
contains the ID of the patching schedule, in this case it is 3"
Is "3" the name of the Patch Schedule? So if I had a schedule named "Windows Critical Patch Run" the SQL would look like WHERE K.TYPE = 'patch-ORG1-Windows Critical Patch Run' or "WHERE K.TYPE = 'Windows Critical Patch Run'.
I couldn't get either to way to work for me, but does this still work on 6.4? - jacob.edwards 8 years ago-
Each patch schedule has an ID number. If you login to your appliance using /adminui instead of /admin then you can see the ID when editing a patching schedule. - chucksteel 8 years ago
-
You must be using a K2000? Because I don't see that anywhere on my patch schedules in the K1000. - jacob.edwards 8 years ago
-
I'm not sure what to tell you. Every patch schedule has an ID associated with it. If you are using the /adminui interface it should be in the URL. - chucksteel 8 years ago
-
Oh I see what you mean about the ID showing up in the URL now. I was looking everywhere but the URL.
Actually I figured this out by removing your WHERE K.TYPE = 'patch-ORG1-3' and running the report. Then I was able to create a different WHERE statement based on P.Description, which is the actual name of the Patch Schedule.
Now the report created shows a Type, which is the K.Type field, either labeled as "inventory" or "dellinv-ORG1-#'. What is the difference between these types? - jacob.edwards 8 years ago
Posted by:
CFassbender
4 years ago
Hi guys,
does anyone already got this SQL query working on Kace V10?
And would it share?
Comments:
-
I have one that reports on critical patches for V10.
SELECT
M.NAME AS MACHINE_NAME,
OS_NAME AS WINDOWS_VERSION,
LAST_USER as Last_User,
CLIENT_VERSION as Agent_Version,
M.Notes as Notes,
M.LAST_SYNC as Last_Inventory,
M.LAST_REBOOT as Last_Re_Boot,
SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,
SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,
FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS =
'PATCHED') + SUM(MS.DETECT_STATUS = 'NOTPATCHED'))) * 100,1)) AS
PERCENT_PATCHED
FROM
PATCH_MACHINE_STATUS MS
JOIN KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
JOIN PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN KBSYS.SMMP_CONNECTION SC ON (M.KUID = SC.KUID)
WHERE
PPS.STATUS = 0
and PP.SEVERITY = 'Critical'
AND PPS.IS_SUPERCEDED = 0
GROUP BY MS.MACHINE_ID
ORDER BY PERCENT_PATCHED , M.NAME - htharp@omig.com 4 years ago-
Wow, many thanks, that is exactly, what I am looking for.
To get recommended updates too, I changed the following line.
PP.SEVERITY = 'Critical' OR PP.SEVERITY = 'Recommended' - CFassbender 4 years ago -
Is this per last Detect/Deploy? Can we put in a time frame and say Last Month? Sorry, not SQL code savvy either - creusser 4 years ago
-
No, its an general overview of all requested Updates with the selected severity (critical, important,recommended, moderate, low) for the client. Feel free to change the PP.Severity like you want.
If everything is working fine the report should show mostely 100%, if not, you have to check which requested patches/updates are missing.
I use the following query to check the updates. including the systems last reboot and inventory. maybe it will help you.
For time frame when only last client sync was done it should be:
WHERE
(Last_Sync > CURDATE() - INTERVAL 30 DAY) AND
PPS.STATUS = 0
For updates it should be Creation_Date:_
WHERE (Creation_Date > CURDATE() - INTERVAL 30 DAY) AND
PPS.STATUS = 0
But I don´t think that updates within last 30 days is a good idea.
Full query for last inventory (Last Sync of client 30 days ago):
SELECT
M.NAME AS Computername,
OS_NAME AS Operatingsystem,
CASE M.OS_BUILD
WHEN '7600' THEN 'Windows 7'
WHEN '7601' THEN 'Windows 7 SP1'
WHEN '9600' THEN '2012R2'
WHEN '10240' THEN '1507 (RTM)'
WHEN '10586' THEN '1511'
WHEN '14393' THEN '1607'
WHEN '15063' THEN '1703'
WHEN '16299' THEN '1709'
WHEN '17134' THEN '1803'
WHEN '17763' THEN '1809'
WHEN '18362' THEN '1903'
WHEN '18363' THEN '1909'
ELSE 'Unknown OS Build'
END
AS 'Version',
LAST_USER as Username,
M.IP as IPAdress,
CLIENT_VERSION as Agentversion,
M.LAST_SYNC as LastInventory,
M.LAST_REBOOT as LastReboot,
SUM(MS.DETECT_STATUS = 'PATCHED') AS Installed,
SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS Notinstalled,
FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS =
'PATCHED') + SUM(MS.DETECT_STATUS = 'NOTPATCHED'))) * 100,1)) AS
Percent
FROM
PATCH_MACHINE_STATUS MS
JOIN KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
JOIN PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN KBSYS.SMMP_CONNECTION SC ON (M.KUID = SC.KUID)
WHERE (Last_Sync > CURDATE() - INTERVAL 30 DAY) AND
PPS.STATUS = 0
and PP.SEVERITY != 'low' AND PPS.IS_SUPERCEDED = 0
GROUP BY MS.MACHINE_ID
ORDER BY Percent , M.NAME - CFassbender 4 years ago
-
This is great, however it only shows for windows computers how do i get to show for macOS computers as well or just macOS computers?
Sorry i cant see where to change the code for this - markc0 4 years ago-
sorry just noticed the ones for macOS was not listed as critical which would explain this - markc0 4 years ago
-
Is there a way to get the PCs that have not needed any "critical" yet from Microsoft to show up in the report still. issue i am having is that 192 of our systems are on win 10 1909 which as of yet has not needed any "critical" patches and thus do not show in the report at all. I would like those pcs to show up in the report with 0 available 0 needed 100% compliant for audit purposes. - isaiahcoughlin 3 years ago
Posted by:
j.catlin@nmscolo.org
4 years ago
Hi,
This report was built for V10 Kace. It lists device, windows version, patched number, un-patched number minus our 12 day grace period for "new" patches, Percentage patched with the same 12 day grace period and device up time. It breaks the group of devices into two manual label groups. OS_NAME AS WINDOWS_VERSION,8I thought you might find it handy to use and configure for your system.
SELECT
M.NAME AS MACHINE_NAME,
OS_NAME AS WINDOWS_VERSION,
SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,
SUM(MS.DETECT_STATUS = 'NOTPATCHED' AND DATE_FORMAT(PP.CREATION_DATE, '%Y-%m-%d') > DATE_SUB(NOW(),INTERVAL 12 DAY)) AS NOTPATCHED,
FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS = 'PATCHED') + (SUM(MS.DETECT_STATUS = 'NOTPATCHED' AND DATE_FORMAT(PP.CREATION_DATE, '%Y-%m-%d') > DATE_SUB(NOW(),INTERVAL 12 DAY))))) * 100,1)) AS PERCENT_PATCHED,
(CONCAT(SUBSTRING_INDEX(M.UPTIME, ',', 1), ' days, ', SUBSTRING(M.UPTIME, LOCATE(',', UPTIME) + 1, LOCATE(':', M.UPTIME) - LOCATE(',', M.UPTIME) - 1), ' hours, ', SUBSTRING_INDEX(M.UPTIME, ':', -1), ' minutes')) AS UPTIME,
LABEL.NAME AS LABEL
FROM
PATCH_MACHINE_STATUS MS
M.NAME AS MACHINE_NAME,0
M.NAME AS MACHINE_NAME,1
M.NAME AS MACHINE_NAME,0
M.NAME AS MACHINE_NAME,3
M.NAME AS MACHINE_NAME,0
M.NAME AS MACHINE_NAME,5
M.NAME AS MACHINE_NAME,0
M.NAME AS MACHINE_NAME,7
M.NAME AS MACHINE_NAME,0
M.NAME AS MACHINE_NAME,9
M.NAME AS MACHINE_NAME,0
OS_NAME AS WINDOWS_VERSION,1
OS_NAME AS WINDOWS_VERSION,2
OS_NAME AS WINDOWS_VERSION,3
OS_NAME AS WINDOWS_VERSION,4
OS_NAME AS WINDOWS_VERSION,5
OS_NAME AS WINDOWS_VERSION,6
OS_NAME AS WINDOWS_VERSION,7
OS_NAME AS WINDOWS_VERSION,8
OS_NAME AS WINDOWS_VERSION,9
SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,0
Comments:
-
Hey ... thx ... just a question, when you say " LABEL.NAME AS LABEL" that is the Patch Label or Device Label ? thanks in advance!!! - benrras 3 years ago
