Dedup Lookup Table Field Results for Table

[bcarr12]

I am trying to find a way to clean up the display of one of my searches. I use a lookup table to input a field from one of my search results (column A in my lookup table), and then output to a table to make the data easy to read (column B and C give a more high level description of column A). The problem I am having is that my lookup table looks like this:

A B C
1 1 one
2 1 one
3 1 one
4 2 two
5 2 two
6 2 two

I am basically inputting the value from my search which corresponds to column A, into the lookup table and trying to return the value from column C (assume "one" is a description for something). In this case, it is intentional that multiple items in the table can roll up into having the same description. The problem is, when I use the table function, it leaves me with results like this:

http://i.imgur.com/936hiWF.png

What I am trying to point out is it shows the same "description" multiple times because there is more than one entry in the lookup table that corresponds with the field value that I put in my lookup table (this is expected because the field I am trying to display is a "rollup" general description that multiple things can fall under.) If I try to dedup on the field that I only want to see one description of, it shortens my results but leaves the multiple duplicate descriptions. Since multiple fields can roll up and have the same exact description, I'd like the table to only show it once rather than multiple times since that makes it more confusing. So it should actually be something like this:

Here is a basic representation of what my search looks like:
"searchtext" source="mylog.log" | transaction maxpause=1s a, b, c, d | search value=1 value=2 eventcount=2 | where mvcount(field)=1 | where b!=0 | eval lookupfield=logfield | lookup my_lookup_table lookupfield | table _time, field1, field2, field3, field4, field5, field6, lookuptablefield, field7, field8

Maybe I am just using the table and dedup commands out of order? Or is there another function that would do a better job at this? I'm kind of stuck on needing the table command because I want the fields to be in a specific order.

[tom_frotscher · Accepted solution]

Hmm, thats bad news 😞

But i have an other idea. Maybe you can use the stats command instead of the table command? If you use the values() function of stats, you get a distinct list:

| stats count | eval mvfield="a,a,a,b,b,b" | makemv mvfield delim="," | stats values(mvfield)

So in your case something like this:

mvcount(field)=1 | where b!=0 | eval lookupfield=logfield | lookup my_lookup_table lookupfield | stats  list(field1) list(field2) list(field3) list(field4) list(field5) list(field6) values(lookuptablefield) list(field7) list(field8) by _time

Maybe you can also use the stats command after your table command. Just try to play a little around with this suggestion.

Greetings

Tom

[bcarr12]

Tom, I owe you one! This is a perfect workaround and does exactly what I was trying to do. Really appreciate the help on this everyone.

[tom_frotscher]

Hey,

thats what splunk answers is for 🙂
If this was what you want, it would be nice if you transform the comment to an answer and mark your question as solved. So others with the same problem can also find the solution.

[ppablo]

Just converted it to an answer and accepted it 🙂 cheers to the weekend folks!

[wpreston]

Hopefully I'm understanding your question correctly. I can't see your imgur link because my company blocks but I'll offer up some suggestions. I would try using stats count and then use fields to get rid of the count field. You can optionally use table after stats if you need to for some reason.

"searchtext" source="mylog.log" | transaction maxpause=1s a, b, c, d | search value=1 value=2 eventcount=2 | where mvcount(field)=1 | where b!=0 | eval lookupfield=logfield | lookup my_lookup_table lookupfield | stats count by _time, field1, field2, field3, field4, field5, field6, lookuptablefield, field7, field8 | fields - count

Alternatively, if the lookuptablefield is being treated as a multivalue field, you can use mvexpand on the lookuptablefield, then use dedup on all of your other fields to get down to what you need:

 "searchtext" source="mylog.log" | transaction maxpause=1s a, b, c, d | search value=1 value=2 eventcount=2 | where mvcount(field)=1 | where b!=0 | eval lookupfield=logfield | lookup my_lookup_table lookupfield | mvexpand lookuptablefield | dedup _time field1 field2 field3 field4 field5 field6 field7 field8 | table _time, field1, field2, field3, field4, field5, field6, lookuptablefield, field7, field8 

[tom_frotscher]

Hi,

there is also a dedup for mvfields:

From the docs:

mvdedup(X)  

This function takes a multi-valued field X and returns a multi-valued field with its duplicate values removed.

... | eval s=mvdedup(mvfield)

So maybe something like this will work?

mvcount(field)=1 | where b!=0 | eval lookupfield=logfield | lookup my_lookup_table lookupfield | table _time, field1, field2, field3, field4, field5, field6, lookuptablefield, field7, field8 | eval lookuptablefield=mvdedup(lookuptablefield)

I created a small run everywhere example:

| stats count | eval mvfield="a,a,a,b,b,b" | makemv mvfield delim="," | eval mvfield=mvdedup(mvfield) | fields - count

Greetings Tom

[bcarr12]

Thank you Tom! The good news is, this actually sounds like it might be exactly what I am trying to do. The bad news is, I am currently stuck on Splunk 4.3.5 and this function does not appear to be available. Is there any other way I might be able to trick my results using another command or maybe an if statement?

[bcarr12]

Thanks again. How do I add a screenshot or picture if I don't have enough "karma"? The data I am working with is proprietary but I am thinking a blurred out picture of my table might give an idea for what I am trying to do.

[aljohnson_splun]

Upload it to imgur.com and post the link here (I'm not sure if it will let you embed).

[bcarr12]

Thanks for the tip. I have updated my question and posted a picture that gives a rough idea of what my table currently looks like.

[aljohnson_splun]

I suggest you add the additional information to your original question and delete the answer you submitted so that the question will remain unanswered and more visible. If you can post more of what your data looks like or format it a bit more, it may make answering easier.