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
SplunkTrust
SplunkTrust
|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"

View solution in original post

Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.