I have two working Splunk queries as follows.
The first one takes in an IP Address and datetime and returns a Mac Address:
index=dhcp signature=DHCPACK dest_ip="192.0.0.0" latest="05/30/2018:00:00:00"| rename dest_mac as mac_address | table mac_address, _time | sort - _time | head 1 | fields mac_address
The second one calls a stored procedure in the database and passes in the mac address and a datetime and returns a machine id, which is a guid:
| dbxquery query="EXEC [dbo].[get_machines_by_mac_date] @mac_address = '11:22:33:44:55:66', @utc_date_time = '05/30/2018'" connection="database1"
What I need to do is combine these two into one query where the Mac Address found in the first is passed to the second one as the mac_address parameter. I've been working on this, and I think I'm pretty close, but its just not working right, here is the combined query I have:
| dbxquery query="EXEC [dbo].[get_machines_by_mac_date] @mac_address = [index=dhcp signature=DHCPACK dest_ip="192.0.0.0" latest="05/30/2018:00:00:00"| rename dest_mac as mac_address | table _time, mac_address | sort - _time | head 1 | return mac_address], @utc_date_time = '05/30/2018'" connection="database1"
I've read that the inner query (inside the square brackets) gets executed first, so I'm trying to supply the mac_address parameter in the outer query with the results of the inner query.
I keep getting an error back that the mac_address is too long, that the maximum length in the database is 128. I'm pretty sure this means that the inner query isn't working and it is trying to send the entire literal text string within the square brackets to the stored procedure.
Here is the error I get back when I try to run the query:
com.microsoft.sqlserver.jdbc.SQLServerException: The identifier that starts with 'index=dhcp signature=DHCPACK dest_ip=192.0.0.0 latest=05/30/2018:00:00:00| rename dest_mac as mac_address | table _time, ma' is too long. Maximum length is 128.
I'm new at using Splunk and I would appreciate any help that can be provided!
I was able to get help from Splunk, and I was going about the query incorrectly. Instead of using the square brackets as a subsearch, I needed to map the dest_mac parameter from query 1 into query 2, here is the working combined query:
index=dhcp signature=DHCPACK dest_ip=192.0.0.0 latest=05/30/2018:00:00:00
| table dest_mac
| sort- _time | head 1 | map search=\"
| dbxquery procedure=\\\"{{call get_machines_by_mac_date(?,?)}}\\\"
connection=\"database1\" params=\\\"\\\"$dest_mac$\\\", 05/30/2018\\\"\"