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.
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
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
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
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
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?
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