topic Re: Searching 2 indexes comparing 2 fields in Splunk Search

Searching 2 indexes comparing 2 fields

MrGlass — Thu, 17 Apr 2025 15:07:45 GMT

I am trying to locate some data between two indexes, the common items are the src_interface and the network device name, but the data gets jumbled up when searching over longer periods of time. This is what I am using now.

index=network "arp-inspection" OR "packets received"
| rename mnemonic as Port_Status
| rename Network_Device TO "NetworkDeviceName"
| rename src_interface TO "src_int"
| join type=inner "NetworkDeviceName" , "src_int"
[ search index=cisco_ise sourcetype=cisco:ise:syslog User_Name="host/*"]
| table device_time, NetworkDeviceName, User_Name, src_int, src_ip, src_mac, message_text, Location, Port_Status

Re: Searching 2 indexes comparing 2 fields

ITWhisperer — Thu, 17 Apr 2025 15:40:57 GMT

Try to avoid using join - I suspect "data gets jumbled up when searching over longer periods of time" (not very precise terminology) is because subsearches (as used by join) are silently truncated at 50,000 events, so you join may not have all the events available that you are expecting (when you have extended periods of time). Try something along these lines:

(index=network "arp-inspection" OR "packets received") OR (index=cisco_ise sourcetype=cisco:ise:syslog User_Name="host/*") | rename mnemonic as Port_Status | rename Network_Device as "NetworkDeviceName" | rename src_interface as "src_int" | stats values(device_time) as device_time, values(User_Name) as User_Name, values(src_ip) as src_ip, values(src_mac) as src_mac, values(message_text) as message_text, values(Location) as Location, values(Port_Status) as Port_Status by NetworkDeviceName, src_int

or perhaps:

(index=network "arp-inspection" OR "packets received") OR (index=cisco_ise sourcetype=cisco:ise:syslog User_Name="host/*") | eval Port_Status=coalesce(Port_Status, mnemonic) | eval NetworkDeviceName=coalesce(NetworkDeviceName, Network_Device) | eval src_int=coalesce(src_int, src_interface) | stats values(device_time) as device_time, values(User_Name) as User_Name, values(src_ip) as src_ip, values(src_mac) as src_mac, values(message_text) as message_text, values(Location) as Location, values(Port_Status) as Port_Status by NetworkDeviceName, src_int

Re: Searching 2 indexes comparing 2 fields

gcusello — Thu, 17 Apr 2025 15:42:11 GMT

Hi @MrGlass ,

Splunk isn't a database, so the join command must be used only when there isn't any other solution and when you have few data, instead use stats, somerhing lie this:

(index=network "arp-inspection" OR "packets received") OR (index=cisco_ise sourcetype=cisco:ise:syslog User_Name="host/*") | eval NetworkDeviceName=coalece(NetworkDeviceName, Network_Device) | rename mnemonic AS Port_Status | rename src_interface AS "src_int" | stats earliest(device_time) AS device_time values(User_Name) AS User_Name values(src_ip) AS src_ip values(src_mac) AS src_mac values(message_text) AS message_text values(Location) AS Location values(Port_Status) AS Port_Status BY "NetworkDeviceName" , "src_int" | table device_time, NetworkDeviceName, User_Name, src_int, src_ip, src_mac, message_text, Location, Port_Status

Ciao.

Giuseppe

Re: Searching 2 indexes comparing 2 fields

MrGlass — Thu, 17 Apr 2025 17:29:33 GMT

This seems to work but does not return any of the fields from the Index=cisco_ise. these are the fields in reference to the data.

Index=network
src_interface
Network_Device
message_text

Index=cisco_ise
src_int
NetworkDeviceName
User_Name
Location
src_ip
src_mac

Thank you.

Re: Searching 2 indexes comparing 2 fields

isoutamo — Thu, 17 Apr 2025 17:55:46 GMT

You must add all needed fields in stats command if you want those to be present after its execution. Use values(a) as a values(b) as b like there is already used.

Here is one old post which explains who you should replace different joins in SPL. https://community.splunk.com/t5/Splunk-Search/What-is-the-relation-between-the-Splunk-inner-left-join-and-the/m-p/391288/thread-id/113948

Re: Searching 2 indexes comparing 2 fields

yuanliu — Thu, 17 Apr 2025 19:57:53 GMT

There's a small mistake in @gcusello's formula. src_interface and src_int should be coalesced (also a small spelling error), not renamed.

(index=network "arp-inspection" OR "packets received") OR (index=cisco_ise sourcetype=cisco:ise:syslog User_Name="host/*") | eval NetworkDeviceName=coalesce(NetworkDeviceName, Network_Device), src_int = coalesce(src_int, src_interface) | rename mnemonic AS Port_Status | stats earliest(device_time) AS device_time values(User_Name) AS User_Name values(src_ip) AS src_ip values(src_mac) AS src_mac values(message_text) AS message_text values(Location) AS Location values(Port_Status) AS Port_Status BY "NetworkDeviceName" , "src_int" | table device_time, NetworkDeviceName, User_Name, src_int, src_ip, src_mac, message_text, Location, Port_Status

Re: Searching 2 indexes comparing 2 fields

MrGlass — Fri, 18 Apr 2025 15:35:16 GMT

This seems to be working great, over 24 hours I get a few quirks but I can live with it. Thank you.

Re: Searching 2 indexes comparing 2 fields

gcusello — Sat, 19 Apr 2025 20:08:52 GMT

Hi @MrGlass ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉