Extract and count IDs from a table

[wkk]

Hi! 🙂 

I have a fallowing table:

SESSION_ID	SUBMITTED_FROM	STAGE
1		submit
1	startPage	someStage1
2		submit
2	page1	someStage1
2	page2	someStage2

How could I count the number of SESSION_IDs that has SUBMITTED_FROM=startPage and STAGE=submit?

So looking at the above table the outcome of that logic should be 2 SESSION_IDs

[wkk]

I slightly changed the query, as I didn't want to use search. Query ends up with the same results.

index=your_index
| stats 
   values(SUBMITTED_FROM) AS SUBMITTED_FROM
   values(STAGE) AS STAGE
   BY SESSION_ID
| where SUBMITTED_FROM=startPage STAGE=submit
| stats count BY SESSION_ID

[wkk]

@gcusello thank you that solved my case 

[gcusello · Accepted solution]

Hi @wkk,

you could try something like this:

index=your_index
| stats 
   values(SUBMITTED_FROM) AS SUBMITTED_FROM
   values(STAGE) AS STAGE
   BY SESSION_ID
| mvexpand SUBMITTED_FROM
| mvexpand STAGE
| search SUBMITTED_FROM=startPage STAGE=submit
| stats count BY SESSION_ID

Ciao.

Giuseppe

[gcusello]

Hi @wkk ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉