Dashboards & Visualizations

streamstats query issue

sawgata12345
Path Finder

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?

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

sawgata12345
Path Finder

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.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

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

Happy Splunking!
0 Karma

sawgata12345
Path Finder

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"

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...