What SQL queries do you run to monitor your system?

[Ryan_Wakefield]

So I have been working through what SQL queries I would like to have executed and based on the results to potentially trigger alerts. However, I know that my list is probably small compared to others. So I come to you to see what other OnBase customers/admins run to monitor their system.

Here is a list of the ones that I have come up with so far.

1. Errored out Unity Scheduler Tasks
2. Workflow queues that have too many items that shouldn't.
3. Distribution Service backlog.
4. Unity Scheduler Tasks with "next run time" older than X [time].
5. Locks older than X [time].
6. Scan queues reaching their batch limit.

What others have you come up with to help monitor your system?

Thanks.

[Paolo_DiLernia]

@Ryan Wakefield I love the idea.  We do not use scripts to monitor.  Are your scripts available to share?  Thanks!

[James_Perry]

I use a combination of Scheduled Tasks, PowerShell, and SQL to check changed permissions for high-value accounts, check for duplicate IDs when users transfer departments, check for failed or inconsistent processes, check for workstation license changes and additions, etc. Some of these are just log while others send notifications to our ticket system to be resolved.

[Kevin_Granger]

Something that recently came up are duplicate emails coming into the system.

I am unclear as to why this happens other than retries due to something in the emails but looking at the email reveals no issues and neither does reprocessing. Thoughts on this issue anyone?

SELECT DISTINCT kt12.keyvaluechar Mail_Messsage_ID_KW12, count(kt12.keyvaluechar) dup_count,
trim(i.itemname) AS 'Document Name'
FROM hsi.itemdata i
left outer join hsi.keyxitem12 kx12 on i.itemnum = kx12.itemnum
left outer join hsi.keytable12 kt12 on kx12.keywordnum = kt12.keywordnum and kt12.keyvaluechar not like 'BATCH%' --Scnaed batches
left outer join hsi.keyitem293 ks293 on i.itemnum = ks293.itemnum --Attachment No
where itemtypegroupnum = 101 --Claims
and status<> 16 --0 – Indexed (available for retrieval) 1 – Awaiting Index 16 – Deleted
and ks293.keyvaluesmall=0
and i.itemdate > cast( '2022-01-01' as date)
group by kt12.keyvaluechar, trim(i.itemname)
having count(kt12.keyvaluechar) >1

[Dirk_Vanderbent]

I use a combination of Scheduled Tasks, PowerShell, and SQL to check changed permissions for high-value accounts, check for duplicate IDs when users transfer departments, check for failed or inconsistent processes, check for workstation license changes and additions, etc. Some of these are just log while others send notifications to our ticket system to be resolved. How are using PowerShell with OnBase?