Splunk Search

combining stats question, can I do this as one search instead of appending 2?

Path Finder
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

0 Karma
1 Solution

Revered Legend

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

View solution in original post

0 Karma

Revered Legend

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

View solution in original post

0 Karma

Path Finder

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!

0 Karma

Path Finder

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 
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!