How to get total number of Count?

[Kirthika]

Device_ID	Handset_ID
1	Serial Number
1	Started
1	1420
1	1420
1	1420
1	Serial Number
1	Started
1	1420
1	Serial Number
1	Started
1	1420
1	1420
1	1420
1	Serial Number
1	Started
1	Serial Number
1	Started
2	1420
2	1420
2	Serial Number
2	Started
2	Serial Number
2	Started
2	Serial Number
2	Started
2	20
2	Serial Number
2	Started
2	Serial Number
2	Started

Expected Output: Count should be based on keyword "Serial Number"  followed by Handset_ID to another "Serial Number". If there is no Handset_ID between , it should skip the rows. Eg.last 4 rows

Handset_ID	Total Count
1420	4
20	1

[ITWhisperer]

| where Handset_ID == "Serial Number" OR match(Handset_ID, "\d+")
| autoregress Handset_ID
| where Handset_ID_p1 == "Serial Number" AND match(Handset_ID, "\d+")
| stats count by Handset_ID

[Kirthika]

Thanks. Its working

[scelikok]

Hi @Kirthika,

You can try below;

| autoregress Handset_ID
| search Handset_ID_p1="Started" Handset_ID!="Serial Number"
| stats count by Handset_ID

[Kirthika]

Hi,

Thanks for your reply. Its working