Splunk Search

use streamstats for checking multiple column values

sahil237888
Path Finder

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

Tags (1)
0 Karma

msivill_splunk
Splunk Employee
Splunk Employee

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
0 Karma

sahil237888
Path Finder

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)"

0 Karma

msivill_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

sahil237888
Path Finder

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)"

0 Karma

msivill_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

sahil237888
Path Finder

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

0 Karma

msivill_splunk
Splunk Employee
Splunk Employee

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

0 Karma

sahil237888
Path Finder

_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

0 Karma

sahil237888
Path Finder

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.

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 ...