topic Re: Use subsearch results for dbquery in Splunk Search

Use subsearch results for dbquery

hartfoml — Thu, 17 Apr 2014 21:07:23 GMT

I have a subsearch that finds destination IP's like this

[search sourcetype=ids sid=xxxx | dedup dst | table dst]

I want to use my local vulnerability scanner database to id the system using the query with the "dst" as the "IPAddressStr"

| dbquery "SQL" "SELECT IPAddressStr, NBName FROM Assets where ipAddressStr LIKE '$dst$'"

Re: Use subsearch results for dbquery

linu1988 — Thu, 17 Apr 2014 21:44:48 GMT

this will work

Re: Use subsearch results for dbquery

martin_mueller — Fri, 18 Apr 2014 12:50:01 GMT

Depending on the size of the Assets table that'll be catastrophically slow.

Re: Use subsearch results for dbquery

martin_mueller — Fri, 18 Apr 2014 12:51:11 GMT

Have you considered switching to a database-backed lookup? Then you'd run your ids search and the lookup would add the additional fields to that search automagically.

Re: Use subsearch results for dbquery

hartfoml — Fri, 18 Apr 2014 13:56:54 GMT

The table is not that big and there is a join as well. the search only takes about 10 seconds Thanks Linu and Martin can you offer an example of the lookup suggestion above

Re: Use subsearch results for dbquery

hartfoml — Fri, 18 Apr 2014 13:56:56 GMT

Martin,

This is a great suggestion and while the answer below works, (thank you very much Linu it works great), it may not be as flexible as your suggestion.

Can you maybe give an example of the syntax as an answer rather than a comment so I can try it, and if it works better, then I can use it as the answer for this question?

Thanks Mike H.

Re: Use subsearch results for dbquery

martin_mueller — Fri, 18 Apr 2014 16:09:17 GMT

Set up a DB lookup as per this: http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Setupadatabaselookuptable
Run your search like this:

sourcetype=ids sid=xxxx | dedup dst | table dst | lookup local=1 your_table ipAddressStr as dst OUTPUT NBName

You can define that as an automatic lookup if you're not in a distributed environment.

Re: Use subsearch results for dbquery

hartfoml — Fri, 18 Apr 2014 16:51:48 GMT

Thanks Martin,

This is good stuff!!!

Re: Use subsearch results for dbquery

hartfoml — Fri, 18 Apr 2014 16:54:08 GMT

Martins Answer below is the better long term answer. setting up a lookup will allow me to use the lookup over and over again.

I chose this answer because it was easy to setup rather than modifying and implementing a database lookup.

I will use the answer below just not right now.