Can I use strftime to compare relative times?

[katzr]

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

[somesoni2]

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)

[richgalloway]

Dates must be in epoch form to be compared. Use strptime to convert human-readable dates to epoch form as necessary.