Splunk Search

Aligning resulsts after a transaction and lookup with multiple values

Path Finder

I am having a very difficult time trying to align fields after a transaction command that is using a lookup with multiple values.
I have a search that uses a transaction command based off an IP address and looks for more than one unique ID in the transaction. I then do a lookup against the unique ID to pull back additional fields like first name, last name, email, etc.

The result set I am trying to get should look something like this.

 IP unique_id   firstname   lastname    email
IP_1    id_1    id_1_firstname  id_1_lastname   id_1_email
    id_2    id_2_firstname  id_2_lastname   id_2_email
    id_3    id_3_firstname  id_3_lastname   id_3_email

What I am getting is the following where the fields in the rows do not match up to the corresponding uniq_id.

IP  unique_id   firstname   lastname    email
IP_1    id_1    id_2_firstname  id_3_lastname   id_1_email
    id_2    id_3_firstname  id_1_lastname   id_3_email
    id_3    id_1_firstname  id_2_lastname   id_2_email

I have tried using table, stats, and stats using the values function but cannot seem to get it correct.
Here is an example of what I have tried.
search … | transaction maxspan=6h ip keepevicted=t | where mvcount(unique_id)>1 | lookup local=t userlookup USER AS unique_id | stats values(unique_id) values(FIRSTNAME), values(LASTNAME), values(EMAIL) by ip

Any suggestions would be appreciated..

0 Karma


I would do this differently altogether...

| stats count by IP unique_id
| eventstats dc(unique_id) as idCount by IP
| where idCount > 1
| fields - idCount
| lookup local=t userlookup USER AS unique_id 

The transaction command can get quite slow over large data sets, and it creates multi-valued fields (which is what is causing you such grief). The solution avoids both of those problems.

0 Karma

Path Finder

Thank you, this search is way more efficient. I think I will wait until I can upgrade to 6.x and use some of the pivot features to to get the output looking the way I want. The current results now at least let me accurately export to Excel.

0 Karma
Get Updates on the Splunk Community!

Streamline Data Ingestion With Deployment Server Essentials

REGISTER NOW!Every day the list of sources Admins are responsible for gets bigger and bigger, often making the ...

Remediate Threats Faster and Simplify Investigations With Splunk Enterprise Security ...

REGISTER NOW!Join us for a Tech Talk around our latest release of Splunk Enterprise Security 7.2! We’ll walk ...

Introduction to Splunk AI

WATCH NOWHow are you using AI in Splunk? Whether you see AI as a threat or opportunity, AI is here to stay. ...