How to pass a field value from one search to another search?

[hillsw19]

Hi All,

I'm extremely new to Splunk and have been tasked to do the following:

Perform a query against one host (Server123) to retrieve MAC addresses then preform a query on a second  host (Server456) using the MAC addresses from the first query. I know all the MAC address from query 1 will not be found, but for the MAC address that are found, the MAC address, (which field name is different), the User Name, Network Device Name, and the IP Address would be put into a table and used as a report.

I run the query and get the following error: ERROR in 'stats' command: The argument 'Calling_Station_ID=(Mac addr.) When I run the 1st query by its self I see that the MAC addr. in error is the 1st MAC addr. in the 1st row.

Code:

index=* host="Server456" | stats count by Calling_Station_ID, User_Name, DeviceName, IP [ search index=* host="Server123" "no free leases" | eval MAC address=suibstr(_raw,52,18) | stats count by MAC address | eval MAC address=replace (MAC address," : ", " - ") | fields MAC address | return Calling_Station_ID=MAC address MAC Address $Mac_address } | table Calling_Station_ID, User_Name, DeviceName, IP |

Results:

I'm looking for the query to use the MAC from the 1st query to search the 2nd query and if there's a match return the MAC (under the Calling_Station_ID field), the User_Name, DeviceName and IP

[inventsekar]

Your Search Query - 
index=* host="Server456" 
| stats count by Calling_Station_ID, User_Name, DeviceName, IP 
    [ search index=* host="Server123" "no free leases" 
    | eval MAC address=suibstr(_raw,52,18) 
    | stats count by MAC address 
    | eval MAC address=replace (MAC address," : ", " - ") 
    | fields MAC address 
    | return Calling_Station_ID=MAC address MAC Address $Mac_address } 
    | table Calling_Station_ID, User_Name, DeviceName, IP 
    |

Edited - version 1:
index=* host="Server456" 
    [ search index=* host="Server123" "no free leases" 
    | eval MAC address=suibstr(_raw,52,18) 
    | stats count by MAC address 
    | eval MAC address=replace (MAC address," : ", " - ") 
    | fields MAC address 
    | table Calling_Station_ID=MAC address MAC Address $Mac_address ] 
| stats count by Calling_Station_ID, User_Name, DeviceName, IP 
| table Calling_Station_ID, User_Name, DeviceName, IP

Hi @hillsw19 few basic editings were needed on your query, i have edited that. Pls check this. i have not understood your problem completely(so i dont know subsearch needed or not), so lets troubleshoot, one step at a time, pls run this above edited query and let us know the output, thanks. 

[hillsw19]

Appreciating your assistance inventsekar ,

The script is now running with no errors, but am not sure if it's pulling the MAC addresses from the 1st query, there's one known MAC in the 1st query that's not coming out in the results. Tried to do a little troubleshooting, but can't get the known MAC to show in results.

index=* host="Server456"
[ search index=* host="Server123" "no free leases"
| eval MAC address=substr(_raw,52,18) 
| stats count by MAC address
| eval MAC address=replace (MAC address," : ", " - ")
| fields MAC address
| return Calling_Station_ID=MAC address MAC Address $Mac_address ] 
| stats count by Calling_Station_ID, User_Name, DeviceName, IP
| table Calling_Station_ID, User_Name, DeviceName, IP

[isoutamo]

Hi
another option is first do a query for getting those IP:s and then pipe it to map which do second query for each IP.
https://docs.splunk.com/Documentation/Splunk/8.0.5/SearchReference/Map
r. Ismo

[hillsw19]

Hi @isoutamo ,

Thanks for your response and your assistance - appreciated!

Not familiar with the map command/feature, currently looking at the link you provided to see what it's all about.

Thanks again for the point in the right direction!