Splunk Search

Lookup Challenge

Path Finder

Hi all,

With all this work from home, I'm now pulling logs from the VPN equipment. Now leadership is asking to equate the UserName to a business unit. Our Active Directory doesn't natively provide that but does give 'department'. I've built the lookup to equate departments to BusinessUnit, but can't figure out the missing piece.


Department  BusinessUnit
11-000-*    GA Legal
11-1*   GA Security
11-2*   GA HR
11-3*   GA Internal Audit
11-5*   GA Procurement
13-2*   GA ITS
14-*    GA Accounting
15-104* GA Publications
15-113-000  GA CFO
15-113-001  GA Intl Cntrl
15-180* GA Treasurer
15-250* GA Financial Planning
15-350* GA Treasury
16-1*   GA Facilities
18-4*   Fusion
19-*    EMS
20-505* Diazyme
51-001* GA Uranium Res. Co.
6*  ASI
7*  SI

My current search:

eventtype=cisco-ise-passed-authentication Location="Location#All Locations#US#CA#Poway" NAS_Port_Type="Virtual" | eval UserName=lower(UserName) | stats dc(UserName) by UserName | lookup adlookup sAMAccountName as UserName |  table UserName department | lookup BusinessUnitLookup.csv department as Department OUTPUTNEW BusinessUnit | stats dc(UserName) by BusinessUnit
0 Karma


To use asterisks in your lookup file, first create a lookup definition that points to your CSV. Go to Settings->Lookups->Lookup definitions and click New Lookup Definition.
Select the appropriate app, enter "BusinessUnitLookup" as the Name, and choose "BusinessUnitLookup.csv" from the "Lookup file" dropdown. Then check the Advanced box and enter "WILDCARD(Department)" in the "Match type" box. Click Save.
Change your query to use the lookup definition instead of the file.

...| lookup BusinessUnitLookup Department as department OUTPUTNEW BusinessUnit
If this reply helps you, an upvote would be appreciated.
0 Karma


What is the missing piece?
Does it have something to do with the asterisks in your lookup file?

If this reply helps you, an upvote would be appreciated.
0 Karma

Path Finder

Yes it was.

0 Karma

Super Champion

@gabarrygowin, lookup command is -

| lookup <lookup-table-name> <lookup-field1> AS <event-field1> OUTPUTNEW <lookup-destfield1> AS <event-destfield1>

After lookup command first field should be lookup field so in your case it would be-

...| lookup BusinessUnitLookup.csv Department as department OUTPUTNEW BusinessUnit
0 Karma

Path Finder

Rich gets the points! It was the asterisks. When I fully populated the department column it worked as stated.

Moral here: Wildcards can get wild....


0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!