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

[jganger]

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?

[javiergn]

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

[jganger]

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.

[javiergn]

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.

[jganger]

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.

[javiergn]

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

[javiergn]

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

[jganger]

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

[jganger]

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.

[javiergn]

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