BASE_SEARCH
| rex field=dest_host "^(?<hostname>([a-z0-9\.\-]*\.)?(?<Domain>[a-z0-9\-]{2,}(?=\.[a-z\.]{3,})\.(?<tld>[a-z\.]{3,})))"
| stats count as Visited by hostname Domain tld
| eventstats sum(Visited) as Visited dc(hostname) as Subdomains by Domain tld
| dedup Visited Subdomains Domain tld
| table Subdomains Visited Domain
| append [search BASE_SEARCH
| rex field=dest_host "^(?<hostname>([a-z0-9\.\-]*\.)?(?<Domain>[a-z0-9\-]{2,}(?=\.[a-z\.]{3,})\.(?<tld>[a-z\.]{3,})))"
| stats count as Visited by hostname Domain tld
| eventstats sum(Visited) as Visited dc(hostname) as Subdomains by Domain tld
| dedup Visited Subdomains Domain tld
| stats sum(Visited) as Visited sum(Subdomains) as Subdomains by tld
| rename tld as Domain]
| sort 0 - Subdomains
You can see that I have basically the same search twice once on the getting me the totals for the domain and then again to append the totals for the TLD: Here is my results that I am trying to get:
Subdomains Visited Domain
13 18 com
3 3 googleapis.com
2 4 google.com
2 2 doubleverify.com
2 2 net
1 4 microsoft.com
1 1 evidon.com
1 1 gstatic.com
1 1 ivaws.com
1 1 krxd.net
1 1 live.net
1 1 outlook.com
1 1 yahoo.com
Here is another way to do it but I repeat all the TLD data for each domain so I don't like the output as much:
BASE_SEARCH
| rex field=dest_host "^(?<hostname>([a-z0-9\.\-]*\.)?(?<Domain>[a-z0-9\-]{2,}(?=\.[a-z\.]{3,})\.(?<tld>[a-z\.]{3,})))"
| stats sum(count) as Visited by hostname domain tld
| eventstats sum(Visited) as Vis1 dc(hostname) as Subdomains by domain
| eventstats sum(Visited) as Vis2 dc(hostname) as Sub2 by tld
| dedup Vis1 Subdomains domain tld
| rename Vis1 as Visited
| rename domain as Domain
| table Subdomains Visited Domain Sub2 Vis2 tld
Subdomains Visited Domain Sub2 Vis2 tld
3 3 googleapis.com 13 18 com
2 4 google.com 13 18 com
2 2 doubleverify.com 13 18 com
1 4 microsoft.com 13 18 com
1 1 evidon.com 13 18 com
1 1 gstatic.com 13 18 com
1 1 ivaws.com 13 18 com
1 1 krxd.net 2 2 net
1 1 live.net 2 2 net
1 1 outlook.com 13 18 com
1 1 yahoo.com 13 18 com
So basically I would like to have the results of the appended searches using only the single search like did with the second search if possible.
Thank you,
Brian
Give this a try.
BASE_SEARCH
| rex field=dest_host "^(?<hostname>([a-z0-9\.\-]*\.)?(?<Domain>[a-z0-9\-]{2,}(?=\.[a-z\.]{3,})\.(?<tld>[a-z\.]{3,})))"
| stats count as Visited by hostname Domain tld
| eventstats sum(Visited) as Visited dc(hostname) as Subdomains by Domain tld
| dedup Visited Subdomains Domain tld
| appendpipe [| stats sum(Visited) as Visited sum(Subdomains) as Subdomains by tld
| rename tld as Domain]
| table Subdomains Visited Domain
Give this a try.
BASE_SEARCH
| rex field=dest_host "^(?<hostname>([a-z0-9\.\-]*\.)?(?<Domain>[a-z0-9\-]{2,}(?=\.[a-z\.]{3,})\.(?<tld>[a-z\.]{3,})))"
| stats count as Visited by hostname Domain tld
| eventstats sum(Visited) as Visited dc(hostname) as Subdomains by Domain tld
| dedup Visited Subdomains Domain tld
| appendpipe [| stats sum(Visited) as Visited sum(Subdomains) as Subdomains by tld
| rename tld as Domain]
| table Subdomains Visited Domain
I opted for this thanks for the |appendpipe that got me to my final answer!
BASE_SEARCH
| rex field=dest_host "^(?<hostname>([a-z0-9\.\-]*\.)?(?<Domain>[a-z0-9\-]{2,}(?=\.[a-z\.]{3,})\.(?<tld>[a-z\.]{3,})))"
| stats sum(count) as Visited by hostname Domain tld
| eventstats sum(Visited) as Vis1 dc(hostname) as Subdomains by Domain
| eventstats sum(Visited) as Vis2 dc(hostname) as Sub2 by tld
| dedup Vis1 Subdomains Domain tld
| rename Vis1 as Visited
| appendpipe [| dedup Vis2 Sub2 tld
| rename tld as Domain
| rename Vis2 as Visited
| rename Sub2 as Subdomains]
| table Subdomains Visited Domain
| sort 0 - Subdomains
Thank you!
This was exactly what I wanted to do. Let me put a but out there 🙂
Some reason this is slower than my 2 searches, and the data is not the same for the same time period Top 3 results for yesterday the Domain results match but the TLD seem to not match but you definitely got me on the right track to simplifying my search and getting the output formatted:
Mine:
Subdomains Visited Domain
46678 19367036 com
8294 1574148 net
5554 177845 gstatic.com
Yours:
Subdomains Visited Domain
46724 19367128 com
8299 1574156 net
5554 177845 gstatic.com