Splunk Search

How to merge multiple lookup lines into one

mhale1982
Path Finder

I have a table with formatted something like this:

1 John, Smith, a123, superuser, blah
2 John, Smith, a123, audit user, blah
3 Sally, Smith, a234, regular user, blah
4 Andy, Smith, a345, audit user, blah
5 Andy, Smith, a345, log user, blah
6 Andy, Smith, a345, super user, blah

When you run the lookup for the user id (so like a123), you get both results on two lines within the same box in the table.

I want one single line that has the user type concatenated.

So instead of:
a123, super user
a123, audit user

I want:
a123, "super user, audit user"

Is that possible?

Labels (2)
0 Karma
1 Solution

anmolpatel
Builder

This:

| inputlookup test_multiValue.csv
| stats values(*) as * by Column3
| makemv delim="\n " Column4
| rex mode=sed field=Column4 "s/ /, /g"

View solution in original post

mhale1982
Path Finder

Anmolpatel got me what I needed specifically (since I was using a lookup table), but the other two are worthy of points and I'll mark them. Thank you all for the great answers!

0 Karma

manjunathmeti
Champion

hi @mhale1982,

If you need preserve other fields and to join values of other fields with different values in the lookup then try this:

| makeresults 
| eval _raw="first,last,user,role,blah_field
 John, Smith, a123, superuser, blah1
 John, Smith, a123, audit user, blah2
 Sally, Smith, a234, regular user, blah3
 Andy, Smith, a345, audit user, blah4
 Andy, Smith, a345, log user, blah5
 Andy, Smith, a345, super user, blah6" 
| multikv forceheader=1 
| fields - _raw, _time, linecount 
| stats delim="," values(role) as role, values(blah_field) as blah_field by user, first, last 
| nomv role 
| nomv blah_field

to4kawa
Ultra Champion
|makeresults
|eval _raw="No,First,Last,user,role,foo
1,John, Smith, a123, superuser, blah
2,John, Smith, a123, audit user, blah
3,Sally, Smith, a234, regular user, blah
4,Andy, Smith, a345, audit user, blah
5,Andy, Smith, a345, log user, blah
6,Andy, Smith, a345, super user, blah"
|multikv forceheader=1
| table No,First,Last,user,role,foo
| stats values(role) as role by user delim=","
| nomv role

try nomv or |eval role=mvjoin(role,",")
Both commands change multivalue to single.

anmolpatel
Builder

This:

| inputlookup test_multiValue.csv
| stats values(*) as * by Column3
| makemv delim="\n " Column4
| rex mode=sed field=Column4 "s/ /, /g"
Get Updates on the Splunk Community!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...