Splunk search results convert a particular lookup matching record from many as a column

[vgoli]

Hi,

I am new to Splunk and looking for some help. I am trying to merge my lookup file data with search results and rearrange them for better usage

My search results are

User      Accessed application                                   Count

A            Prog 1                                                            10

A            Prog 2                                                            6

A            Prog 3                                                            8

B            Prog 2                                                            4

B            Prog 4                                                            6

And my lookup file data is

Accessed application      Auth_object                                  

Prog 1                               Auth object 1                                                                            

Prog 1                               Auth object 2                                                                           

Prog 1                               Auth object 3                                                                           

Prog 1                                common_auth_object

Prog 2                               Auth object 1                                                                            

Prog 2                               Auth object 4                                                                           

Prog 2                                common_auth_object

Prog 3                                Auth object 1

And my expected results are

User      Accessed application      Auth_object              part_of_common_auth_object                Count

A            Prog 1                               Auth object 1                                  x                                         10

A            Prog 1                               Auth object 2                                 x                                         10

A            Prog 1                               Auth object 3                                 x                                         10

A            Prog 3                                Auth object 1                                                                             8

instead of showing a seperate line for program that is part of common auth object, I want to show as X next to other auth object to indicate that the program is also part of common object.

Thanks

Vijay

[manjunathmeti]

hi Vijay,
You can try below query:

index=index 
| fields User, "Accessed application", Count 
| lookup filename.csv "Accessed application" OUTPUT Auth_object 
| eval part_of_common_auth_object=if(match(Auth_object, "common_auth_object"), "x", "") 
| mvexpand Auth_object 
| where NOT Auth_object="common_auth_object"
| fields User, "Accessed application", Auth_object, part_of_common_auth_object, Count

If this reply helps you, a like would be appreciated.