Can someone help me with a search that parses through two lookup tables?

[ajith_sukumaran]

Hi,

I have two lookup tables

lookup1:

RealName,      username

Smith, J ( LDN), smithj
Andy, H (LDN),andyh
Tan, Y ( JPN), tany
Jiang, T ( JPN), jiangt

lookup2:

Group,            Members

admin, CN=Smith, J ( LDN),OU=Users,OU=LDN CN=Andy, H ( LDN),OU=Users,OU=LDN 
access, CN=Tan,Y ( JPN),OU=Users,OU=JPN CN=Jiang, T ( JPN),OU=Users,OU=JPN 

My original search will output a username ( eg: "smithj"). I need to parse this username into lookup 1 to get the RealName. Then parse that RealName into lookup 2 to search it under "Members" field to get the Group value.

Eg: If my original search returns "joesmith" and parse it into lookup1, I need "admin" from lookup 2.

Could someone help with this search?

[FrankVl]

I'd strongly suggest flattening that second lookup into something with a single member per line and the CN in a separate column. Then you can just complete this with two consecutive lookup commands, one to translate username into real name (aka CN) and one to lookup the group based on CN.

And flattening that lookup can be done like this:

|  makeresults 
|  eval Group="admin"
|  eval Members="CN=Smith, J ( LDN),OU=Users,OU=LDN CN=Andy, H ( LDN),OU=Users,OU=LDN"
|  rex field=Members max_match=0 "(?<Member>CN=[^\)]+\)(?:,OU=\w+)+)"
|  mvexpand Member
|  rex field=Member max_match=0 "(?<CN>CN=[^\)]+\))"
|  table Group,Member,CN

First 3 lines are just to generate the example, you can replace these by an inputlookup to open the respective lookup. Add an outputlookup command at the end to write the results back to the lookup.

Note: you might need more elaborate regex depending on the actual data in the Members column (e.g. if the real name can also contain parentheses).

[ajith_sukumaran]

I vaguely remember completing this work by doing something like below

| inputlookup lookup2.csv| search [ | inputlookup lookup1.csv | search username="smithj" | table RealName| eval RealName="".RealName."" | rename RealName as Members] | table Group

[ajith_sukumaran]

I vaguely remember completing this work by doing something like below

| inputlookup lookup2.csv| search [ | inputlookup lookup1.csv | search username="smithj" | table RealName| eval RealName="".RealName."" | rename RealName as Members] | table Group

[joebisesi]

To start with your lookup table shows 'Realname,Username' but the data shows Smith, J ( LDN), smithj. So if you ran | inputlookup lookup1 your table is going to look similar to this:
Realname username
Smith J(LDN)
Andy H(LDN)
Tan Y(JPN)
Same goes for lookup2

Keep in mind a lookup table is a csv file which is 'comma' delimited. Meaning at every "," it sees, it interprets it as a break in the data.

You will have to either add a field for the first initial and location or combine them, if you need to maintain the two fields you currently have. The same will apply to lookup2.

As far as the search itself, I would try running an eval statement against both lookups. I don't have time to test the eval statements currently. I will try to do that later today, and add to this with the results.

[ajith_sukumaran]

Thanks for the reply. Please note the comma in the csv is not an issue at all. All the fields are populated with the correct values as required. Both the lookups are manually created considering the comma values in the fields.
Eg:

If I run "| inputlookup lookup1.csv | fields realname " it returns the below.

Smith, J ( LDN)
Andy, H (LDN)
Tan, Y ( JPN)
Jiang, T ( JPN)

Same with lookup 2

| inputlookup lookup2.csv | fields Members

CN=Smith, J ( LDN),OU=Users,OU=LDN CN=Andy, H ( LDN),OU=Users,OU=LDN
CN=Tan,Y ( JPN),OU=Users,OU=JPN CN=Jiang, T ( JPN),OU=Users,OU=JPN