Let's start with a simple motivating question: over the past 14 days, what are my most common errors in the Application Error Log?
Answering this through the management portal or terminal is an annoying manual process - we should just be able to use SQL. Fortunately, there are a few class queries to help with this in the SYS.ApplicationError class in the %SYS namespace. You can answer the question for a single date with something like:
select "Error message",count(*)
from SYS.ApplicationError_ErrorList('CCR','12/16/2024')
group by "Error message"
order by 2 desc
Unfortunately, the structure of the class queries runs into the same overall structural limitations as the management portal pages; the ErrorList query needs a namespace and date. Surely there's a better approach than 14 union'ed calls to that class query for different dates, right? On some level, this is a real question; if there is a good way to do this through plain old SQL and I'm just missing it, please tell me!
The logical step is to write our own custom class query. This involves adding a Query class member (say <QueryName>) and implementing methods named <QueryName>Execute, <QueryName>Fetch, and <QueryName>Close. In general, Execute sets up the context for the class query and does any initial work, maintaining any state in qHandle; Fetch gets a single row and says whether we've found all the rows or not; and Close does any final cleanup. For example, if the implementation of Execute/Fetch uses a process-private global, Close might kill it.
Don't forget the magical [ SqlProc ] flag on the Query member that allows it to be called as a TVF (table-valued function) from other SQL queries!
Here's the full working example:
Class AppS.Util.ApplicationErrorLog
{
Query All() As %Query(ROWSPEC = "Date:%Date,ErrorNumber:%Integer,ErrorMessage:%String,Username:%String") [ SqlProc ]
{
}
ClassMethod AllExecute(ByRef qHandle As %Binary) As %Status
{
Set ns = $Namespace
New $Namespace
Set $Namespace = "%SYS"
Set stmt = ##class(%SQL.Statement).%New()
Set stmt.%SelectMode = 0
Set result = ##class(%SQL.Statement).%ExecDirect(stmt,"select %DLIST(""Date"") ""Dates"" from SYS.ApplicationError_DateList(?)",ns)
$$$ThrowSQLIfError(result.%SQLCODE,result.%Message)
If 'result.%Next(.sc) {
Return sc
}
Set qHandle("list") = result.%Get("Dates")
Set qHandle("pointer") = 0
Quit $$$OK
}
ClassMethod AllFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = AllExecute ]
{
Set sc = $$$OK
Set ns = $Namespace
New $Namespace
Set $Namespace = "%SYS"
If $Get(qHandle("dateResult")) = "" {
Set pointer = qHandle("pointer")
If '$ListNext(qHandle("list"),pointer,oneDate) {
Set AtEnd = 1
Quit $$$OK
}
Set qHandle("pointer") = pointer
Set qHandle("currentDate") = oneDate
Set qHandle("dateResult") = ##class(%SQL.Statement).%ExecDirect(,"select * from SYS.ApplicationError_ErrorList(?,?)",ns,oneDate)
$$$ThrowSQLIfError(qHandle("dateResult").%SQLCODE,qHandle("dateResult").%Message)
}
If qHandle("dateResult").%Next(.sc) {
Set Row = $ListBuild(qHandle("currentDate"),qHandle("dateResult").%GetData(1),qHandle("dateResult").%GetData(2),qHandle("dateResult").%GetData(6))
} ElseIf $$$ISOK(sc) {
Set qHandle("dateResult") = ""
Set $Namespace = ns
Set sc = ..AllFetch(.qHandle,.Row,.AtEnd)
}
Quit sc
}
ClassMethod AllClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = AllExecute ]
{
New $Namespace
Set $Namespace = "%SYS"
Kill qHandle("dateResult")
Quit $$$OK
}
}
In this example, we start out in a user namespace but all the queries really run in %SYS. Execute gets a list of error dates for the current namespace and stashes it in qHandle. Fetch advances to the next date when appropriate, then returns the next error for the current date. And Close makes sure that the class query goes out of scope in %SYS, because I sometimes got errors if it didn't. This was a little surprising but kind of makes sense because the class query we're calling only exists in %SYS.
There's a lot of power in reusability of table-valued functions. For example, we can add another one in the same class:
Query ErrorCounts(Days As %Integer) As %SQLQuery(ROWSPEC = "Occurrences:%Integer,ErrorMessage:%String") [ SqlProc ]
{
SELECT COUNT(*) AS Occurrences, ErrorMessage
FROM AppS_Util.ApplicationErrorLog_All()
WHERE DATEDIFF(D,"Date",$h) <= :Days
GROUP BY ErrorMessage
ORDER BY Occurrences DESC
}
And now getting our most common application errors over the last 14 days is as simple as:
call AppS_Util.ApplicationErrorLog_ErrorCounts(14)
Now we just need to fix them all! 😅