topic Re: How to pass earliest and latest in search time using inputlookup in Splunk Search

How to pass earliest and latest in search time using inputlookup

james_n — Thu, 09 Apr 2020 12:52:57 GMT

Hi Experts, I have a one month data inputlookup file i.e, sample.csv which contains two fields test and _time. I want to compare weekly data.

sample.csv

test _time
101 202/04/02 14:02:18
102 202/04/01 20:21:50
101 202/04/05 02:09:12
101 202/03/31 08:11:29

Expected output:

test count week
101 2 thisweek
102 1 lastweek
103 1 prior week
101 1 prior week

like this.. please help on this and thanks in advance.

Re: How to pass earliest and latest in search time using inputlookup

manjunathmeti — Thu, 09 Apr 2020 17:14:57 GMT

Check if this query works for you. Note that below query sets this_week_start to 2020/04/06 00:00:00 (starting this Monday) and last_week_start to 2020/03/30 00:00:00 (starting previous week Monday) when I posted this answer.

| inputlookup sample.csv 
| eval _time=strptime(_time, "%Y/%m/%d %H:%M:%S"), this_week_start=relative_time(now(), "@w1"), last_week_start=relative_time(now(), "-w@w1"), week = case(_time > this_week_start, "thisweek", _time > last_week_start, "lastweek", 1==1, "prior week") 
| stats count by test, week

Re: How to pass earliest and latest in search time using inputlookup

james_n — Fri, 10 Apr 2020 11:04:29 GMT

@manjunathmeti , Thanks for the quick response, but this not working. all results showing as prior week only that to total count for 30days.

Re: How to pass earliest and latest in search time using inputlookup

to4kawa — Fri, 10 Apr 2020 11:24:12 GMT

sample:

| makeresults
| eval _raw="test time
101 2020/04/02 14:02:18
102 2020/04/01 20:21:50
101 2020/04/05 02:09:12
101 2020/03/31 08:11:29"
| multikv forceheader=1
| eval _time=strptime(time,"%Y/%m/%d %T")
| eval today = now()
| eval daysago=mvindex(split(tostring(today - _time,"duration"),"+"),0)
| eval week=case(daysago<=7,"thisweek",daysago > 7 AND daysago <= 14 ,"lastweek" , true(), "prior week")
| stats count by test week

Recommend:

| inputlookup sample.csv 
| eval _time=strptime(time,"%Y/%m/%d %T")
| eval today = now()
| eval daysago=mvindex(split(tostring(today - _time,"duration"),"+"),0)
| eval week=case(daysago<=7,"thisweek",daysago > 7 AND daysago <= 14 ,"lastweek" , true(), "prior week")
| stats count by test week

How about this?