Is there an example/advice on some SPL for tracking down users running AD-HOC searches that search on all indexes?

[cass_major]

Hello,

Could anyone offer an example/advice on some SPL for tracking down users running AD-HOC searches that search on all indexes please?

Thank you

[isoutamo · Accepted solution]

Hi

you could try something like this

index=_audit action=search user=* search=*
| fields _time user action info search_type search_id search
| where (match(search,"index\s*=[\w\s,]*\*"))
| fillnull value="N/A" search_type
| stats max(_time) as _time values(user) as user values(action) as action values(info) as info values(search_type) as search_type first(search) as search by search_id
| where search_type IN ("ad hoc", "N/A")

In earlier versions search_type was always "ad hoc" for AD-HOC searches, but currently it seems to be sometimes also NULL. 

You should also check that "where (match(...." that it's regex match all those different possibilities how your users are writing index=*

r. Ismo

[hettervik]

I'm looking into finding ad-hoc searches as well.  I've found that simply searching for search_type=ad-hoc does not work, cause clearly this finds scheduled searches as well, I'm positive. I can e.g. see users with an exact same number of searches every hour throughout the night, day after day. Seems to me that Splunk has some weird way of defining "ad-hoc".

If anyone has found a more reliable way of finding real ad-hoc searches, let me know!

[cass_major]

Thank you isoutamo. Any advice how to drill down to a particular user with the SPL? I am trying to find anyone who isn't properly constraining their searches by index.

Cheers,

Cass

[isoutamo]

The above SPL shows to you all those ad-hoc queries (not saved searches like alerts etc.). You can see the users who are running those. Just limit searches by user etc. and you probably get what you are wanting.

One good tool is MC which helps you to find long running or other expensive queries.

There is also some apps on splunkbase and in GitHub which you could use for that:

• https://splunkbase.splunk.com/app/3541
• https://github.com/dpaper-splunk/public/tree/master/dashboards
• https://github.com/nicovdw/splunk_concurrency_helper
• https://github.com/silkyrich/cluster_health_tools/tree/master
• https://splunkbase.splunk.com/app/3796/

There are lot of other apps on net which you could try and get more information about what is happening on your environment.