I am trying to include something in my query like this
index=* domain=acbd_1 earliest=-16m@m latest=-1m@m | bin _time span=15m | stats avg(responstime) by domain | stats values(avg(responsetime)) as avg_res_time by _time, domain | eval ts_time=_time * 1000 | where avg_res_time > 2 | top limit=1 avg_res_time by domain, ts_time | table ts_time, domain, avg_res_time, channel, lob
I want the display to be like this :
ts_time domain avg_res_time channel lob
1506542400000 abcd_1 120.83 dot Clear
1506542600000 abcd_1 82.11 dot Clear
1506563400000 acbd_1 9 dot Clear
I want result As shown in above table; ts_time, domain,avg_res_time as extracted from the data we have. I am trying to add "channel, lob" fields with "dot, Clear" values in my splunk result table by using query, in other words I want to predefine channel and lob values within the query and display them in table. How can I achieve it? Appreciate the help ASAP, please.
@chow11 - yes, eval
is the proper way to set the value of a variable. However, it would be easier to do the eval at the end rather than complicating the code above, which is already way more complicated than you need.
Binning the _time at 15m doesn't make sense if you are only running a 15m range of time. Your first stats command will get nothing because of the misspelling of the field. Your second will get nothing because the _time
field was dropped by the first stats
. Your where will have no effect, because after the stats (if it were working) there would only be one avg_resp_time per combination of domain and _time. Your domain is a single domain anyway, so it's largely redundant in the code.
I'm going to assume that what you were trying to do is find out the average response time for the domain over a period of time, and eliminate all records where the avg response time is less than 2 seconds. I'm going to assume the "beats" are every 15 minutes, even though the time between two of your examples is only 200 seconds.
index=* domain=acbd_1
| bin _time span=15m
| stats avg(responsetime) as avg_res_time by domain _time
| eval ts_time=_time * 1000
| where avg_res_time > 2
| eval channel= "dot"
| eval lob="Clear"
| table ts_time, domain, avg_res_time, channel, lob
Hi DalJeanis,
Thank you for the prompt response. In my above question I was using one domain as example. I am trying to find the highest avg response among a set of 6 domains for every 15mins. Trying to get the highest avg response time value and the domain name which has highest avg response time value in that given 15mins (when job runs) and the table should contain the timestamp, domain name, avg response time value, lob name and channel name. I am trying to use that data to push the data into influxDB via job which runs every 15mins.
If you know a better and simple query to get it accomplished, please let me know.
Your above response was also useful for me, but for a different task I am working on.
I think i found the solution:
in the query after "bin _time span=15m " add this " |eval channel="dot" , lob="Clear" | " and add the created fields channel & lob at the end of other declaration like this,
stats values(avg.......... , channel , lob | ........| top ........, channel, lob | table .............., channel, lob