Splunk Search

Can I use strftime to compare relative times?

Path Finder

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

0 Karma

Revered Legend

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)
0 Karma

SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!