topic Re: use streamstats for checking multiple column values in Splunk Search

use streamstats for checking multiple column values

sahil237888 — Wed, 31 Oct 2018 13:40:27 GMT

How can I use streamstats for checking multiple column values.(With or without foreach command for multiple columns)

Re: use streamstats for checking multiple column values

msivill_splunk — Thu, 01 Nov 2018 08:51:28 GMT

Could you pad out the question with some examples of incoming event data and expected outcomes?

Re: use streamstats for checking multiple column values

sahil237888 — Wed, 14 Nov 2018 14:17:59 GMT

Hey msivill,

Basically the requirement is :

_time A B C D
2018-11-14 07:56:00 1 0 0 0
2018-11-14 07:57:00 1 1 0 1
2018-11-14 07:58:00 1 1 0 0
2018-11-14 07:59:00 0 0 1 0
2018-11-14 08:00:00 1 1 1 0
2018-11-14 08:01:00 1 0 0 0
2018-11-14 08:02:00 1 0 0 1
2018-11-14 08:03:00 1 0 0 0
2018-11-14 08:04:00 0 0 1 0
2018-11-14 08:05:00 1 1 0 1
2018-11-14 08:06:00 1 0 0 0

streamstats to check for each column (A,B,C,D) and if there is continuous 0 for any column for 4 minutes, it should alert.

As in the above output for B column (between 08:01 - 08:04) and for D (between 07:58 - 08:01) we have continuous 0 so alert should be triggered that B and D column has issue.

Re: use streamstats for checking multiple column values

sahil237888 — Wed, 14 Nov 2018 16:16:31 GMT

Streamstats to check for each column (A,B,C,D) and if there is continuous 0 for any column for 4 minutes, it should alert.

As in the above output B have 0 (between 08:01 and 08:04) and for D (between 07:58 - 08:01) there is continuous 0 so the alert should have B and D have continuous 0

Re: use streamstats for checking multiple column values

msivill_splunk — Thu, 15 Nov 2018 11:08:38 GMT

The following is a self contained example, so people can quickly try this in their own instance of Splunk. Everything up to and including the table command generates the test data.

| makeresults count=11 
| streamstats count as row 
| eval now = relative_time(now(),"-1m@m")
| eval _time = now - ( row * 60 ) 
| eval a = IF ( (row >= 1 and row <= 3) or (row >= 5 and row <= 8) or (row >= 10 and row <= 11), 1, 0 )
| eval b = IF ( (row >= 2 and row <= 3) or (row == 5) or (row == 10), 1, 0 ) 
| eval c = IF ( (row >= 4 and row <= 5) or (row == 9) , 1, 0 ) 
| eval d = IF ( (row == 2) or (row == 7) or (row == 10), 1, 0 ) 
| table _time, a, b, c, d 
| streamstats count as count_a by a reset_on_change=true 
| streamstats count as count_b by b reset_on_change=true 
| streamstats count as count_c by c reset_on_change=true 
| streamstats count as count_d by d reset_on_change=true 
| where (a==0 and count_a >= 4) or (b==0 and count_b >= 4) or (c==0 and count_c >= 4) or (d==0 and count_d >= 4)

This will return one or more rows if there are 4 or more continuous 0 matches across any of the columns (a,b,c,d). An assumption is made that a new record appears on the minute. If you want a specific alert for each column you may want to consider running several queries instead of one.

Re: use streamstats for checking multiple column values

msivill_splunk — Thu, 15 Nov 2018 11:51:45 GMT

A time based one....

| makeresults count=11 
| streamstats count as row 
| eval now = relative_time(now(),"-1m@m")
| eval _time = now - ( row * 60 ) 
| eval a = IF ( (row >= 1 and row <= 3) or (row >= 5 and row <= 8) or (row >= 10 and row <= 11), 1, 0 )
| eval b = IF ( (row >= 2 and row <= 3) or (row == 5) or (row == 10), 1, 0 ) 
| eval c = IF ( (row >= 4 and row <= 5) or (row == 9) , 1, 0 ) 
| eval d = IF ( (row == 2) or (row == 7) or (row == 10), 1, 0 ) 
| table _time, a, b, c, d 
| streamstats first(_time) as time_a_first, last(_time) as time_a_last by a reset_on_change=true
| streamstats first(_time) as time_b_first, last(_time) as time_b_last by b reset_on_change=true
| streamstats first(_time) as time_c_first, last(_time) as time_c_last by c reset_on_change=true
| streamstats first(_time) as time_d_first, last(_time) as time_d_last by d reset_on_change=true
| eval time_a_duration = time_a_first - time_a_last 
| eval time_b_duration = time_b_first - time_b_last 
| eval time_c_duration = time_c_first - time_c_last 
| eval time_d_duration = time_d_first - time_d_last 
| where (a==0 and time_a_duration >= 180) or (b==0 and time_b_duration >= 180) or (c==0 and time_c_duration >= 180) or (d==0 and time_d_duration >= 180) 
| stats max(time_a_duration) as a, max(time_b_duration) as b, max(time_c_duration) as c, max(time_d_duration) as d
| untable row indicator duration 
| table indicator duration
| where duration >= 180

Re: use streamstats for checking multiple column values

sahil237888 — Tue, 29 Sep 2020 22:03:56 GMT

This seems to be somewhat working for rows but the result should be based on columns.
and there are multiple columns (say more than 10) so its difficult to create too many eval conditions if we have multiple columns.
Will it be possible to use foreach and streamstats in this.
like below :

| streamstats window=5 sum(column_) as newcolumn_ reset_before="("column_*"<0)"

Re: use streamstats for checking multiple column values

sahil237888 — Tue, 29 Sep 2020 22:03:58 GMT

| streamstats window=5 sum(column_) as newcolumn_ reset_before="("column_*"<0)"

Re: use streamstats for checking multiple column values

msivill_splunk — Thu, 15 Nov 2018 12:39:37 GMT

If it is for only ten columns I would expand out the above example. Not sure its difficult (just repetitive) as there is a pattern in the SPL that needs to be repeated.

Re: use streamstats for checking multiple column values

sahil237888 — Thu, 15 Nov 2018 12:57:51 GMT

There are more than 10 columns so thats why I was guessing to use foreach.