Splunk Search

How to combine two indexes with Stats?

Engager

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 macaddress destmac
| eval macaddress=replace(macaddress,"\W","")
| eval macaddress=lower(macaddress)
| rex field=destnthost "(?[^.]+)."
| eval destnthost=lower(destnthost)
| eval destmac=lower(destmac)
| stats values(index) as index values(field1) as field1 values(field2) as field 2 values(index) as index values(macaddress) by destmac
| table destmac macaddress 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 destmac and macaddress, 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

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

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

Engager

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