Hello,
I received help in building a search of mine, and I cannot figure out the syntax of comparing the time. I need help with this part of the search below (test the date for if this event is in baseline/average). My average is looking at the past 3 months and my baseline is looking at between 6/01/2017 and 12/31/2017. I tried using strftime and couldn't get it to work.
| join overwrite=false contact_type
[search index=example earliest=-6mon@mon latest=now (assignment_group="*")
| fields contact_type ... whatever else you absolutely need...
| eval _time = relative _time(_time,"@mon")
| eval BaselineFlag = case(...test the date for if this event is in baseline...., 1)
| eval AverageFlag = case(...test the date for if this event is in average...., 1)
| rename COMMENT as "The above commands are streaming and distributable, so should be above the dedup unless you have LOTS of dups."
| rename COMMENT as "By using dc instead of count, this stats eliminates the need for dedup."
| stats dc(eval(case(BaselineFlag=1,number))) as BaselineTickets
dc(eval(case(AverageFlag=1,number))) as AverageTickets
by contact_type _time
| stats avg(BaselineTickets) as Baseline avg(AverageTickets) as Average by contact_type
| eval Baseline = round(Baseline,0)
| eval Average = round(Average,0)
Essentially my goal of the search is to look at the tickets by contact_type for the current month and then compare those against a baseline and average. So this part of the search included is the comparison of the baseline and average
Try this for your search (check starting/ending of the search per your requirement)
index=example earliest=-6mon@mon latest=now (assignment_group="*")
| fields contact_type ... whatever else you absolutely need...
| eval _time = relative_time(_time,"@mon")
| eval BaselineNumber=if(_time>=strptime("06/01/2017","%m/%d/%Y"),number,null())
| rename COMMENT as "If it's not a fixed date, use relative_time(now(),"@y+5mon") instead of strptime("06/01/2017","%m/%d/%Y")"
| eval AverageNumber = if(_time>=relative_time(now(),"-3mon@mon") AND _time<relative_time(now(),"@mon"),number,null())
| rename COMMENT as "The above commands are streaming and distributable, so should be above the dedup unless you have LOTS of dups."
| rename COMMENT as "By using the value of field number directly you can avoid expesive eval-case in stats"
| stats dc(BaselineNumber) as BaselineTickets
dc(AverageNumber) as AverageTickets
by contact_type _time
| stats avg(BaselineTickets) as Baseline avg(AverageTickets) as Average by contact_type
| eval Baseline = round(Baseline,0)
| eval Average = round(Average,0)
Dates must be in epoch form to be compared. Use strptime
to convert human-readable dates to epoch form as necessary.