Splunk Search

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

christopheryu
Communicator

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.

0 Karma
1 Solution

sundareshr
Legend

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

View solution in original post

woodcock
Esteemed Legend

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
0 Karma

christopheryu
Communicator

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

0 Karma

woodcock
Esteemed Legend

Fixed in original answer; retry now.

0 Karma

christopheryu
Communicator

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

0 Karma

sundareshr
Legend

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
Communicator

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.

0 Karma

sundareshr
Legend

Try the updated query

0 Karma

christopheryu
Communicator

You are the man, thank you!

0 Karma

chrisduimstra
Path Finder

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
0 Karma

masonmorales
Influencer

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
Communicator

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.

0 Karma

christopheryu
Communicator

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.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...