We have some SQL Jobs disabled. somehow the jobs are enabled.
Is there any way to find who and when the jobs are modified using SolarWinds DPA?
We have some SQL Jobs disabled. somehow the jobs are enabled.
Is there any way to find who and when the jobs are modified using SolarWinds DPA?
If you know what SQL statement or tables/columns is used for SQL Jobs admin, you could use the DPA "FindSQL" feature and filter SQL statements by portions of the text and what time, users, applications, or client hosts requested their execution.
However, DPA shouldn't be used as an auditing tool. While DPA collects statistics on queries every second, if the SQL Jobs modification took less than a second to run and it executed entirely in between one DPA collection cycle ended and before the next starts, there is a race condition where DPA could miss capturing that query instance.
You'd be better off scanning the DB's execution history for the SQL Jobs activity in question from msdb.dbo.sysjobhistory, msdb.dbo.sysjobs, msdb.dbo.sysjobactivity etc.
If you wish to be alerted in the future when/if it happens again, you could create a DPA custom alert that queries those views/stored procs and upon matching the criteria you are looking for, send an alert notification to email, Teams, Slack etc.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 200,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.