Lookup Challenge

[gabarrygowin]

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

[richgalloway]

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

[richgalloway]

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

[gabarrygowin]

Yes it was.

[493669]

@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

[gabarrygowin]

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