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

[amdhindsa]

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!

[woodcock]

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

[amdhindsa]

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.

[Vijeta · Accepted solution]

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

[amdhindsa]

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