topic Re: How to count the number of times an event in one sourcetype is occuring in another sourcetype? in Splunk Search

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

christopheryu — Tue, 29 Sep 2020 11:06:49 GMT

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.

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

masonmorales — Wed, 21 Sep 2016 21:47:25 GMT

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

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

christopheryu — Tue, 29 Sep 2020 11:06:52 GMT

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.

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

chrisduimstra — Tue, 29 Sep 2020 11:06:55 GMT

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

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

sundareshr — Thu, 22 Sep 2016 01:36:38 GMT

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

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

christopheryu — Tue, 29 Sep 2020 11:07:41 GMT

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.

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

christopheryu — Tue, 29 Sep 2020 11:07:44 GMT

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.

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

sundareshr — Thu, 22 Sep 2016 13:59:48 GMT

Try the updated query

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

woodcock — Thu, 22 Sep 2016 14:06:57 GMT

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

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

christopheryu — Thu, 22 Sep 2016 15:32:54 GMT

You are the man, thank you!

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

christopheryu — Tue, 29 Sep 2020 11:07:46 GMT

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

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

woodcock — Thu, 22 Sep 2016 16:27:55 GMT

Fixed in original answer; retry now.

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

christopheryu — Thu, 22 Sep 2016 18:42:04 GMT

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