Splunk Search
Highlighted

How to merge multiple lookup lines into one

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
Highlighted

Re: How to merge multiple lookup lines into one

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

Highlighted

Re: How to merge multiple lookup lines into one

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.

Highlighted

Re: How to merge multiple lookup lines into one

Influencer

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
Highlighted

Re: How to merge multiple lookup lines into one

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