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