Archive

Use subsearch results for dbquery

Motivator

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

0 Karma
1 Solution

Champion

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

this will work

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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.

Motivator

Thanks Martin,

This is good stuff!!!

0 Karma

Motivator

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.

0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma

Champion

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

this will work

View solution in original post

0 Karma

Motivator

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.

0 Karma

Motivator

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!