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?
I'll tell you what I did to solve this problem, although it is not trivial:
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
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.
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.
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.
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
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
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
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.
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