Splunk Search

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

bkirk
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

somesoni2
SplunkTrust
SplunkTrust

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

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

bkirk
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

bkirk
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...