Splunk Search

How to combine two indexes with Stats?

mattfunk20
Explorer

Following a super helpful thread here https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo...
But I've ran into an issue where when I start to use stats, I always drop one of my indexes, is it possible to use stats and still maintain both indexes, or at least merge the data prior to losing one of them?

(index=Index1 sourcetype=Type1) OR (index=Index2)
| fields field1 field 2 mac_address dest_mac
| eval mac_address=replace(mac_address,"\W","")
| eval mac_address=lower(mac_address)
| rex field=dest_nt_host "(?[^.]+)."
| eval dest_nt_host=lower(dest_nt_host)
| eval dest_mac=lower(dest_mac)
| stats values(index) as index values(field1) as field1 values(field2) as field 2 values(index) as index values(mac_address) by dest_mac
| table dest_mac mac_address field 1 field 2 index

essentially whatever state with the BY-clause is the index that's kept, but ideally I'd like to match on dest_mac and mac_address, while pulling field 1 from index 1, and field 2 from index 2
Without the by clause, my data essentially is appending without append, looking like...

field 1 dest_mac index 1

                                            field 2 mac_Address index 2

Thanks in advanced!

0 Karma
1 Solution

MuS
SplunkTrust
SplunkTrust

Hi mattfunk20,

you need to get the unique identifier from both indexes and use it in the stats by clause. I assume that dest_mac and mac_address are theses fields, so try something like this:

(index=Index1 sourcetype=Type1) OR (index=Index2)
| fields field1 field 2 mac_address dest_mac 
| eval mac_address=replace(mac_address,"\W","") 
| eval mac_address=lower(mac_address) 
| rex field=dest_nt_host "(?[^.]+)."
| eval dest_nt_host=lower(dest_nt_host) 
| eval dest_mac=lower(dest_mac)
| eval mac=case(isnotnull(dest_mac), dest_mac, isnotnull(mac_address), mac_address, 1=1, "unknown")
| stats values(index) as index values(field1) as field1 values(field2) as field 2 values(index) as index values(mac_address) by mac 
| table mac mac_address field 1 field 2 index

Might need some tweaking but I'm sure you get what I mean 😉

And thanks for finding my original post helpful 🙂

Hope this helps ...

cheers, MuS

View solution in original post

MuS
SplunkTrust
SplunkTrust

Hi mattfunk20,

you need to get the unique identifier from both indexes and use it in the stats by clause. I assume that dest_mac and mac_address are theses fields, so try something like this:

(index=Index1 sourcetype=Type1) OR (index=Index2)
| fields field1 field 2 mac_address dest_mac 
| eval mac_address=replace(mac_address,"\W","") 
| eval mac_address=lower(mac_address) 
| rex field=dest_nt_host "(?[^.]+)."
| eval dest_nt_host=lower(dest_nt_host) 
| eval dest_mac=lower(dest_mac)
| eval mac=case(isnotnull(dest_mac), dest_mac, isnotnull(mac_address), mac_address, 1=1, "unknown")
| stats values(index) as index values(field1) as field1 values(field2) as field 2 values(index) as index values(mac_address) by mac 
| table mac mac_address field 1 field 2 index

Might need some tweaking but I'm sure you get what I mean 😉

And thanks for finding my original post helpful 🙂

Hope this helps ...

cheers, MuS

mattfunk20
Explorer

Super cool to see the same person help me out again.
Thanks, worked quite well!

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...