How to match users in a lookup file to a field with two different types of values in events?

[jmaple]

I'm trying to create a report that details our VPN usage over the course of a month. I've got the base of the report completed and the last thing I'm trying to do is correlate login information with our Active Directory lookup file. The problem is depending on the method people use to log in, the Username value will differ. If they use RSA, their user ID is displayed. If they use PIV, either their common name is displayed or another link to their PIV card is displayed. Again, all of that falls under the Username field.

I have all the necessary information to match the users in the lookup file, but because all the Username information falls in the same field in the event, I'm having a hell of a time trying to figure out how I would match it to the lookup for each circumstance.

base search
|eval logon = if(group="piv_group", "PIV", "RSA") 
|eval piv=if(like(Username, "%@pivcard.com"), Username, "") 
|lookup ad_users identity as Username, PIVAddress as piv
|eval Nickname=last.", ".first 
|eval email=lower(email) 
|table start Username logon duration employeeType division Nickname email 
|rename duration as "Total Session Time" start as "Date" Nickname as "Account Name" logon as "Logon Type" division as Division email as Email Username as "Last Login Username" employeeType as "Employee Type"

For my lookup command, if I just map idendity to Username or PIVAddress to piv, I get the correct values from the lookup. I just haven't been able to get them both simultaneously.

[sundareshr]

Why not break it up into two lookup calls. Once for Username and then for piv

base search
 |eval logon = if(group="piv_group", "PIV", "RSA") 
 |eval piv=if(like(Username, "%@pivcard.com"), Username, "") 
 |lookup ad_users identity as Username OUTPUT last AS LUserName first AS FUserName
 |lookup ad_users PIVAddress as piv last AS LPIVAddress first AS FPIVAddress
 | eval last=coalesce(LUserName, LPIVAddress)
 | eval first=coalesce(FUserName, FPIVAddress)
 |eval Nickname=last.", ".first 
 |eval email=lower(email) 
 |table start Username logon duration employeeType division Nickname email 
 |rename duration as "Total Session Time" start as "Date" Nickname as "Account Name" logon as "Logon Type" division as Division email as Email Username as "Last Login Username" employeeType as "Employee Type"