Internet usage report by Active Directory Group

[ucteam]

We are getting syslog data from our web proxy/firewall (Palo Alto) we would like to be able to generate internet usage reports based on users who are members of specific active directory groups or OUs.

For exmaple we would like our marketing manager to able to view an internet usage summary of the marketing department users.

Would this require an external lookup script to query AD for a list of all members of a group/OU and then load in as an array for a custom search? or is there some native syntax or plugin for Splunk which can achieve this?

Note. Username information is included in the syslog data recieved from the proxy/firewall we have no problems running general reports filtered on specific subnets or individual users.. its just generating reports which filter down to only inlcude the users of a specific AD group...

Any suggestions.. or pointers to the best/most efficient method would be appreciated.

[ftk]

I would do this by leveraging lookups. Specifically I would create a script that on a certain interval queries AD and outputs a CSV file mapping usernames to OU and/or department. This could be quite easily achieved either with VB Script or PowerShell.

After you have that script ready, just make sure it outputs to the appropriate lookup directory in your Splunk directory tree. You can then test it with a search like this:

sourcetype=my_firewall | lookup mylookup.csv username OUTPUTNEW department | where department="Marketing" | top URI by username

or something similar; obviously you will have to tune this to your specific sourcetypes, fieldnames, etc.

If this looks pretty good, define the lookup in props.conf and transforms.conf as per the documentation. Now you should be able to do searches like this:

sourcetype=my_firewall department="Marketing"