How to count events that are common or existing among multiple sourcetypes?

[christopheryu]

Seeking help of Splunk Gurus.

I have three sourcetypes : TICKET_OPENED, TICKET_ACTIVITY & TICKET_CLOSED. A common field among these three sourcetypes is TICKET_NUMBER.

It is possible that a specific TICKET_NUMBER was opened (exists in TICKET_OPENED) but was not closed (does not exist in TICKET_CLOSED). My question is how do I count the number of distinct TICKET_NUMBER that exist in all of the three sourcetypes?

PS - tried to look if this was previously asked but can't find any answer.

[twinspop]

Try this to find transactions missing a step:

(sourcetype=TICKET_OPENED OR sourcetype=TICKET_ACTIVITY  OR sourcetype=TICKET_CLOSED) |
stats count list(SOURCETYPE) as Types by TICKET_NUMBER | where count<3

Or, specific to your question:

(sourcetype=TICKET_OPENED OR sourcetype=TICKET_ACTIVITY  OR sourcetype=TICKET_CLOSED) |
stats dc(TICKET_NUMBER) as TICKET_NUMBERS by sourcetype  

[christopheryu]

Thank you but not quite what I was looking for.

[sundareshr · Accepted solution]

Try this

index=xyz sourcetype=TICKET_OPENED OR sourcetype=TICKET_ACTIVITY OR sourcetype=TICKET_CLOSED | stats values(sourcetype) sourcetypes  dc(sourcetype) as count by TICKET_NUMBER | where count=3

[christopheryu]

removed "sourcetypes" in the stats pipe and it's good to go, thank you!

[sundareshr]

that was meant to be as sourcetypes

[christopheryu]

both produce the same result that I am looking for. Thanks again 🙂