How to edit my search to get the total count of two fields in two different indexes when using the "dedup" command?

[micave]

I have two indexes that I need to search. For the first index, I need to count the total from a certain field however I need to dedup this field first. I have the same dilemma with the second index. I need to count the total but have to dedup as well. For example:

index=MyIndex1 OR index=MyIndex2  | dedup MyIndex1Field | dedup MyIndex2Field | stats count MyIndexField1 as Total1, count MyIndexField2 as Total2 | eval CalcField=(Total1/Total2) 

There are some commands I will pipe in once I get this solved but for now just trying to figure this out. I keep getting issues when I dedup one or the other so the goal is to dedup both. Any help would be appreciated.

[gokadroid]

How about separating MyIndex1Field of MyIndex1 and MyIndex2Field of MyIndex2 for something like this and see if it works for you:

index=MyIndex1 | dedup MyIndex1Field | stats count as Total1
| append [ search index=MyIndex2 | dedup MyIndex2Field | stats count as Total2 ]
| eval CalcField=(Total1/Total2)

[micave]

Tried the other solution first and it worked for me. Interested in also trying this. Thanks!

[somesoni2 · Accepted solution]

Try like this

index=MyIndex1 OR index=MyIndex2 | eval commonfield=coalesce(MyIndex1Field ,MyIndex2Field) | dedup index commonfield | eval temp=1 | chart count(commonfield) over temp by index | fields - temp | rename MyIndex1 as Total1 Myindex2 as Total2   | eval CalcField=(Total1/Total2)