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

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

jganger — Tue, 05 Jan 2016 14:28:22 GMT

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?

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

javiergn — Tue, 05 Jan 2016 20:12:33 GMT

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

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

jganger — Tue, 05 Jan 2016 20:19:15 GMT

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.

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

javiergn — Tue, 05 Jan 2016 20:27:54 GMT

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.

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

jganger — Tue, 05 Jan 2016 21:54:26 GMT

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.

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

javiergn — Tue, 05 Jan 2016 22:13:33 GMT

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

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

javiergn — Tue, 05 Jan 2016 22:17:36 GMT

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

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

jganger — Wed, 06 Jan 2016 14:29:25 GMT

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

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

jganger — Wed, 06 Jan 2016 14:46:33 GMT

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.

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

javiergn — Wed, 06 Jan 2016 15:08:01 GMT

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