topic Re: Find user that ran a specific dbquery in Splunk Search

Find user that ran a specific dbquery

jcrane — Sat, 28 Jun 2014 04:01:11 GMT

I need to find which user ran a specific dbquery such as 'select * from table1'. Can someone tell me how to search splunk for this?

Thanks,
j

Re: Find user that ran a specific dbquery

Ayn — Sat, 28 Jun 2014 07:42:53 GMT

Splunk keeps all issued searches in its _audit index, so you can look there. Something like this:

index=_audit action=search info=granted search="*select * from table1*"

Re: Find user that ran a specific dbquery

MythiliIyer — Mon, 28 Sep 2020 16:57:15 GMT

This works for me to find queries on any database in the instance. I'm sysadmin on the instance (check your priviledges):

SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.*
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.dbid = DB_ID('msdb')
ORDER BY deqs.last_execution_time DESC

This is the same answer that Aaron Bertrand provided but it wasn't placed in an answer.