Splunk Search

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

jmaple
Communicator

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.

0 Karma

sundareshr
Legend

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"
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...