Stymied by subsearches

[awmorris]

I can run the following search with perfect results:

sourcetype="aws" varOutcome=Blocked|rex field=varDNS_Name "(?<varShortName>.*)."|dedup varShortName|table varShortName

I can then take the search results returned by the previous search (let's suppose one is "badsite.cn") and then search my internal DNS logs with a query like:

sourcetype=named badsite.cn

and this finds the internal DNS request that created the offending OPENDNS query. All of this works fine up to this point.

Now when I try to join them with a subsearch like this:

sourcetype=named [search sourcetype="aws" varOutcome=Blocked|rex field=varDNS_Name "(?<varShortName>.*)."|table varShortName|dedup varShortName]

I get zero results. What am I doing wrong? I've used this query structure before with no problems.

[awmorris]

Thanks guys. Unfortunately, I can't accept more than one answer- but your replies were spot on perfect.

[DalJeanis]

Easy - then you accept the one that you feel was most helpful or informative, and upvote the others.

[gcusello]

Hi awmorris,
to use a sub-search field in a full text search, you have to rename your field:

sourcetype=named 
[search 
   sourcetype="aws" varOutcome=Blocked
  | rex field=varDNS_Name "(?<varShortName>.*)."
  | dedup varShortName
  | rename varShortName AS query
  | fields query ]
| ...

Bye.
Giuseppe

[the_wolverine]

As others have mentioned, you'll have to know what the matching varShortName field is in the "sourcetype=named" set. If not, you can use the subsearch output as a string search by doing the following:

sourcetype=named [search sourcetype="aws" varOutcome=Blocked|rex field=varDNS_Name "(?<varShortName>.*)."| stats count by varShortName| fields - count | search ]

[woodcock]

It helps to know what magic the subsearch is doing and for this, you can use the format command. Let us see how it works. This will create some sample events for our subsearch fun:

|makeresults | eval varShortName="hostA.com hostB.com hostC.com hostD.com hostE.com"
| makemv varShortName
| mvexpand varShortName
| table varShortName

Now let's see what format does by adding this:

| format

This gives us a field called search like this:

( ( varShortName="hostA.com" ) OR ( varShortName="hostB.com" ) OR ( varShortName="hostC.com" ) OR ( varShortName="hostD.com" ) OR ( varShortName="hostE.com" ) )

The problem is now obvious: the fieldname varShortName should not be there so let's fix it. We can do it like this:

| return 99999 $varShortName

But you will notice that the entire string is not surrounded by an extra pair of parentheses which is dangerous depending on where you put it so I prefer something like this instead:

| format "(" "" "" "" "OR" ")"
| rex field=search mode=sed "s/varShortName=//g"

So our final solution is this:

sourcetype=named [search sourcetype="aws" varOutcome=Blocked|rex field=varDNS_Name "(?<varShortName>.*)." | table varShortName | dedup varShortName | format "(" "" "" "" "OR" ")" | rex field=search mode=sed "s/varShortName=//g"]

[cramasta · Accepted solution]

This search
sourcetype=named [search sourcetype="aws" varOutcome=Blocked|rex field=varDNS_Name "(?.*)."|table varShortName|dedup varShortName]

Ends up creating a search like this

sourcetype=named (varShortName=badsite1 OR varShortName=badsite2 OR varShortName=badsite1..)

So if in your sourcetype=named logs you dont have an extracted field called varShortName it will not find any matching events. You can rename the field in your subsearch to match the field as it is named in sourcetype=named

If you dont have a field extraction in sourcetype=named you can do something like this (returning only the first 100 values found)
sourcetype=named [search sourcetype="aws" varOutcome=Blocked|rex field=varDNS_Name "(?.*)."|stats count by varShortName | fields varShortName | return 100 $varShortName]

This will create a search like below where you are not looking for a key value pair and rather just the value...

sourcetype=named (badsite1 OR badsite2 OR badsite3..)