Checking Host's status as offline or online by comparing data from today vs yesterday

[mbasharat]

Hi,

I have vulnerability scanner that scans all device on our network every day. The agent of vulnerability scanner is on all endpoints being scanned. When an endpoint is offline or being rebooted, it misses the scan and does not appear in scan so does not appear in Splunk.

What I need is, I need a Splunk search that tells me the status of endpoint being online/offline by using above data. For example, is it possible to compare yesterday's data when endpoint appeared on scan vs today when an endpoint did not appear in scan and show results as below?

[jpolvino]

While not a direct solution, you can always look into the use of sentinel values. I found this Splunk .conf 2015 presentation helpful, with focus on slide 25.

The challenge you describe is identifying what is missing, which is hard to do if you don't know what should exist in the first place. It's like asking a classroom: "OK, who isn't here today?"

[DalJeanis · Accepted solution]

If you can assume that each host will have been checked at least once in the last 48 hours, and if they all get the exact same time when they are scanned, then you could do something like this:

your search that gets _time and Server for all scans for the last 48 hours
| fields _time Server
| stats min(_time) as minTime max(_time) as maxTime by Server
| eventstats max(maxTime) as latestTime
| eval Status=if(maxTime=latestTime,"Online","Offline") 
| addinfo 
| eval AsOfTime =strftime(info_max_time,"%Y-%m-%d %H:%M:%S.%3Q")
| eval minTime  =strftime(minTime,"%Y-%m-%d %H:%M:%S.%3Q")
| eval _time = maxTime
| eval maxTime  =strftime(maxTime,"%Y-%m-%d %H:%M:%S.%3Q")
| eval latestTime  =strftime(latestTime,"%Y-%m-%d %H:%M:%S.%3Q")
| table Server _time Status minTime maxTime latestTime

If the servers do not all get identical scan times, then do this:

your search that gets _time and Server for all scans for the last 48 hours
| fields _time Server
| stats min(_time) as minTime max(_time) as maxTime by Server
| eventstats max(maxTime) as latestTime
| eval splitTime=latestTime - 24*3600
| eval Status=if(maxTime>splitTime,"Online","Offline") 
| addinfo 
| eval AsOfTime =strftime(info_max_time,"%Y-%m-%d %H:%M:%S.%3Q")
| eval minTime  =strftime(minTime,"%Y-%m-%d %H:%M:%S.%3Q")
| eval _time = maxTime
| eval maxTime  =strftime(maxTime,"%Y-%m-%d %H:%M:%S.%3Q")
| eval latestTime  =strftime(latestTime,"%Y-%m-%d %H:%M:%S.%3Q")
| table Server _time Status minTime maxTime latestTime 

In the above outputs, LatestTime is the last scan that was performed for any Server, _time and maxTime are the latest scan time for that Server, and minTime is the earliest scan time for that server.

[mbasharat]

Thanks @ Daljeanis!!! I used the second option as all scan times are different. Tiny typo adjustment from mintime to minTime @ last line 🙂

[DalJeanis]

Great. Glad we could help. Typos fixed.

[to4kawa]

why do you compare the results?
today' s status is missing, status is offline.
Is this enough?