Splunk Search
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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