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
| 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
And another thing I need help with
I need to get the max action_count from the previous SPL:
2022-12-06 00:03:00 | 1 |
2022-12-06 00:04:00 | 1 |
2022-12-06 00:05:00 | 1 |
2022-12-06 00:06:00 | 2 |
2022-12-06 00:07:00 | 3 |
2022-12-06 00:08:00 | 4 |
2022-12-06 00:09:00 | 1 |
2022-12-06 00:10:00 | 2 |
2022-12-06 00:11:00 | 3 |
2022-12-06 00:12:00 | 1 |
TO
2022-12-06 00:03:00 | 1 |
2022-12-06 00:04:00 | 1 |
2022-12-06 00:08:00 | 4 |
2022-12-06 00:11:00 | 3 |
2022-12-06 00:12:00 | 1 |
| 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