/build/static/layout/Breadcrumb_cap_w.png

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


1 Comment   [ + ] Show comment
  • 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 10 years ago

Answers (3)

Posted by: BNewland 1 year ago
Yellow Belt
1

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

Posted by: rsm11 10 years ago
Blue Belt
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

 

 


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 10 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 10 years ago
    • That's a good point. any suggestions? - rsm11 10 years ago
Posted by: jdornan 10 years ago
Red Belt
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 

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ