Splunk Search
Highlighted

Compare responseTime field toady to last week without using append

Path Finder

Hello, I have a problem comparing responseTime field last minute with last week (monday - sunday).
Below query give the results what i am seeking for, but append command limits to 50000 events, So avg(responseTime) is not accurate for the last week.

index=abc sourcetype=123
| eval responseTime1=responseTime/1000
| append [search index=abc earliest=-1w@w1 latest=@w1 sourcetype=123 | eval responseTime7=responseTime/1000 ]

| stats avg(responseTime1) AS one avg(responseTime7) AS two by application

I have tried many examples which i found in splunk answers but none of them are suitable for my requirement.

Can someone help me with this one?

Thank you very much in advance!...

0 Karma
Highlighted

Re: Compare responseTime field toady to last week without using append

Legend

Try this

index=abc sourcetype=123 earliest=-1w@w1 
| eval when=if(_time>relative_time(now(), "-1m@m", "Current", "Last Week")
| eval responseTime=responseTime/1000 
| chart avg(responseTime) AS one by application when

The relative_time function checks to see if time the event occured is greater than -1min from now, it considers it as current. You can adjust the -1m to whatever you need it to be.

View solution in original post

0 Karma
Highlighted

Re: Compare responseTime field toady to last week without using append

Path Finder

Thankyou so much sundareshr, your query did helped me out appreciate your quick response. i need to have this query in ITSI in ITSI i need to specify threshold field "Current" and "Last week" as kpi to monitor real time.
is there a way i could divide the field "when" into two separate fields "Current" and Last week".

0 Karma
Highlighted

Re: Compare responseTime field toady to last week without using append

Legend

You mean something like this?

eval Current=if(_time>relative_time(now(), "-1m@m"), 1, 0)  | eval "Last Week"=if(_time<relative_time(now(), "-1m@m"), 1, 0)
0 Karma
Highlighted

Re: Compare responseTime field toady to last week without using append

Path Finder

No, when had (| chart avg(responseTime) AS one by application when) we get Current and Lastweek fields out of it. even if we separate "when" into current and lastweek it still gives o and 1 for both.
Now "When" is a field in interesting fields on our left. Instead of that i need Current and Lastweek as a fields
i am expecting as below
|chart avg(responseTime) AS one by application Current LastWeek

application Current LastWeek
1 values values
2 values values
3 values values

0 Karma
Highlighted

Re: Compare responseTime field toady to last week without using append

Legend

Sorry, I am missing something. Don't you get the desired output when you do (| chart avg(responseTime) AS one by application when

0 Karma
Highlighted

Re: Compare responseTime field toady to last week without using append

Path Finder

When we do (| chart avg(responseTime) AS one by application when )
"when" populates two sub fields "Current" and "Lastweek"
instead of having sub fields in "when" is it possible to have "Current" and "Lastweek" as a separate fields like "when".

0 Karma
Highlighted

Re: Compare responseTime field toady to last week without using append

Path Finder

even like the above example if we divide the field into two separate fields again we have two sub fields in Current and LastWeek. this doesnt work in ITSI. because we cant use any aggregations in ITSI except eventstats. Until unless we have Current and LastWeek as an separate fields intresting fields on our left hand side without having sub fields "0" and "1" i wont be able to use this query

0 Karma
Highlighted

Re: Compare responseTime field toady to last week without using append

Legend

Like this?

eval Current=if(_time>relative_time(now(), "-1m@m"), 1, null())  | eval "Last Week"=if(_time<relative_time(now(), "-1m@m"), 1, null())
0 Karma
Highlighted

Re: Compare responseTime field toady to last week without using append

Legend

Or like this

index=abc sourcetype=123 earliest=-1w@w1 
 | eval when=if(_time>relative_time(now(), "-1m@m", "Current", "Last Week")
 | eval responseTime=responseTime/1000 
 | stats avg(eval(if(when="Current", responseTime, null()) as Current avg(eval(if(when="Last Week", responseTime, null()) as "Last Week"
0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.