Compare responseTime field toady to last week without using append

[appache]

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

[sundareshr]

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"

[sundareshr · Accepted solution]

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.

[appache]

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

[sundareshr]

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)

[appache]

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

[sundareshr]

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

[appache]

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

[appache]

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

[sundareshr]

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

[sundareshr]

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"

[appache]

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

[sundareshr]

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

[appache]

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