topic Re: streamstats query issue in Dashboards & Visualizations

streamstats query issue

sawgata12345 — Tue, 21 Aug 2018 10:39:24 GMT

Hi,

index=$indx$ nodeIdStr=$selswitch$ |sort _time,rtIOt|fields nodeIdStr,sid,did,lun,rtIOt,tmrtIOc|
fields _time,nodeIdStr,rtIOt, tmrtIOc, sid, did, lun|eval combination=sid."-".did."-".lun|eval timediff=tmrtIOc| 
streamstats current=f last(rtIOt) as last_rtio by combination | rename rtIOt as current_rtio | eval diffrtio =  current_rtio -last_rtio 
|eval res=diffrtio/timediff | timechart span=30  avg(res) as AVG  usenull=f by combination

I am using this query above which finds difference between two rtIOt between two events, but in each event has "tmrtIOc" also.
I need to find the difference of tmrtIOc as well in the same query. I tried like below but it didnt work

index=$indx$ nodeIdStr=$selswitch$ |sort _time,rtIOt|fields nodeIdStr,sid,did,lun,rtIOt,tmrtIOc|
fields _time,nodeIdStr,rtIOt, tmrtIOc, sid, did, lun|eval combination=sid."-".did."-".lun|eval timediff=tmrtIOc| 
streamstats current=f last(rtIOt) as last_rtio by combination | rename rtIOt as current_rtio | eval diffrtio =  current_rtio -last_rtio |
streamstats current=f last(tmrtIOc) as last_tmIOc by combination | rename tmrtIOc as current_tmIOc | eval difftmIOc =  current_tmIOc -last_tmIOc |eval res=diffrtio/timediff | timechart span=30  avg(res) as AVG  usenull=f by combination

I tried below one also:

index=$indx$ nodeIdStr=$selswitch$ |sort _time,rtIOt|fields nodeIdStr,sid,did,lun,rtIOt,tmrtIOc|
fields _time,nodeIdStr,rtIOt, tmrtIOc, sid, did, lun|eval combination=sid."-".did."-".lun|eval timediff=tmrtIOc| 
streamstats current=f last(rtIOt) as last_rtio,last(tmrtIOc) as last_tmIOc by combination | rename rtIOt as current_rtio|rename tmrtIOc as current_tmIOc | eval diffrtio =  current_rtio -last_rtio |  eval difftmIOc =  current_tmIOc -last_tmIOc|eval res=diffrtio/timediff | timechart span=30  avg(res) as AVG  usenull=f by combination

where am I doing wrong?

Re: streamstats query issue

renjith_nair — Tue, 21 Aug 2018 13:17:49 GMT

@sawgata12345, whats the problem/error/wrong in the result?

Re: streamstats query issue

DalJeanis — Tue, 21 Aug 2018 18:52:58 GMT

what, exactly, is tmrtIOc, and what exactly is tfIOt?

Re: streamstats query issue

DalJeanis — Tue, 21 Aug 2018 19:04:15 GMT

OKay, there are several things that could be wrong, depending on what your data really is. Here are my assumptions...

1) rtIOt is a reading of some kind.
2) tmrtIOc is a timestamp of some kind.
3) There is a_time on each record that equates to tmrtIOc, and _time is in normal epoch time, no matter what scale tmrtIOc might be in.
4) You are looking for the difference between successive readings, divided by the difference between successive readings times, in whatever unit is present in each of those fields.

If all the above assumptions hold, then the following should get you what you want...

 index=$indx$ nodeIdStr=$selswitch$
| fields _time, nodeIdStr, rtIOt, tmrtIOc, sid, did, lun
| eval combination=sid."-".did."-".lun
| sort 0 combination, tmrtIOc
| streamstats current=f last(rtIOt) as last_rtio last(tmrtIOc) as last_tmrtIOc by combination
| eval diffrtio =  coalesce(rtIOt - last_rtio,0) 
| eval difftmrtIOc =  coalesce(tmrtIOc - last_tmrtIOc,1)
| eval res = diffrtio / difftmrtIOc
| timechart span=30  avg(res) as AVG  usenull=f by combination

Items to note -

1) You can do multiple aggregate commands in a streamstats at one pass.

2) Make sure to code the 0 in | sort 0 ...your sort fields.... Sort, in splunk, is a transforming command that defaults to limit the number of results to 100. You need the 0 to have it return all values.

3) Difference will be null for the first record of each combination, since there was no prior event. By defaulting the reading difference to 0, and the time difference to 1, we achieve a start at 0 at the beginning of the report.

Re: streamstats query issue

sawgata12345 — Fri, 24 Aug 2018 07:45:34 GMT

Hi
actually when i am using one streamstats its showing results but as soon as i put a pipe(|) and add one more streamstats for another parameter it shows "No results found"

Re: streamstats query issue

sawgata12345 — Fri, 24 Aug 2018 07:56:30 GMT

these are just parameters received in json format.
suppose like this
{"nodeIdStr":"sw1","sid":"001","did":"0000","lun":"0001","rtIOt":1002,"tmrtIOc":1001000} lot other parameters are there but only these parameters are required for this query.
rtIOt is float type and its total read time,
tmrtIOc is also float type and total bytes transfered during the above read time
These both values are cumulative so need to take a difference with streamstats function between two events.