Developing for Splunk Enterprise

Compare the Avg value for a Particular Application_Name for today with the last week for any particular timing.

sagar_shubham
Explorer
index = abc earliest=-70m@m latest=@m| stats avg(AVERAGE_RESPONSE_TIME) as Today by Time Application_Name |eval Today= round(Today,2) |appendcols [search index = abc earliest=-7d@m-70m latest=-7d@m |stats avg(AVERAGE_RESPONSE_TIME) as LastWeek by Time  Application_Name |eval LastWeek= round(LastWeek,2)|eval _time=relative_time(now(),"-7d")]|lookup RESP_LOOKUP_App Application_Name as Application_Name OUTPUTNEW RESP_DEVIATION_THRESHOLD | eval AVG_RESPONSE_Deviation=(Today/LastWeek)*100|table Time Application_Name Today LastWeek AVG_RESPONSE_Deviation RESP_DEVIATION_THRESHOLD | where AVG_RESPONSE_Deviation>RESP_DEVIATION_THRESHOLD

My Aim is to compare the Avg value for a Particular Application_Name for today with the last week for any particular timing.
E.g Today For a particular time "t" for a particular Application_Name "x" . I am calculating the Average of "AVERAGE_RESPONSE_TIME" field mentioned in the logs. When I am trying to find the average of AVERAGE_RESPONSE_TIME field for the application named "x", for the particular time "t" in the last week, it is not showing the correct value. I guess it is considering another Application_Name and showing the Average of Average_Response_Time for other application_names for that time.

Please help to modify the query

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

This is a bad use case for appendcols. You cannot be sure that the columns will line up. At any given time, whenever there is a new Application_Name added or an old one retires, then that code will fail.

This gets your values for the two time periods in one pass. Modify the bin _time command to match your use case...

(index = abc earliest=-70m@m latest=@m) OR
(index = abc earliest=-7d@m-70m@m latest=-7d@m)
| fields _time Application_Name

| rename COMMENT as "set the time period and offset the _time "
| eval Date=if(_time< now()-80000,"LastWeek","Today")
| eval _time=if(_time< now()-80000,_time+86400,_time)

| rename COMMENT as "bin the _time and calculate averages for each application for each time period"
| bin _time span=1m
| stats avg(AVERAGE_RESPONSE_TIME) as avgresp by Application_Name _time Date

Then you can use code like this to roll the two Date readings together onto a single record for any given Application

| rename COMMENT as "copy the data to a field named by Date, drop unneeded fields, then roll the two readings onto the same record"
| eval {Date} = avgresp
| fields - Date avgresp
| stats values(*) as * by Application_Name _time 

And finally add your lookup processing...

| lookup RESP_LOOKUP_App Application_Name as Application_Name OUTPUT RESP_DEVIATION_THRESHOLD 
| eval AVG_RESPONSE_Deviation=(Today/LastWeek)*100
| table _time Application_Name Today LastWeek AVG_RESPONSE_Deviation RESP_DEVIATION_THRESHOLD 
| where AVG_RESPONSE_Deviation>RESP_DEVIATION_THRESHOLD

Since no such field can logically exist at the time of the lookup, the NEW part of OUTPUTNEW is not required.

I suspect your AVG_RESPONSE_Deviation should be calculated in either absolute terms or relative terms, not as a ratio. It might be supposed to be something like this...

| eval AVG_RESPONSE_Deviation=( 100.0 *(Today-LastWeek) /LastWeek)

You might need an absolute value in there as well, depending on how you want to define the difference. You are only testing in one direction (greater than something) and the avg response can deviate in both directions.

0 Karma

sagar_shubham
Explorer

I am not getting any values in Today, Lastweek column. 😞

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!