Splunk Search

How to use lookup file to set earliest and latest time

sangs8788
Communicator

I have a lookup file with dates. how do i use it to set earliest and latest inorder to search for events,

For example,

Lookupfile

No Date
1 10/9/2016
2 20/11/2016
3 21/1/2017

I want to find the specific set of events which falls between 1 and 2. How to write a query to join index with lookup file to get the dates as earliest and latest.

Thanks

Tags (1)
0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

Assuming the date field is called csvDate and the lookup file is called mylookup.csv, you can do this...

 *** some part of your base search ***

 [| inputlookup mylookup.csv 
  | streamstats count 
  | eval earliest=if(count=1,strptime(csvDate,"%d/%m/%Y"),null()) 
  | eval latest=if(count=2,strptime(csvDate,"%d/%m/%Y"),null())
  | table earliest latest 
  | stats values(*) as * 
  | format "(" "" "" "" "" ")" 
  | rex field=search mode=sed "s/\"//g"]

 *** Any other part of your base search ***

 | your remaining logic here

Given the above, everything in braces will come out like this ....

 *** some part of your base search ***

(  earliest=firstepochtime latest=secondepochtime )

 *** Any other part of your base search ***

If that fails due to epoch time format not being okay for earliest and latest, then add a strftime to turn the result of strptime to the format needed by earliest and latest.

View solution in original post

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Assuming the date field is called csvDate and the lookup file is called mylookup.csv, you can do this...

 *** some part of your base search ***

 [| inputlookup mylookup.csv 
  | streamstats count 
  | eval earliest=if(count=1,strptime(csvDate,"%d/%m/%Y"),null()) 
  | eval latest=if(count=2,strptime(csvDate,"%d/%m/%Y"),null())
  | table earliest latest 
  | stats values(*) as * 
  | format "(" "" "" "" "" ")" 
  | rex field=search mode=sed "s/\"//g"]

 *** Any other part of your base search ***

 | your remaining logic here

Given the above, everything in braces will come out like this ....

 *** some part of your base search ***

(  earliest=firstepochtime latest=secondepochtime )

 *** Any other part of your base search ***

If that fails due to epoch time format not being okay for earliest and latest, then add a strftime to turn the result of strptime to the format needed by earliest and latest.

0 Karma

sangs8788
Communicator

Ok I think i have to be little more clearer. This is what i am trying to do. I have a ReleaseCalender.csv which will have Release number and Release dates. Say i want to calculate events from another index based on the dates specified in the lookup file ? how do i map the timestamp of events with the Lookup file Date?

|inputlookup ReleaseCalender.csv |sort Release ASC| reverse |streamstats current=f last(Production) as latest|rename Production as earliest|table earliest,latest|where Release="1"

The above query would give me the Release No, StartDate and EndDate. Now i would like to join this with another query and list all the events which has occured during this period of time. something like below, (PS the below query wouldnt work since i didnt know what to put in Join condition.

index=app exception="sql" host="prod*" sqlserver |convert timeformat="%d/%m/%Y" ctime(_time) AS TIMESTAMP |eval SERVICE="MSSQL"|eval Release="1"| stats count by SERVICE
|join Release [|inputlookup ReleaseCalender.csv |sort Release ASC| reverse |streamstats current=f last(Production) as latest|rename Production as earliest|table earliest,latest|where Release="1"]

0 Karma

niketn
Legend

Try the following Dashboard code, which uses multiselect to reach Dates from startenddate.csv(you can use your own lookup filename instead). It sets the lower value of Date as Start Time and higher value as End Time to be used in the final search query:

<form>
  <label>Start End Date Lookup</label>
  <!--############################################################################
  Query to set Start Time and End Time based on Multi-Select Input
  true() condition i.e. when time1 and time2 are equal should ideally not be hit.
  (Since Multiselect input will not allow same value to be selected twice.
  Basic Validation to test time1 > time2 or time1 < time2 to ensure start time
  and end time are set correctly.
  Even if more than two values are selected only first two values will be used
  through split() and mvindex() functions

  ##############################################################################-->
  <search>
    <query>
    | makeresults
    | eval selectedDates="$tokStartEndDate$"
    | eval selectedDates=split(selectedDates,",")
    | eval time1=mvindex(selectedDates,0)
    | eval time2=mvindex(selectedDates,1)
    | eval starttime=case((time1<time2),time1,(time1>time2),time2,true(),time1)
    | eval endtime=case((time1>time2),time1,(time1<time2),time2,true(),"now()")
    | table starttime endtime
    </query>
    <done>
      <set token="tokStartTime">$result.starttime$</set>
      <set token="tokEndTime">$result.endtime$</set>
    </done>
  </search>
  <!--###############################################################################
  Multiselect input to set multivalue token tokStartEndDate which is comma separated.
  Picks up Date from lookup file startenddate.csv. Builds epoch time using strptime()

  ################################################################################-->
  <fieldset submitButton="true">
    <input type="multiselect" token="tokStartEndDate">
      <label>Select Start and End Date</label>
      <delimiter>,</delimiter>
      <fieldForLabel>Date</fieldForLabel>
      <fieldForValue>epochDate</fieldForValue>
      <search>
        <query>|inputlookup startenddate.csv
| eval epochDate=strptime(Date,"%d/%m/%Y")
| sort epochDate
| table Date epochDate</query>
      </search>
    </input>
  </fieldset>

  <row>
    <panel>
      <table>
        <search>
          <query>
            <YourSearchGoesHere>
          </query>
          <earliest>$tokStartTime$</earliest>
          <latest>$tokEndTime$</latest>
        </search>
      </table>
    </panel>
  </row>
</form>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...