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

1 Comment   [ + ] Show Comment

Comments

  • 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.
Please log in to comment

Answers

0

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

 

 

Answered 09/25/2013 by: rsm11
Blue Belt

  • 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")
  • This is an interesting solution but it can't account for machines that run patching but produce errors and therefore aren't actually patched.
    • That's a good point. any suggestions?
Please log in to comment
0

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 
Answered 09/28/2013 by: jdornan
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share