Splunk Search

PCs not scanned for more than 3 days

numeroinconnu12
Path Finder

Hello,

This is my request:

 

 

index=antivirus
    | stats values(SAVVersion) as SAVVersion, values(EngineVersion) as EngineVersion ,values(VirusDataVersion) as VirusDataVersion,  max(LastMessageTime) as LastMessageTime, max(LastScanDateTime) as LastScanDateTime by Name
    | sort LastScanDateTime
    | eval diff=round((Now() - LastScanDateTime)/60/60/24)
    | eval "active the last seven days ?"=if(round((Now() - LastMessageTime)/60/60/24)>7,"NO","YES")
    | where (diff > 3) OR isnull(diff)
    | fillnull value="-"
    | sort - "active the last seven days ?" - diff

 

 

I would like to have only the PCs(Name) not scanned (LastScanDateTime) for more than three days, but my request does not work, it returns all the PCs. Can you please help me?
sorry for my english

 

Labels (2)
0 Karma
1 Solution

numeroinconnu12
Path Finder

Thank you @PickleRick 

my correction, it's work

index=antivirus
    | eval lmt=strptime(LastMessageTime,"%m/%d/%Y %H:%M:%S")
    | eval lst=strptime(LastScanDateTime,"%m/%d/%Y %H:%M:%S")
    | stats values(SAVVersion) as SAVVersion, values(EngineVersion) as EngineVersion ,values(VirusDataVersion) as VirusDataVersion,  max(lmt) as LastMessageTime, max(lst) as LastScanDateTime by Name
    | sort LastScanDateTime
    | eval diff=round((Now() - LastScanDateTime)/60/60/24)
    | eval "active in the last 7 days ?"=if(round((Now() - LastMessageTime)/60/60/24)>7,"NO","YES")
    | eval LastMessageTime=strftime(LastMessageTime,"%d/%m/%Y %H:%M:%S")
    | eval LastScanDateTime=strftime(LastScanDateTime,"%d/%m/%Y %H:%M:%S")
    | where (diff > 3) OR isnull(diff)
    | fillnull value="-"
    | sort - "active in the last 7 days?" - diff

View solution in original post

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Fistly - no point in sorting the data if you want to filter the entries anyway. It'd be more efficient to filter first, then sort - possibly less data to sort.

But to the point - is your LastScanDateTime properly set? Are you sure you don't need to strptime() it first, before doing a comparison to now()?

Oh, and round()-ing the difference will show you results which are more than 7.5 days behind, not 7. You don't need to do round() to just compare to a value and even if you wanted it to - for example - calculate a new field with number of days since last check, you'd rather use floor(), not round().

0 Karma

numeroinconnu12
Path Finder

Thank you @PickleRick 

my correction, it's work

index=antivirus
    | eval lmt=strptime(LastMessageTime,"%m/%d/%Y %H:%M:%S")
    | eval lst=strptime(LastScanDateTime,"%m/%d/%Y %H:%M:%S")
    | stats values(SAVVersion) as SAVVersion, values(EngineVersion) as EngineVersion ,values(VirusDataVersion) as VirusDataVersion,  max(lmt) as LastMessageTime, max(lst) as LastScanDateTime by Name
    | sort LastScanDateTime
    | eval diff=round((Now() - LastScanDateTime)/60/60/24)
    | eval "active in the last 7 days ?"=if(round((Now() - LastMessageTime)/60/60/24)>7,"NO","YES")
    | eval LastMessageTime=strftime(LastMessageTime,"%d/%m/%Y %H:%M:%S")
    | eval LastScanDateTime=strftime(LastScanDateTime,"%d/%m/%Y %H:%M:%S")
    | where (diff > 3) OR isnull(diff)
    | fillnull value="-"
    | sort - "active in the last 7 days?" - diff
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...