Filtering on multiple times

JoshuaJohn — Tue, 27 Mar 2018 18:57:14 GMT

I have a search with two indexes.
The first index needs to be run on all time, but I have a field within that index that I would like to filter on (it contains a date/time field as a string), I have another field within that index that I would also like to filter on (it also contains a date/time field as a string).

index=BASE earliest=0
 | eval LPR = strptime(LastPolicyRequest, "%m/%d/%Y %I:%M:%S") 
 | where LPR >= "-30d@d" AND LPR <= "now"
| eval now_epoch = now() 
| eval valid_to_epoch = strptime(ValidTo, "%m/%d/%Y %I:%M:%S %p") 
| where valid_to_epoch < now_epoch
| table "WiFiMAC","LastPolicyRequest","ValidFrom","ValidTo" 
| rename WiFiMAC as hdr.macaddress

Today is 3/27/18, yet getting results for 2/24/18

The query that I am joining needs to be set for 2 days

index=BASE earliest =-48h
    | eval indextime=strftime(_indextime,"%Y-%m-%d %H:%M:%S") 
    | eval Indextime =strptime(indextime,"%Y-%m-%d %H:%M:%S.%N") 
    | eval Time =strptime(_time,"%s") 
    | eval Minutes_Diff = round((Indextime - Time)/60,2) 
    | stats avg(Minutes_Diff) as Avg_Minutes_Diff stdev(Minutes_Diff) as StDev_Minutes_Diff min(Minutes_Diff) as Min_Minutes_Diff max(Minutes_Diff) as Max_Minutes_Diff count as count by hdr.macaddress hdr.locId hdr.deviceModel hdr.firmwareVersion 
    | eval Avg_Minutes_Diff = round(Avg_Minutes_Diff,2) 
    | rename count as "Sample Size" 
    | table "hdr.macaddress", "Avg_Minutes_Diff", "StDev_Minutes_Diff", "Min_Minutes_Diff", "Max_Minutes_Diff", "Sample Size"

Here is the joined search:

index=BASE earliest=0
 | eval LPR = strptime(LastPolicyRequest, "%m/%d/%Y %I:%M:%S %p") 
 | where LPR >= "-30d@d" AND LPR <= "now"
| eval now_epoch = now() 
| eval valid_to_epoch = strptime(ValidTo, "%m/%d/%Y %I:%M:%S %p") 
| eval twenty_days_epoch=relative_time(now(), "-20d@d") 
| where valid_to_epoch < now_epoch
| table "WiFiMAC","LastPolicyRequest","ValidFrom","ValidTo" 
| rename WiFiMAC as hdr.macaddress 
| join type=left hdr.macaddress 
    [ search index=BASE earliest =-48h
    | eval indextime=strftime(_indextime,"%Y-%m-%d %H:%M:%S") 
    | eval Indextime =strptime(indextime,"%Y-%m-%d %H:%M:%S.%N") 
    | eval Time =strptime(_time,"%s") 
    | eval Minutes_Diff = round((Indextime - Time)/60,2) 
    | stats avg(Minutes_Diff) as Avg_Minutes_Diff stdev(Minutes_Diff) as StDev_Minutes_Diff min(Minutes_Diff) as Min_Minutes_Diff max(Minutes_Diff) as Max_Minutes_Diff count as count by hdr.macaddress hdr.locId hdr.deviceModel hdr.firmwareVersion 
    | eval Avg_Minutes_Diff = round(Avg_Minutes_Diff,2) 
    | rename count as "Sample Size" 
    | table "hdr.macaddress", "Avg_Minutes_Diff", "StDev_Minutes_Diff", "Min_Minutes_Diff", "Max_Minutes_Diff", "Sample Size" ] 
| table "hdr.macaddress","Avg_Minutes_Diff", "StDev_Minutes_Diff", "Min_Minutes_Diff", "Max_Minutes_Diff","ValidFrom","ValidTo","LastPolicyRequest", "Sample Size"
| dedup hdr.macaddress

Any Ideas as to what I am doing wrong?

Re: Filtering on multiple times

maciep — Tue, 27 Mar 2018 22:47:35 GMT

can you filter on time like that in the where command? If you can, very cool. If not, you might want to use relative_time() instead.

... | where LPR >= relative_time(now(),"-30d@d") AND LPR <= now() | ...

topic Re: Filtering on multiple times in Getting Data In

Filtering on multiple times

Re: Filtering on multiple times