Splunk Search

How to achieve row count until data changes in column?

michael_vi
Path Finder

I have a table with 3 columns: _time, type and action

| makeresults count=10
| eval type = "typeA"
| eval action = if((random()%2) == 1, "open", "close")
| union
[| makeresults count=10
| eval type = "typeB"
| eval action = if((random()%2) == 1, "open", "close")]

I need to create a column for each type that would identify the change in the column action and count # of actions in ascending order like this...

_time typeA typeB typeA_count typeB_count
2022-01-01 05:00:00 open close 1 1
2022-01-01 05:00:01 open open 2 1
2022-01-01 05:00:02 close close 1 1
2022-01-01 05:00:03 open open 1 1
2022-01-01 05:00:04 close open 1 2
2022-01-01 05:00:05 open close 1 1
2022-01-01 05:00:06 open close 2 2
2022-01-01 05:00:07 open close 3 3
2022-01-01 05:00:08 close open 1 1
2022-01-01 05:00:09 open close 1 1


Thanks

Labels (3)
Tags (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults count=10
| eval type = "typeA"
| eval action = if((random()%2) == 1, "open", "close")
| union
[| makeresults count=10
| eval type = "typeB"
| eval action = if((random()%2) == 1, "open", "close")]
| streamstats reset_on_change=true count as action_count by action
| streamstats reset_on_change=true count as type_count by type

View solution in original post

michael_vi
Path Finder

And another thing I need help with

I need to get the max action_count from the previous SPL:

2022-12-06 00:03:001
2022-12-06 00:04:001
2022-12-06 00:05:001
2022-12-06 00:06:002
2022-12-06 00:07:003
2022-12-06 00:08:004
2022-12-06 00:09:001
2022-12-06 00:10:002
2022-12-06 00:11:003
2022-12-06 00:12:001

 

TO

2022-12-06 00:03:001
2022-12-06 00:04:001
2022-12-06 00:08:004
2022-12-06 00:11:003
2022-12-06 00:12:001

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults count=10
| eval type = "typeA"
| eval action = if((random()%2) == 1, "open", "close")
| union
[| makeresults count=10
| eval type = "typeB"
| eval action = if((random()%2) == 1, "open", "close")]
| streamstats reset_on_change=true count as action_count by action
| streamstats reset_on_change=true count as type_count by type
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

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

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...