How to count the number of times an event in one sourcetype is occuring in another sourcetype?

[christopheryu]

I have two sourcetypes, TICKET_OPENED & TICKET_ACTIVITY, both of which have a common field TICKET_NUMBER. I am able to narrow my search on sourcetype TICKET_OPENED using the following:

index xyz sourcetype=TICKET_OPENED PRODUCT_TYPE=A GROUP=B

A specific TICKET_NUMBER from sourcetype =TICKET_OPENED can have multiple activity logs in sourcetype=TICKET_ACTIVITY. How can I get the number of activity logs of a TICKET_NUMBER on sourcetype=TICKET_ACTIVITY? Please note that TICKET_ACTIVITY does not have the fields PRODUCT_TYPE & GROUP.

[woodcock]

Like this:

index xyz sourcetype=TICKET_ACTIVITY OR (sourcetype=TICKET_OPENED PRODUCT_TYPE=A GROUP=B)
| eval PRODUCT_TYPE_AND_GROUP = PRODUCT_TYPE . "::" . GROUP
| eventstats values(PRODUCT_TYPE_AND_GROUP) AS PRODUCT_TYPE_AND_GROUP BY TICKET_NUMBER
| stats count(eval(sourcetype=TICKET_ACTIVITY)) AS ticket_count BY PRODUCT_TYPE_AND_GROUP

[christopheryu]

Splunk message below:

Error in 'stats' command: You must specify a rename for the aggregation specifier on the dynamically evaluated field 'count(eval(sourcetype=TICKET_ACTIVITY))'.

and when I do a rename ... stats count(eval(sourcetype=TICKET_ACTIVITY)) AS NEW_NAME BY PRODUCT_TYPE_AND_GROUP

pulls 0 events

[woodcock]

Fixed in original answer; retry now.

[christopheryu]

"No results found" Got my answer though above, thank you.

[sundareshr · Accepted solution]

Try this

index xyz (sourcetype=TICKET_OPENED PRODUCT_TYPE=A GROUP=B) OR sourcetype=TICKET_ACTIVITY | stats values(sourcetype) as sourcetype count(eval(sourcetype="TICKET_ACTIVITY")) as TICKET_ACTIVITY_COUNT by TICKET_NUMBER  | where mvcount(sourcetype)=2

[christopheryu]

This search is good as it only pulls tickets from sourcetype=TICKET_ACTIVITY using the filtered tickets from sourcetype=TICKET_OPENED PRODUCT_TYPE=A GROUP=B, however, all TICKET_ACTIVITY_COUNTs is equal to 0.

[sundareshr]

Try the updated query

[christopheryu]

You are the man, thank you!

[chrisduimstra]

This should give you a count by ticket_number in ticket_activity for ticket_number that occur in both sourcetypes.

index=xyz sourcetype=TICKET_OPENED PRODUCT_TYPE=A GROUP=B 
| stats count by TICKET_NUMBER sourcetype 
| append 
    [search index=xyz sourcetype=TICKET_ACTIVITY 
    | stats count by TICKET_NUMBER sourcetype] 
| eventstats count(sourcetype) as num by TICKET_NUMBER 
| where num=2 
| where sourcetype=TICKET_ACTIVITY
| stats count by TICKET_NUMBER

[masonmorales]

It sounds like you're trying to do something like this...

index xyz (sourcetype=TICKET_OPENED PRODUCT_TYPE=A GROUP=B) OR sourcetype=TICKET_ACTIVITY | stats count(TICKET_ACTIVITY)  as TICKET_ACTIVITY_COUNT by TICKET_NUMBER

[christopheryu]

Thanks for the response but it does not work. Please note that I narrowed my ticket search from sourcetype=TICKET_OPENED using the fields PRODUCT_TYPE=A GROUP=B. Sourcetype TICKET_ACTIVITY does not have these fields and it includes other tickets. Illustration below:

sourcetype=TICKET_OPENED

TICKET_NUMBER 01 (PRODUCT_TYPE=A GROUP=B)
TICKET_NUMBER 02 (PRODUCT_TYPE=B GROUP=A)

I am only looking for TICKET_NUMBER 01 (PRODUCT_TYPE=A GROUP=B) in above.

I need to find how many activity logs TICKET NUMBER 01 has in sourcetype=TICKET_ACTIVITY

TICKET_NUMBER 01 activity log 1
TICKET_NUMBER 01 activity log 2
TICKET_NUMBER 01 activity log 3
TICKET_NUMBER 02 activity log 1
TICKET_NUMBER 03 activity log 1
TICKET_NUMBER 03 activity log 2

So the result I am trying to come up with is 3 activity logs for TICKET_NUMBER 01, and eventually find the ticket activity logs stats for all TICKET_NUMBERs that belong to PRODUCT_TYPE=A GROUP=B.

[christopheryu]

As additional info, for yesterday's data, sourcetype=TICKET_OPENED PRODUCT_TYPE=A GROUP=B only has 11 tickets. Running your answer resulted to pulling all TICKET_NUMBERs for sourcetype=TICKET_ACTIVITY, which is 30,509 and showing the activity counts for each of these tickets.