topic Re: How can I use the results of a search in a second search? in Splunk Search

How can I use the results of a search in a second search?

splunkThreatHun — Mon, 09 Oct 2017 23:40:20 GMT

I'm running a query which returns destination ip address of external traffic of a user in one column something like that:

----dest-----
1.2.3.4
23.23.23.23
45.45.45.45
67.67.67.67
4.3.2.1

Which means the user access the IP addresses listed above (last 15 minutes for example)

I'm also running a different query to make DNS ptr record check of a given ip address something like that:

index=dnslogs sourcetype=ptr_data dns_name="1.2.3.4" | stats values(query)

and it returns something like that:

----------DNS Value---------
 google.com

What I'm trying to do is:
AFTER my first query return this one column result, I want to iterate every value of the each raw into my next query to look for DNS ptr records and then merge the result of queries.
What I mean is I need to run every IP address in this query and then merge the results. In my case:

my ip addresess are 1.2.3.4, 23.23.23.23, 45.45.45.45, 67.67.67.67, 4.3.2.1
x.x.x.x is should be replaced by these ip addresses for each iteration
index=dnslogs sourcetype=ptr_data dns_name="x.x.x.x" | stats values(query)

And my expected results should look something like that:

----dest------                ---------DNS Value---------
1.2.3.4                              google.com
23.23.23.23                          NULL
45.45.45.45                          whatsapp.com
                                     web.whatsapp.com
67.67.67.67                          twitter.com
4.3.2.1                              NULL

The result above shows that some of query result return NULL, some of them return multiple values like 45.45.45.45 some of them only return one value.
Is there any way to do that something like that? These two query are completely different query. I looked at sub-search but it didn't work me or I couldn't do this.

Re: How can I use the results of a search in a second search?

kyaparla — Tue, 29 Sep 2020 16:12:52 GMT

You can try something like this.

OR
(using dnslookup lookup)
firstquery | stats count by dest | lookup dnslookup clientip as dest

Re: How can I use the results of a search in a second search?

DalJeanis — Tue, 10 Oct 2017 04:33:58 GMT

First, run this so you understand what is going on...

 your first query | stats count by dest | fields dest | rename dest as dns_name | format

That will return a single field called "search", with a value that looks like this....

( ( dns_name="1.2.3.4" ) OR ( dns_name="23.23.23.23" ) OR ... )

When you put that search inside brackets, it will be run first as a subsearch, and the output of the field search will be dropped into the main search just the way you read it above. The format at the end is implicit, so you don't actually need to have it there unless you are using some of its options.

So this...

index=dnslogs sourcetype=ptr_data [your first query | stats count by dest | fields dest | rename dest as dns_name ]

...is the same as this...

index=dnslogs sourcetype=ptr_data   ( ( dns_name="1.2.3.4" ) OR ( dns_name="23.23.23.23" ) OR ... )

... and then you run it into this...

| stats values(query) by dns_name

...so the whole thing is like this...

index=dnslogs sourcetype=ptr_data 
    [your first query | stats count by dest | fields dest | rename dest as dns_name ]
| stats values(query) by dns_name

Re: How can I use the results of a search in a second search?

chicoocholoco — Wed, 30 Sep 2020 04:50:55 GMT

i believe you have to add the word search itself inside the bracket so it should look like this

index=dnslogs sourcetype=ptr_data
[search your first query | stats count by dest | fields dest | rename dest as dns_name ]
| stats values(query) by dns_name

Re: How can I use the results of a search in a second search?

chicoocholoco — Wed, 30 Sep 2020 04:50:57 GMT

from my understanding, I believe you have to add search inside the bracket something like this:

index=dnslogs sourcetype=ptr_data
[search your first query | stats count by dest | fields dest | rename dest as dns_name ]
| stats values(query) by dns_name

Re: How can I use the results of a search in a second search?

anubhp — Thu, 02 Apr 2020 07:56:02 GMT

This is not working for me. It was working fine until yesterday, but suddenly stopped working Please find my query below

sourcetype="ms:o365:reporting:messagetrace" SenderAddress=@company.com Status= [search index=notable source="Threat - Detect Spam Email - Rule" | stats count by Subject | fields Subject] | stats dc(RecipientAddress) as recipientcount count by Subject SenderAddress

Any idea?