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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...