topic Re: How to combine two indexes with Stats? in Splunk Search

How to combine two indexes with Stats?

mattfunk20 — Wed, 30 Sep 2020 04:17:50 GMT

Following a super helpful thread here https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-join-append-or-use-of-subsearches.html
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!

Re: How to combine two indexes with Stats?

MuS — Thu, 20 Feb 2020 02:41:42 GMT

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

Re: How to combine two indexes with Stats?

mattfunk20 — Fri, 21 Feb 2020 17:45:26 GMT

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