Use subsearch results for dbquery

[hartfoml]

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$'"

[martin_mueller]

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.

[hartfoml]

Thanks Martin,

This is good stuff!!!

[hartfoml]

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.

[martin_mueller]

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.

[linu1988 · Accepted solution]

| dbquery "SQL" "SELECT IPAddressStr, NBName FROM Assets"|where [search sourcetype=ids sid=xxxx | dedup dst | table dst|rename dst as IPAddressStr]

this will work

[hartfoml]

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.

[hartfoml]

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

[martin_mueller]

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