Searching 2 indexes comparing 2 fields

[MrGlass]

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

[gcusello]

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

[MrGlass]

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.

[gcusello]

Hi @MrGlass ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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

[yuanliu · Accepted solution]

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

[MrGlass]

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

[isoutamo]

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-joi...

[ITWhisperer]

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