topic Re: Lookup Challenge in Splunk Search

Lookup Challenge

gabarrygowin — Sun, 22 Mar 2020 03:58:35 GMT

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.

Lookup:

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

Re: Lookup Challenge

richgalloway — Sun, 22 Mar 2020 13:38:59 GMT

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

Re: Lookup Challenge

493669 — Sun, 22 Mar 2020 16:50:11 GMT

@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

Re: Lookup Challenge

gabarrygowin — Sun, 22 Mar 2020 21:20:39 GMT

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....

Thanks

Re: Lookup Challenge

gabarrygowin — Sun, 22 Mar 2020 21:22:20 GMT

Yes it was.

Re: Lookup Challenge

richgalloway — Mon, 23 Mar 2020 15:14:34 GMT

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