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 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
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:
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
If recentFile is Nothing Then
WScript.Echo "Not Patched"
WScript.Echo "Patching is Current"
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,
MACHINE.ID as TOPIC_ID