How can I use streamstats for checking multiple column values.(With or without foreach command for multiple columns)
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 <= 😎 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
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)"
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 <= 😎 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.
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)"
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.
There are more than 10 columns so thats why I was guessing to use foreach.
Could you pad out the question with some examples of incoming event data and expected outcomes?
_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 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
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.