How do I use lookup to list all records on a specific field

[cbrownlee]

I have a sourcetype that contains application (SYS_ID) information. I also have a table that contains the responsible parties for the application. What I am wanting to do is run a search on the Managing Director of an application using a drop-down and put out a table with the corresponding data related to the director. For example Managing Director "John Public" has the following information.

sourcetype="netcool_prod_app_logs"  | eval EMP_Name_dd="John Public" |  lookup TechResources2   EMP_Name as EMP_Name_dd OUTPUT  SYS_ID, EMP_Name, EMP_LEVEL_TX, ROLE_EMP_NAME | table EMP_Name_dd  SYS_ID,EMP_Name, EMP_LEVEL_TX, ROLE_EMP_NAME

SYS_ID     EMP_Name     EMP_LEVEL_TX          ROLE_EMP_NAME
ADI     Bob Johnson  Executive Director Sr. Systems Dir. Responsible
ADI     Bob Johnson  Executive Director Vice President Responsible
ADI     Steve Gimble    Director              Systems Director Responsible
ADI     Dan Morgan      Manager            SYSID Administrator
ADI     Dan Morgan      Manager            Manager Responsible
ADI     John Public  Managing Director   Managing Director Responsible

I would like the information to come out in the form below, but all I get is the chosen Managing Director from the drop-down box information for all of the SYS_IDs and the seach results are repetative

EMP_Name_dd   SYS_ID   EMP_Name    EMP_LEVEL_TX      ROLE_EMP_NAME
John Public   ADI     Bob Johnson   Executive Director   Sr. Systems Dir. Responsible
                       Bob Johnson  Executive Director   Vice President Responsible
                       Steve Gimble   Director           Systems Director Responsible
                       Dan Morgan     Manager             SYSID Administrator
                       Dan Morgan     Manager             Manager Responsible
                       John Public  Managing Director   Managing Director Responsible

EMP_Name_dd   SYS_ID   EMP_Name       EMP_LEVEL_TX       ROLE_EMP_NAME
John Public   ADI      ALBERT GAMBALE   Managing Director   Area Content Lead Responsible
John Public   BFC      ALBERT GAMBALE   Managing Director   Area Content Lead Responsible
John Public   CAD      ALBERT GAMBALE   Managing Director   Area Content Lead Responsible
John Public   CAE     ALBERT GAMBALE    Managing Director   Area Content Lead Responsible
John Public   CBI     ALBERT GAMBALE    Managing Director   Area Content Lead Responsible
John Public   CLS     ALBERT GAMBALE    Managing Director   Area Content Lead Responsible
John Public   CMU     ALBERT GAMBALE    Managing Director   Area Content Lead Responsible
John Public   CNS     ALBERT GAMBALE    Managing Director   Area Content Lead Responsible
John Public   CTR     ALBERT GAMBALE    Managing Director   Area Content Lead Responsible
John Public   DBL     ALBERT GAMBALE    Managing Director   Area Content Lead Responsible
John Public   FAT     ALBERT GAMBALE    Managing Director   Area Content Lead Responsible
John Public   FDI     ALBERT GAMBALE    Managing Director   Area Content Lead Responsible
John Public   FFT     ALBERT GAMBALE    Managing Director   Area Content Lead Responsible
John Public   GBL     ALBERT GAMBALE    Managing Director   Area Content Lead Responsible
John Public   GLB     ALBERT GAMBALE    Managing Director   Area Content Lead Responsible
John Public   MAP     ALBERT GAMBALE    Managing Director   Area Content Lead Responsible
John Public   MBA     ALBERT GAMBALE    Managing Director   Area Content Lead Responsible
John Public   MBC     ALBERT GAMBALE    Managing Director   Area Content Lead Responsible

Help! Also, this is my first time with developing in splunk so my search may look very primitive.

[somesoni2 · Accepted solution]

You're using an eval command which is overwriting the field EMP_Name_dd with same value of John Public, hence the repetition.

If you want to correctly list all the EMP_Name_dd from logs with corresponding details from TechResources2 lookup, try this

sourcetype="netcool_prod_app_logs"  |  lookup TechResources2   EMP_Name as EMP_Name_dd OUTPUT  SYS_ID, EMP_Name, EMP_LEVEL_TX, ROLE_EMP_NAME | table EMP_Name_dd  SYS_ID,EMP_Name, EMP_LEVEL_TX, ROLE_EMP_NAME

If you've a form control (dropdown) using which you want to filter your logs based on some Managing director (I assume it's stored in field EMP_Name_dd, then try like this

sourcetype="netcool_prod_app_logs"  EMP_Name_dd="$YourDropdownTokenName$"|  lookup TechResources2   EMP_Name as EMP_Name_dd OUTPUT  SYS_ID, EMP_Name, EMP_LEVEL_TX, ROLE_EMP_NAME | table EMP_Name_dd  SYS_ID,EMP_Name, EMP_LEVEL_TX, ROLE_EMP_NAME

[maciep]

It might help to see the actual TechResources2 lookup file. It's a little hard to follow your examples to really understand why your lookup command is returning the results it is.