Splunk Search

How to edit my search to pair values using "stats values()"?

janderson19
Path Finder

Hello,

I'm trying to pair values using stats values(), and I'm having some trouble.

I'm searching for blocked users in Websense, counting the number of times they are blocked, and I want to get their source IP and which destination host(s) was visited from each IP.

Currently my search is as follows:

index = websense | search action = blocked | stats count(username) as Count, values(src_host) as SourceIP stats values(dst_host) as DestinationHost by SourceIP  | sort -Count | head </code>

I'm currently getting data that shows

jsmith |5     |IP1     |website1
       |      |IP2     |website2
       |      |        |website3

I'd like to show, for example, that the user visited website1 from IP1 and IP2, website2 from IP1, and website 3 from IP2, like this, or similar

jsmith |5     |IP1     |website1
       |      |IP2     |website1
       |      |IP1     |website2
       |      |IP2     |website3
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try like this (double check the field names used)

 index = websense  action = blocked |eval temp=src_host."##".dst_host | stats count(username) as Count, values(temp) as temp by username  | sort -Count | head | rex field=temp "(?<SourceIP>.+)##(?<DestinationHost>.+)" | table username Count SourceIP DestinationHost

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Try like this (double check the field names used)

 index = websense  action = blocked |eval temp=src_host."##".dst_host | stats count(username) as Count, values(temp) as temp by username  | sort -Count | head | rex field=temp "(?<SourceIP>.+)##(?<DestinationHost>.+)" | table username Count SourceIP DestinationHost

janderson19
Path Finder

Thanks! I had to change "values(temp)temp" to "values(temp) as temp," but otherwise it worked perfectly.

somesoni2
SplunkTrust
SplunkTrust

I updated the answer as well to correct that.

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