Splunk Alert for host that has not had an specific event happen in 30 days?

[wmbryan]

I'm trying to alert/query  any Host that has not had an update in more than say 30 days.

Here is the search in Splunk:

"index=endpoint_mcs_server sourcetype="Windows:UpdateList""

Which gives me this data:

"PSComputerName="host" description="Update" hotfixid="KB5022503" installedby="NT AUTHORITY\SYSTEM" Installedon="02/23/2023""

So it gives me a date "InstalledOn" so I just need to edit the search to only show systems that have not "InstalledOn" and or had an update in the last 30 days.

Thanks for the help

[gcusello]

Hi @wmbryan,

the solution is the one described by @isoutamo  that's similat to hundreds of answers i gave in these years.

The main problem is the monitoring perimeter, in other words, you have to create a list of hosts and for each host you should identify the hotfixes to check.

You could create a search to list in a dashboard panel the latest hotfixes installed and the installation dates, but I don't see a solution to create an automatic alert.

So you could try something like this:

index=endpoint_mcs_server sourcetype="Windows:UpdateList" description="Update" hotfixid="KB5022503"
| eval PSComputerName=lower(PSComputerName)
| stats 
   last(installedby) AS installedby ="NT AUTHORITY\SYSTEM" 
   last(Installedon) AS Installedon
   count
   BY PSComputerName
| append [ 
   | inputlookup perimeter.csv 
   | eval PSComputerName=lower(PSComputerName), count=0
   | fields PSComputerName count
   ]
| stats 
   last(installedby) AS installedby
   last(Installedon) AS Installedon
   sum(count) AS total
   BY PSComputerName
| eval 
   status=if(total=0,"Missed", if(now()-strptime(Installedon,"%m/%d/%Y")>2592000,"Late","Present"))
| table PSComputerName installedby Installedon status

you could also list all hotfixes and the update date or put the hotfixes in a dropdown list to choose it.

Ciao.

Giuseppe

[wmbryan]

Thanks for the reply!

This search gives me the below results. (I'm only showing a few of the results)

index=endpoint_mcs_server sourcetype="Windows:UpdateList" | stats latest(Installedon) as LastPatchDate by host

host LastPatchDate

Computer1	09/06/2022
Computer2	09/06/2022
Computer3	09/06/2022
Computer4	09/14/2022
Computer5	09/23/2022
Computer6	10/11/2022

Would it be possible to just add to that search with something like:

| Where LastPatchDate > 30d

Or do I have to use | Eval ?

Seems I should be able to add to the search above to only show results where LastPatchDate is older than X amount of days. Thanks again for the assistance.

[wmbryan]

Thanks to all that responded.

Below is what I ended up with. It still has some bad apples but that is not because of the search but rather something else with the system or data that is not consistent with all of the other systems. I mostly notice issues with systems that are Server 2012 or 2016. Out of roughly 300 systems they are only a few bad apples.

index=index type="Windows:UpdateList" | eval Installedon=strptime(Installedon,"%m/%d/%Y") | stats latest(Installedon) as LastUpdate by host | where LastUpdate<=relative_time(now(), "-30d") | eval LastUpdate=strftime(LastUpdate,"%m/%d/%Y") | sort LastUpdate

[Tom_Lundie]

Here is a run-anywhere example to try:

| makeresults
| fields - _time
| eval _raw = "PSComputerName=\"host\" description=\"Update\" hotfixid=\"KB5022503\" installedby=\"NT AUTHORITY\SYSTEM\" Installedon=\"02/23/2023\""
| extract
| where strptime(Installedon, "%m/%d/%Y") > relative_time(now(), "-30days")

If you already have field extractions set-up, it's only that last line you'll need to focus on.

[wmbryan]

Thanks for the reply Tom.

I wasn't able to get this to work the way I need it to.

[isoutamo]

Hi

I think that this old answer is your solution https://community.splunk.com/t5/Splunk-Search/Alerting-based-off-of-no-events-by-server/td-p/568786 . Just update it with your query.

It's main point is that you need lookup for normal situation where to refer to see if there is someone missing.

r. Ismo