topic Re: Compare responseTime field toady to last week without using append in Splunk Search

Compare responseTime field toady to last week without using append

appache — Sat, 27 Aug 2016 15:46:53 GMT

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!...

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

sundareshr — Sat, 27 Aug 2016 16:29:53 GMT

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.

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

appache — Sat, 27 Aug 2016 16:50:01 GMT

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".

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

sundareshr — Sat, 27 Aug 2016 17:07:38 GMT

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)

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

appache — Sat, 27 Aug 2016 17:53:57 GMT

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

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

sundareshr — Sat, 27 Aug 2016 18:11:44 GMT

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

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

appache — Sat, 27 Aug 2016 18:23:28 GMT

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".

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

appache — Sat, 27 Aug 2016 18:35:46 GMT

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

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

sundareshr — Sat, 27 Aug 2016 19:00:38 GMT

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())

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

sundareshr — Sat, 27 Aug 2016 19:02:57 GMT

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"

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

appache — Sat, 27 Aug 2016 21:32:52 GMT

Thankyou sundareshr, it did work, how do i specify latest time in the query to limit to only last week (august 14 -aug 21), now its taking till today. if iam adding latest=@w1 i am not able to get "current" field

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

sundareshr — Sun, 28 Aug 2016 13:24:02 GMT

If its only last week, what will you be comparing against?

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

appache — Tue, 29 Sep 2020 10:46:29 GMT

I apologize for not being clear appreciate your help, i need to compare avg(responsetime) of only last week (ex: monday - sunday), compare with avg(responsetime) of last min by application and calculate the variance(difference) of both avg_responsetime fields in percentage.
last week field should change be static through out the week and it should change only on every monday giving last monday to sunday avg_responsetime. and the current fields should be dynamic every min as well as variance%.

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

sundareshr — Sun, 28 Aug 2016 17:58:01 GMT

See of this gives you some ideas

index=abc sourcetype=123 earliest=-1w@w1 
  | eval when=case(_time>relative_time(now(), "-1m@m"), "Current", _time>relative_time(now(), "-1w@w1") AND _time<relative_time(now(), "-1w@w6"), "Last Week", 1=1, "Somewhere in between")
  | 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"