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
Legend

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
Legend

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!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...