Splunk Search

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

splunkThreatHun
Engager

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.

1 Solution

DalJeanis
Legend

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

View solution in original post

chicoocholoco
New Member

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

0 Karma

DalJeanis
Legend

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

chicoocholoco
New Member

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

0 Karma

anubhp
New Member

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?

0 Karma

kyaparla
Path Finder

You can try something like this.

index=dnslogs sourcetype=ptr_data [search firstquery | stats count by dest | fields dest | rename dest as dns_name | format] | stats count by dns_name,query

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

0 Karma
Get Updates on the Splunk Community!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...