Splunk Search

How do you do a multisearch query with the dedup command in a subsearch?

amdhindsa
New Member

I need to search on multiple indexes with the need of the dedup command on one of the searches, for which I only need to pull unique events based on one of the fields. I can get the expected results with the below query, but it's really slow (if the search is run for 24 hrs).

index=x  “32432” “client” | append [search index=y “23232” “client” ] | append [search index=z “2323” “client” | spath "EventStreamData.args.id” | rename"EventStreamData.args.id” as ID | dedup ID ] | spath "EventStreamData.eventName" | rename "EventStreamData.eventName" as "Event_Name" 
| timechart span=1h count(eval(Event_Name==“32432”)) as x_count count(eval(Event_Name==“23232”)) as y_count count(eval(Event_Name==“2323” )) as z_count
| table _time, x_count, y_count, z_count

And I have the below query which performs way better, but I am not able to use dedup in this for the subsearch.

(index=x “32432”) OR (index=y “23232”) OR (index=z “2323”) “client” | spath "EventStreamData.eventName" | rename "EventStreamData.eventName" as "Event_Name" 
| timechart span=1h count(eval(Event_Name==“32432”)) as x_count count(eval(Event_Name==“23232”)) as y_count count(eval(Event_Name==“2323” )) as z_count
| table _time, x_count, y_count, z_count

My question is, how can I improve the performance of the first query or add dedup for index "z" to the second query to filter it only for unique queries?

Thanks!

0 Karma
1 Solution

Vijeta
Influencer

Try below search, it should work.

(index=x “32432”) OR (index=y “23232”) OR (index=z “2323”) “client” | spath "EventStreamData.eventName" | rename "EventStreamData.eventName" as "Event_Name" |spath "EventStreamData.args.id" as "Id"| eval new_id= if(Event_Name==“2323”,Id,NULL)
 | timechart span=1h count(eval(Event_Name==“32432”)) as x_count count(eval(Event_Name==“23232”)) as y_count dc(eval(ISNOTNULL(new_Id )) as z_count
 | table _time, x_count, y_count, z_count

View solution in original post

0 Karma

woodcock
Esteemed Legend

Try this:

(index=x  AND "32432" AND "client") OR (index=y AND "23232" AND "client") OR (index=z AND "2323" AND client")
| spath "EventStreamData.args.id"
| rename"EventStreamData.args.id" AS ID
| dedup ID
| spath "EventStreamData.eventName"
| rename "EventStreamData.eventName" AS "Event_Name" 
| timechart span=1h count(eval(Event_Name=="32432")) As x_count count(eval(Event_Name=="23232")) AS y_count count(eval(Event_Name=="2323")) AS z_count
| table _time, x_count, y_count, z_count
0 Karma

amdhindsa
New Member

This doesn't work because all the indexes doesn't have this field - "EventStreamData.args.id". If I add dedup then x and z counts comes as 0 and y has right count.

0 Karma

Vijeta
Influencer

Try below search, it should work.

(index=x “32432”) OR (index=y “23232”) OR (index=z “2323”) “client” | spath "EventStreamData.eventName" | rename "EventStreamData.eventName" as "Event_Name" |spath "EventStreamData.args.id" as "Id"| eval new_id= if(Event_Name==“2323”,Id,NULL)
 | timechart span=1h count(eval(Event_Name==“32432”)) as x_count count(eval(Event_Name==“23232”)) as y_count dc(eval(ISNOTNULL(new_Id )) as z_count
 | table _time, x_count, y_count, z_count
0 Karma

amdhindsa
New Member

(index=x “32432”) OR (index=y “23232”) OR (index=z “2323”) “client” | spath "EventStreamData.eventName" | rename "EventStreamData.eventName" as "Event_Name" |spath "EventStreamData.args.id" as "Id"| eval new_id= if(Event_Name==“2323”,Id,NULL)
| timechart span=1h count(eval(Event_Name==“32432”)) as x_count count(eval(Event_Name==“23232”)) as y_count dc(new_id) as z_count
| table _time, x_count, y_count, z_count

This worked. But dedup and dc doesn't give same numbers for some reason. Difference is not huge, it's something like dedup giving 1458 vs dc giving 1471.

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 ...