How to write a search to find the count of Reported and Non-Reported Users with the total based on a lookup and bluecoat index?

[splunkrocks2014]

Hi.

I tried to get a summary of a covered or a non-covered users from a given lookup vs. an index, i.e. bluecoat. The lookup file contains all the users, i.e.

department      user
----------      ------
HR              U12345
HR              B12345
HR              X12345

Assuming there is only one user U12345 found from the bluecoat events, how can I generate a report to show numbers of users reported and non-reported based on a given lookup and bluecoat index?

The following result is expected from the search query:`

department     reported    non-reported   total
----------     --------    ------------   ------
HR             1           2              3

Is it possible to do it via Splunk search?

Thanks

[MuS]

Hi splunkrocks2014,

based on your question I created a lookup called users contains this list of users:

department,user
HR,U12345
HR,B12345
HR,X12345
HR,admin

I added the admin user so I can create and test this run everywhere search:

index=_internal user=* sourcetype=splunkd_ui_access earliest=-1min 
| lookup users user | inputlookup append=t users 
| streamstats sum(eval(if(isnotnull(sourcetype), 1,null()))) AS srcTcount count(eval(if(isnull(sourcetype), 1,null()))) AS looKcount by user
| dedup user
| stats max(srcTcount) AS reported sum(eval(if(isnull(sourcetype), looKcount, null()))) AS non-reported by department | eval Total = reported + 'non-reported'

The result looks like this:

So, what happens here? The first line is the base search to get events containing a user field from the index=_internal

 index=_internal user=* sourcetype=splunkd_ui_access earliest=-1min 

The second line uses the lookup users to lookup the department of any user found in the index and appends the complete lookup file users.csv to get all users available:

| lookup users user | inputlookup append=t users 

Here is the trick, we count the sourcetype for those users found in the index and if they don't have a sourcetype they were from the lookup:

| streamstats sum(eval(if(isnotnull(sourcetype), 1,null()))) AS srcTcount count(eval(if(isnull(sourcetype), 1,null()))) AS looKcount by user

Next will be a simple dedup on the user

| dedup user

followed by some stats-Fu to get the reported user from the index, the non-reported users from the lookup and a total of all users:

| stats max(srcTcount) AS reported sum(eval(if(isnull(sourcetype), looKcount, null()))) AS non-reported by department | eval Total = reported + 'non-reported'

Hope this makes sense and feel free to adapt to your needs ...

cheers, MuS