Compare results from 2 searches

[RahulMisra1]

Hi, 

I am running a search to get count of IP';s from yesterday & last month.

index=<> source="/****" IP!="10.*" [| inputlookup ip_tracking.csv | rename MIDS AS MID | format ] earliest=-30d@d latest=now| eval ReportKey="Last30Day"|append [search index=<> source=""/****"" IP!="10.*" [| inputlookup ip_tracking.csv | rename MIDS AS MID | format ] earliest=-1d@d latest=@d | eval ReportKey="yesterday"]| eval Day=if(_time<=relative_time(now(),"-30d@d"),"yesterday","Last30Day")
| stats count(eval(Day="yesterday")) AS yesterday count(eval(Day="Last30Day")) AS Last30Day BY IP

This search is giving me all results in Month but not in yesterday. Can you help me in correcting the query

[bowesmana · Accepted solution]

@RahulMisra1 It looks like you have double accounting - you are counting yesterday twice because your first search is -30 days to NOW, which includes yesterday and then you append yesterday's data, so you will effectively count yesterday twice.

You don't actually need the append as you already have the data from the first search to calculate yesterday.

One question though - is your last 30 days supposed to include yesterday - anyway this search should work

index=<> source="/****" IP!="10.*" earliest=-30d@d latest=now 
[
  | inputlookup ip_tracking.csv 
  | rename MIDS AS MID 
  | format 
] 
``` If the data time is -1d to end of day, then the data is yesterday ```
| eval Day=if(_time>=relative_time(now(),"-1d@d") AND _time<=relative_time(now(),"@d"),"yesterday","Last30Day") 
``` Now count yesterday, but then count last 30 days as ALL items, which 
    includes yesterday ```
| stats count(eval(Day="yesterday")) AS yesterday count AS Last30Day BY IP

The final count as last30Day will just count all items in the search including yesterday - if you want that to exclude yesterday BUT also include today up to now, then use your original eval statement in the count.

[richgalloway]

Double-check the calculation for the Day field.  Events less than a day old will have *greater* timestamps than older events.

eval Day=if(_time>=relative_time(now(),"-1d@d"),"yesterday","Last30Day")