topic Re: Help with Auditing the Auditors? (How can i identify from a list what matches aren't met?) in Splunk Search

Help with Auditing the Auditors? (How can i identify from a list what matches aren't met?)

voninski — Sat, 24 Sep 2016 18:50:30 GMT

I have a requirement to check to see if our auditors have run specific dashboards every week. I would like to build a query that reports if they haven't checked their areas of responsibility.

I've already been able to extract a search that extracts applications, users, times and dashboards accessed by end users. I've created regex to already extract 2 values 1) dashDashboards that is the dashboard accessed and 2) dashApplication that names the application being used. The graphic below shows what it looks like and here is the query to produce that.

index=_internal sourcetype=splunkd_access dashDashboard!=_admin | eval Date=strftime(_time,"%m/%d/%Y")|eval Time=strftime(_time,"%H:%M") | eval dayOfWeek=strftime(_time,"%A")| table  Date Time dayOfWeek user dashApplication dashDashboard | rename dayOfWeek AS "Day of Week", AppUsed AS "Application", dashDashboard AS Dashboard, dashApplication AS Application,user AS User

Now I would like to build a lookup table to use as a source with user names and dashboards they are required to check. The idea here is to create a search that finds what dashboards they haven't checked. A way to 'audit the auditors'

auditor,dashboard
rich,home_status
rich,mail_delivery
veronica,temperature_sensors

The following query does show if they have checked one of their areas but i would ideally like to check (and for every week woudl be ideal) if they haven't done their weekly job.

index=_internal sourcetype=splunkd_access dashDashboard!=_admin | eval Date=strftime(_time,"%m/%d/%Y")|eval Time=strftime(_time,"%H:%M") | eval dayOfWeek=strftime(_time,"%A")| join user,dashDashboard [|inputlookup auditor.list | rename dashboard AS dashDashboard, auditor AS user]|table  Date Time dayOfWeek user dashApplication dashDashboard | rename dayOfWeek AS "Day of Week", AppUsed AS "Application", dashDashboard AS Dashboard, dashApplication AS Application,user AS User

How can i identify given a lookup source if they haven't done their responsibilities? Or if you have a better idea of how to accomplish this please let me know.

Thanks.

Re: Help with Auditing the Auditors? (How can i identify from a list what matches aren't met?)

sundareshr — Sat, 24 Sep 2016 19:53:55 GMT

Try this

| inputlookup auditor.list | search NOT [ search index=_internal sourcetype=splunkd_access dashDashboard!=_admin | fields dashDashboard | rename dashDashboard AS dashboard ]

*OR*

index=_internal sourcetype=splunkd_access dashDashboard!=_admin | append [| inputlookup auditors.csv | eval sourcetype=lookup | rename auditor as user ] | stats values(sourcetype) as st values(_time) as _time values(dashApplication) as dashApplication by user | where mvcount(st)<2 | eval Date=strftime(_time,"%m/%d/%Y")|eval Time=strftime(_time,"%H:%M") | eval dayOfWeek=strftime(_time,"%A")| table  Date Time dayOfWeek user dashApplication dashDashboard | rename dayOfWeek AS "Day of Week", AppUsed AS "Application", dashDashboard AS Dashboard, dashApplication AS Application,user AS User

Re: Help with Auditing the Auditors? (How can i identify from a list what matches aren't met?)

voninski — Sun, 25 Sep 2016 21:48:44 GMT

Thank you for the replies. I tried them both. On the first one it returns nothing. I understand the logic and it makes sense to me as an inverse. But maybe we have something off. In the second one its not giving me the dashboards so I added values(dashDashboard) AS dashDashboard but it returns events but no results in the tables. I know it isn't quite coming back right though from the events since I have rich monitoring mail_delivery and it is returning a completed event as one of its returns. This one really has me stumped.

Re: Help with Auditing the Auditors? (How can i identify from a list what matches aren't met?)

sundareshr — Mon, 26 Sep 2016 00:06:55 GMT

Try the first one again and click on the Job Inspector >> Inspect and look for litsearch (Ctrl+F litsearch). That should show something like this

| inputlookup auditor.list | search NOT  ((dashboard="somevalue) OR (dashboard="someothervalue"))