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
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