Splunk Search

How to search for any account lockout events, then filter by Active Directory group membership?

jganger
Explorer

I found another question on this same subject, but haven't found an answer. For instance, I'm trying to search for any account lockout events with index=domain-infrastructure EventCode=4740, and then I'd like to filter based on membership in a particular Active Directory group. Is this possible?

0 Karma

javiergn
SplunkTrust
SplunkTrust

I'll tell you what I did to solve this problem, although it is not trivial:

  • I've got a daily job in PowerShell that reads all the groups and theirs members into an SQL database. Your can use the ActiveDirectory module
  • The same job calculates the effective membership of any given group before inserting it into SQL. That includes nested groups, etc. This is needed in order to end up with a basic table in SQL with just two fields: User, Group. For instance:

    User1, Group1
    User1, Group2
    User1, Group3
    User2, Group1
    User3, Group3
    ...

  • Create a DB lookup in Splunk that points to the table above and returns for any given user, all the groups this user is a member of

  • Run your search and then pass this to your lookup in order to enrich every event with group information. It's a one to many map so you should expect more events than before

  • Now you can filter by group

Keep in mind you can probably do the same outside SQL simply by using a CSV file. The only reason I use SQL is because I use the same table in lots of places.

Thanks,
J

0 Karma

jganger
Explorer

Thanks for the response. That's disappointingly complex though.

Did you see the SA-ldapsearch app? I've only come across it briefly but it seems like it might be able to pull in the necessary info.

0 Karma

javiergn
SplunkTrust
SplunkTrust

You should be able to achieve something similar with the ldap app but I had so many problems (performance, errors, data integrity) when I used it 1 year ago that I gave up and turned to PowerShell + lookups.

Keep in mind PowerShell has modules for AD and if you use a CSV file it shouldn't be hard to implement at all.

0 Karma

jganger
Explorer

I can definitely handle (and have handled) the powershell export and CSV. Would you mind giving an example of what the lookup would look like in Splunk? I'm having a failure of imagination on that part.

0 Karma

javiergn
SplunkTrust
SplunkTrust

Sure. It should be something like:

index=domain-infrastructure EventCode=4740
| lookup YOURLOOKUPNAME UserNameInEvent AS UserNameInCSV OUTPUT GroupNameInCSV AS GroupNameInEvent

Syntax:

lookup [local=<bool>] [update=<bool>] <lookup-table-name> ( <lookup-field> [AS <event-field>] )... [ OUTPUT | OUTPUTNEW (<lookup-destfield> [AS <event-destfield>] )... ]

More info here:
How to use lookups -> http://docs.splunk.com/Documentation/Splunk/6.3.2/SearchReference/Lookup
How to configure lookups from GUI -> http://docs.splunk.com/Documentation/Splunk/6.3.2/SearchTutorial/Usefieldlookups

0 Karma

javiergn
SplunkTrust
SplunkTrust

Try manually creating a small CSV first with a few users and groups and test it with your events.
All the lookup is doing is enriching your event log information by adding a group field to your events.

The CSV should use a relational schema with a 1 to many mapping (1 user, multiple groups, 1 row per unique User-Group combination):

USER, GROUP
User1, Group1
User1, Group2
User1, Group3
User2, Group1
User3, Group3
0 Karma

jganger
Explorer

Perfect! Thanks so much for your help with this. One error in your post though, the lookup should be:

lookup YOURLOOKUPNAME UserNameInCSV AS UserNameInEvent OUTPUT GroupNameInCSV AS GroupNameInEvent
0 Karma

jganger
Explorer

Ok, powershell is done, lookup works. If I'm running the powershell as a scheduled task is there a way to automatically update the lookup table in Splunk? I'd hate to have to manually update the file every time.

0 Karma

javiergn
SplunkTrust
SplunkTrust

Simply overwrite the csv file you are using in your lookup and Splunk will refresh its cache.

Make sure the operation is transactional, that is, create a temp file first and once you finish writing to it copy it to the destination directory

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...