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..
I would do this differently altogether...
yoursearchhere | 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
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.
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.