https://community.splunk.com/t5/Splunk-Search/How-to-achieve-row-count-until-data-changes-in-column/m-p/623722#M216830 <P>I have a table with 3 columns: _time, type and action<BR /><BR />| makeresults count=10<BR />| eval type = "typeA"<BR />| eval action = if((random()%2) == 1, "open", "close")<BR />| union<BR />[| makeresults count=10<BR />| eval type = "typeB"<BR />| eval action = if((random()%2) == 1, "open", "close")]</P> <P>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...</P> <TABLE border="1" width="51.019967777937794%"> <TBODY> <TR> <TD width="14.285714285714286%" height="25px">_time</TD> <TD width="14.285714285714286%" height="25px">typeA</TD> <TD width="14.285714285714286%" height="25px">typeB</TD> <TD width="14.285714285714286%" height="25px">typeA_count</TD> <TD width="14.285714285714286%" height="25px">typeB_count</TD> </TR> <TR> <TD width="14.285714285714286%" height="47px">2022-01-01 05:00:00</TD> <TD width="14.285714285714286%" height="47px">open</TD> <TD width="14.285714285714286%" height="47px">close</TD> <TD width="14.285714285714286%" height="47px">1</TD> <TD width="14.285714285714286%" height="47px">1</TD> </TR> <TR> <TD width="14.285714285714286%" height="47px">2022-01-01 05:00:01</TD> <TD width="14.285714285714286%" height="47px">open</TD> <TD width="14.285714285714286%" height="47px">open</TD> <TD width="14.285714285714286%" height="47px">2</TD> <TD width="14.285714285714286%" height="47px">1</TD> </TR> <TR> <TD width="14.285714285714286%" height="47px">2022-01-01 05:00:02</TD> <TD width="14.285714285714286%" height="47px">close</TD> <TD width="14.285714285714286%" height="47px">close</TD> <TD width="14.285714285714286%" height="47px">1</TD> <TD width="14.285714285714286%" height="47px">1</TD> </TR> <TR> <TD width="14.285714285714286%" height="47px">2022-01-01 05:00:03</TD> <TD width="14.285714285714286%" height="47px">open</TD> <TD width="14.285714285714286%" height="47px">open</TD> <TD width="14.285714285714286%" height="47px">1</TD> <TD width="14.285714285714286%" height="47px">1</TD> </TR> <TR> <TD width="14.285714285714286%" height="47px">2022-01-01 05:00:04</TD> <TD width="14.285714285714286%" height="47px">close</TD> <TD width="14.285714285714286%" height="47px">open</TD> <TD width="14.285714285714286%" height="47px">1</TD> <TD width="14.285714285714286%" height="47px">2</TD> </TR> <TR> <TD width="14.285714285714286%" height="47px">2022-01-01 05:00:05</TD> <TD width="14.285714285714286%" height="47px">open</TD> <TD width="14.285714285714286%" height="47px">close</TD> <TD width="14.285714285714286%" height="47px">1</TD> <TD width="14.285714285714286%" height="47px">1</TD> </TR> <TR> <TD>2022-01-01 05:00:06</TD> <TD>open</TD> <TD>close</TD> <TD>2</TD> <TD>2</TD> </TR> <TR> <TD>2022-01-01 05:00:07</TD> <TD>open</TD> <TD>close</TD> <TD>3</TD> <TD>3</TD> </TR> <TR> <TD>2022-01-01 05:00:08</TD> <TD>close</TD> <TD>open</TD> <TD>1</TD> <TD>1</TD> </TR> <TR> <TD>2022-01-01 05:00:09</TD> <TD>open</TD> <TD>close</TD> <TD>1</TD> <TD>1</TD> </TR> </TBODY> </TABLE> <P><BR />Thanks</P> Thu, 08 Dec 2022 19:37:38 GMT michael_vi 2022-12-08T19:37:38Z https://community.splunk.com/t5/Splunk-Search/How-to-achieve-row-count-until-data-changes-in-column/m-p/623722#M216830 <P>I have a table with 3 columns: _time, type and action<BR /><BR />| makeresults count=10<BR />| eval type = "typeA"<BR />| eval action = if((random()%2) == 1, "open", "close")<BR />| union<BR />[| makeresults count=10<BR />| eval type = "typeB"<BR />| eval action = if((random()%2) == 1, "open", "close")]</P> <P>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...</P> <TABLE border="1" width="51.019967777937794%"> <TBODY> <TR> <TD width="14.285714285714286%" height="25px">_time</TD> <TD width="14.285714285714286%" height="25px">typeA</TD> <TD width="14.285714285714286%" height="25px">typeB</TD> <TD width="14.285714285714286%" height="25px">typeA_count</TD> <TD width="14.285714285714286%" height="25px">typeB_count</TD> </TR> <TR> <TD width="14.285714285714286%" height="47px">2022-01-01 05:00:00</TD> <TD width="14.285714285714286%" height="47px">open</TD> <TD width="14.285714285714286%" height="47px">close</TD> <TD width="14.285714285714286%" height="47px">1</TD> <TD width="14.285714285714286%" height="47px">1</TD> </TR> <TR> <TD width="14.285714285714286%" height="47px">2022-01-01 05:00:01</TD> <TD width="14.285714285714286%" height="47px">open</TD> <TD width="14.285714285714286%" height="47px">open</TD> <TD width="14.285714285714286%" height="47px">2</TD> <TD width="14.285714285714286%" height="47px">1</TD> </TR> <TR> <TD width="14.285714285714286%" height="47px">2022-01-01 05:00:02</TD> <TD width="14.285714285714286%" height="47px">close</TD> <TD width="14.285714285714286%" height="47px">close</TD> <TD width="14.285714285714286%" height="47px">1</TD> <TD width="14.285714285714286%" height="47px">1</TD> </TR> <TR> <TD width="14.285714285714286%" height="47px">2022-01-01 05:00:03</TD> <TD width="14.285714285714286%" height="47px">open</TD> <TD width="14.285714285714286%" height="47px">open</TD> <TD width="14.285714285714286%" height="47px">1</TD> <TD width="14.285714285714286%" height="47px">1</TD> </TR> <TR> <TD width="14.285714285714286%" height="47px">2022-01-01 05:00:04</TD> <TD width="14.285714285714286%" height="47px">close</TD> <TD width="14.285714285714286%" height="47px">open</TD> <TD width="14.285714285714286%" height="47px">1</TD> <TD width="14.285714285714286%" height="47px">2</TD> </TR> <TR> <TD width="14.285714285714286%" height="47px">2022-01-01 05:00:05</TD> <TD width="14.285714285714286%" height="47px">open</TD> <TD width="14.285714285714286%" height="47px">close</TD> <TD width="14.285714285714286%" height="47px">1</TD> <TD width="14.285714285714286%" height="47px">1</TD> </TR> <TR> <TD>2022-01-01 05:00:06</TD> <TD>open</TD> <TD>close</TD> <TD>2</TD> <TD>2</TD> </TR> <TR> <TD>2022-01-01 05:00:07</TD> <TD>open</TD> <TD>close</TD> <TD>3</TD> <TD>3</TD> </TR> <TR> <TD>2022-01-01 05:00:08</TD> <TD>close</TD> <TD>open</TD> <TD>1</TD> <TD>1</TD> </TR> <TR> <TD>2022-01-01 05:00:09</TD> <TD>open</TD> <TD>close</TD> <TD>1</TD> <TD>1</TD> </TR> </TBODY> </TABLE> <P><BR />Thanks</P> Thu, 08 Dec 2022 19:37:38 GMT https://community.splunk.com/t5/Splunk-Search/How-to-achieve-row-count-until-data-changes-in-column/m-p/623722#M216830 michael_vi 2022-12-08T19:37:38Z https://community.splunk.com/t5/Splunk-Search/How-to-achieve-row-count-until-data-changes-in-column/m-p/623727#M216835 <LI-CODE lang="markup">| 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</LI-CODE> Thu, 08 Dec 2022 13:55:45 GMT https://community.splunk.com/t5/Splunk-Search/How-to-achieve-row-count-until-data-changes-in-column/m-p/623727#M216835 ITWhisperer 2022-12-08T13:55:45Z https://community.splunk.com/t5/Splunk-Search/How-to-achieve-row-count-until-data-changes-in-column/m-p/624001#M216945 <P>And another thing I need help with</P><P>I need to get the max action_count from the previous SPL:<BR /><BR /></P><TABLE border="1" width="100%"><TBODY><TR><TD width="50%" height="25px">2022-12-06 00:03:00</TD><TD width="50%" height="25px">1</TD></TR><TR><TD width="50%" height="25px">2022-12-06 00:04:00</TD><TD width="50%" height="25px">1</TD></TR><TR><TD width="50%" height="25px">2022-12-06 00:05:00</TD><TD width="50%" height="25px">1</TD></TR><TR><TD width="50%" height="25px">2022-12-06 00:06:00</TD><TD width="50%" height="25px">2</TD></TR><TR><TD width="50%" height="25px">2022-12-06 00:07:00</TD><TD width="50%" height="25px">3</TD></TR><TR><TD width="50%" height="25px">2022-12-06 00:08:00</TD><TD width="50%" height="25px">4</TD></TR><TR><TD width="50%" height="25px">2022-12-06 00:09:00</TD><TD width="50%" height="25px">1</TD></TR><TR><TD width="50%" height="25px">2022-12-06 00:10:00</TD><TD width="50%" height="25px">2</TD></TR><TR><TD width="50%" height="25px">2022-12-06 00:11:00</TD><TD width="50%" height="25px">3</TD></TR><TR><TD width="50%" height="25px">2022-12-06 00:12:00</TD><TD width="50%" height="25px">1</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>TO</P><TABLE border="1" width="100%"><TBODY><TR><TD width="50%" height="25px">2022-12-06 00:03:00</TD><TD width="50%" height="25px">1</TD></TR><TR><TD width="50%" height="25px">2022-12-06 00:04:00</TD><TD width="50%" height="25px">1</TD></TR><TR><TD width="50%" height="25px">2022-12-06 00:08:00</TD><TD width="50%" height="25px">4</TD></TR><TR><TD width="50%" height="25px">2022-12-06 00:11:00</TD><TD width="50%" height="25px">3</TD></TR><TR><TD width="50%" height="25px">2022-12-06 00:12:00</TD><TD width="50%" height="25px">1</TD></TR></TBODY></TABLE><P>&nbsp;</P> Mon, 12 Dec 2022 13:55:04 GMT https://community.splunk.com/t5/Splunk-Search/How-to-achieve-row-count-until-data-changes-in-column/m-p/624001#M216945 michael_vi 2022-12-12T13:55:04Z