PCs not scanned for more than 3 days

[numeroinconnu12]

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

[PickleRick]

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().

[numeroinconnu12 · Accepted solution]

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