Smart Label to detect machines with deploy status date older than 5 days
I have created this report that will show machines that have a deploy status older than 5 days past the current date. I am trying to create a smart label that will do the same thing but the SQL query doesn't appear to work for the smart label. The purpose of this would be to allow for tiered patching at night and users that don't leave their computers on at night will be forced in to the thursday daytime patching. The thursday day time patching would have the new label so if anyone DID patch at night they would "fall out of" the label and not be forced to run patching during the day thursday. Below is the SQL Query:
SELECT DISTINCT MACHINE.NAME as MACHINE_NAME FROM PATCHLINK_MACHINE_STATUS left join KBSYS.PATCHLINK_PATCH on KBSYS.PATCHLINK_PATCH.UID = PATCHLINK_MACHINE_STATUS.PATCHUID left join MACHINE on MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID WHERE ((DEPLOY_STATUS_DT > curdate()-5)) GROUP BY PATCHLINK_MACHINE_STATUS.MACHINE_ID,PATCHLINK_MACHINE_STATUS.PATCHUID ORDER BY MACHINE_NAME
-
I don't think using the DEPLOY_STATUS_DT is a good way to go since I believe that table will contain entries showing dates when patches were deployed going back a long time. I would instead look at the PATCHLINK_SCHEDULE_MACHINE_STATUS.LAST_RUN. This would show you the last time a patching schedule was run on a machine. - chucksteel 11 years ago
Answers (3)
This is for devices that have patched at some point, but not within the last 5 days:
SELECT *
FROM (
SELECT
MACHINE.ID,
MAX(PSMS.LAST_RUN) AS LAST_RUN
FROM MACHINE
INNER JOIN PATCH_SCHEDULE_MACHINE_STATUS AS PSMS
ON MACHINE.ID = PSMS.MACHINE_ID
INNER JOIN PATCH_SCHEDULE AS PS
ON PSMS.PATCH_SCHEDULE_ID = PS.ID
WHERE PS.PATCH_ACTION = 3
AND OS_NAME NOT LIKE '%SERVER%'
AND OS_NAME LIKE '%WINDOWS%'
GROUP BY MACHINE.ID) AS PSTEMP
WHERE LAST_RUN <= NOW() - INTERVAL 5 DAY
This is for devices that have run a detection schedule at some point, but not within the last 5 days:
SELECT *
FROM (
SELECT
MACHINE.ID,
MAX(PSMS.LAST_RUN) AS LAST_RUN
FROM MACHINE
INNER JOIN PATCH_SCHEDULE_MACHINE_STATUS AS PSMS
ON MACHINE.ID = PSMS.MACHINE_ID
INNER JOIN PATCH_SCHEDULE AS PS
ON PSMS.PATCH_SCHEDULE_ID = PS.ID
WHERE PS.PATCH_ACTION <> 3
AND OS_NAME NOT LIKE '%SERVER%'
AND OS_NAME LIKE '%WINDOWS%'
GROUP BY MACHINE.ID) AS PSTEMP
WHERE LAST_RUN <= NOW() - INTERVAL 5 DAY
This is for devices that have never run a patch schedule:
SELECT
MACHINE.ID,
MACHINE.NAME,
PS.ID AS PSID,
PS.PATCH_ACTION AS PSPA,
PSMS.MACHINE_ID AS PSMSMID,
PS.LAST_RUN
FROM MACHINE
LEFT JOIN PATCH_SCHEDULE_MACHINE_STATUS AS PSMS
ON MACHINE.ID = PSMS.MACHINE_ID
LEFT JOIN PATCH_SCHEDULE AS PS
ON PSMS.PATCH_SCHEDULE_ID = PS.ID
WHERE PSMS.MACHINE_ID IS NULL
AND OS_NAME NOT LIKE '%SERVER%'
AND OS_NAME LIKE '%WINDOWS%'
GROUP BY MACHINE.ID
I have done this using a custom inventory instead.
I have two files, one that copies the kpatch.log to it's own directory and the other which reads the modify date on the kpatch log file. If the modify date is older than 5 days it reports to the CI that patching is not current, if it is within the last 5 days then it reports the patching is current. I then created a smart label that looks at the CI inventory results to add/remove computers from the smart label.
First batch file to run is this:
Called it mkpatch.bat
mkdir c:\programdata\dell\kace\patch
copy c:\programdata\dell\kace\kpatch.log c:\programdata\dell\kace\patch\
forfiles /p "c:\programdata\dell\kace\patch" /m *.log /d -5 /c "cmd /c del @file"
Next is called kpatch.vbs and looks like this:
Option Explicit
Dim fso, path, file, recentDate, recentFile, folderPath
Folderpath = "c:\programdata\dell\kace\patch"
Set fso = CreateObject("Scripting.FileSystemObject")
Set recentFile = nothing
For Each file in fso.GetFolder(folderPath).Files
If (recentFile is Nothing) Then
Set recentFile = file
ElseIf (file.DateLastModified > recentFile.DateLastModified) Then
Set recentFile = file
End If
Next
If recentFile is Nothing Then
WScript.Echo "Not Patched"
Else
WScript.Echo "Patching is Current"
End If
Comments:
-
I then zip the two files and sync them to all machines, the file sync will uncompress the files to the directory you specify.
a scheduled script runs the mkpatch.bat daily and the vbs is run using the software inventory.
with the following command:
ShellCommandTextReturn(cmd.exe /c cscript //nologo "c:\admin\scripts\kpatch.vbs") - rsm11 11 years ago -
This is an interesting solution but it can't account for machines that run patching but produce errors and therefore aren't actually patched. - chucksteel 11 years ago
-
That's a good point. any suggestions? - rsm11 11 years ago
Just as an FYI,
This label doesnt work because KACE looks for a certain set of criteria when scanning a machine label and its missing here. A good rule of thumb is create a simpl eon with KACE and then look at the fields it has created. Now structure your query to have the filters you want but still return the criteria KACE needs to aply the filter and return results.
In this case it is
MACHINE.NAME AS SYSTEM_NAME,
SYSTEM_DESCRIPTION,
MACHINE.IP,
MACHINE.MAC,
MACHINE.ID as TOPIC_ID