Splunk Search

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

micave
New Member

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.

0 Karma
1 Solution

somesoni2
Revered Legend

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)

View solution in original post

gokadroid
Motivator

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

micave
New Member

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

0 Karma

somesoni2
Revered Legend

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)
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...