Splunk Search

Lookup Challenge

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

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
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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, Karma would be appreciated.
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

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

gabarrygowin
Path Finder

Yes it was.

0 Karma

493669
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

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

Thanks

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...