Splunk Search

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

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

Revered Legend

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

Revered Legend

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

Path Finder

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

Revered Legend

I updated the answer as well to correct that.

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!