Splunk Search

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

Communicator

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

index xyz sourcetype=TICKET_OPENED PRODUCT_TYPE=A GROUP=B

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

0 Karma
1 Solution

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

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

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=TICKETACTIVITY)) AS NEWNAME BY PRODUCTTYPEAND_GROUP

pulls 0 events

0 Karma

Esteemed Legend

Fixed in original answer; retry now.

0 Karma

Communicator

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

0 Karma

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

Communicator

This search is good as it only pulls tickets from sourcetype=TICKETACTIVITY using the filtered tickets from sourcetype=TICKETOPENED PRODUCTTYPE=A GROUP=B, however, all TICKETACTIVITY_COUNTs is equal to 0.

0 Karma

Legend

Try the updated query

0 Karma

Communicator

You are the man, thank you!

0 Karma

Path Finder

This should give you a count by ticketnumber in ticketactivity 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

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

Communicator

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

sourcetype=TICKET_OPENED

TICKETNUMBER 01 (PRODUCTTYPE=A GROUP=B)
TICKETNUMBER 02 (PRODUCTTYPE=B GROUP=A)

I am only looking for TICKETNUMBER 01 (PRODUCTTYPE=A GROUP=B) in above.

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

TICKETNUMBER 01 activity log 1
TICKET
NUMBER 01 activity log 2
TICKETNUMBER 01 activity log 3
TICKET
NUMBER 02 activity log 1
TICKETNUMBER 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 TICKETNUMBER 01, and eventually find the ticket activity logs stats for all TICKETNUMBERs that belong to PRODUCT_TYPE=A GROUP=B.

0 Karma

Communicator

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

0 Karma