topic Re: Merging Results from 3 Searches in Splunk Search

Merging Results from 3 Searches

welkinson — Mon, 28 Sep 2020 10:08:38 GMT

Hi I have 3 searches from 3 different device, I would like to have 1 report which contains data from the the 3 devices into 1 line. I am tracking a user who plugs his pc to a switch which in turn asks the DHCP server to assign an IP address to him a role will then be assigned to him by an app server. I want to match the MAC address from logs of the switch to the Mac address from the logs in the DHCP then match the IP address from the DHCP logs to the IP Adress in the App server log. Is this possible? Thanks in advance!

Switch: Switch MAC Address & Local Port

DHCP: MAC Address & IP Address

App Server: IP Address & Role

Report will have :

MAC Address IP Address Role in 1 Line

Edit:
Here are the three searches:

host="10.21.10.23" | rex field=_raw "for client (..[)] on Interface [ ]" | eval switch_mac=switch_mac1.switch_mac2.switch_mac3 | stats count by switch_mac IPort

(host="10.21.10.8" OR host="10.21.10.7") "10.21.23" | rex field=_raw "IP address (?.) is assigned to (?.)[.] ([)]" | stats count by Mac_adr, IPadr

(host="10.21.10.3" OR host="10.21.10.4") "10.21.23" | rex field=_raw " on host (?.) changed from <(?.)> to <(?.*)>" | stats count by clientpc, FromRole, ToRole

Thank You

Re: Merging Results from 3 Searches

eelisio2 — Wed, 23 Nov 2011 12:59:15 GMT

Assuming you have the appropriate fields extracted, you should be able to use the transaction command:

sourcetype=Switch OR sourcetype=DHCP OR sourcetype=Appsvr | transaction MacAddress IPAddress | table MacAddress IPAddress Role

Re: Merging Results from 3 Searches

welkinson — Thu, 24 Nov 2011 14:58:25 GMT

Hi Thanks for your answer, what do you mean by appropriate fields extracted. Thanks!

Re: Merging Results from 3 Searches

eelisio2 — Thu, 24 Nov 2011 15:03:37 GMT

Your searches results depend on having certain fields (MacAddress, IPAddress, Role). Fields can be automatically extracted by Splunk at search time based on key-value pairs in the logs events. Or they can be extracted explicitly by editing props.conf (and transforms.conf if necessary).

Re: Merging Results from 3 Searches

eelisio2 — Mon, 28 Sep 2020 10:09:16 GMT

Assuming clientpc is an IPAddress, I would try the append command to gather the events from your 3 searches and then pipe to the transaction command to correlate. In order for the transaction command to correlate based on field names, you need to change "clientpc" to "IPadr" and also change "switch_mac" to "Mac_adr". (Make sure the spelling of the Ip address and Mac address fields match.) Include your 3 searches (without the stats commands) in the framework below:

"Your first search"

| append
[search "Your second search"]

| append
[search "Your third search"]

| transaction Mac_adr IPadr

Re: Merging Results from 3 Searches

welkinson — Tue, 29 Nov 2011 03:31:52 GMT

Yes,

Finally got it. Many many thanks!