Table-valued function example: querying the Application Error Log

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')
groupby"Error message"orderby2desc

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:

/// Utility queries to help access the application error log from SQLClass AppS.Util.ApplicationErrorLog
{

/// Returns all application errors (all dates) from the application error log
Query All() As%Query(ROWSPEC = "Date:%Date,ErrorNumber:%Integer,ErrorMessage:%String,Username:%String") [ SqlProc ]
{
}

/// Gets a list of dates with errors and stashes it in qHandleClassMethod AllExecute(ByRef qHandle As%Binary) As%Status
{
    Set ns = $NamespaceNew$NamespaceSet$Namespace = "%SYS"Set stmt = ##class(%SQL.Statement).%New()
    Set stmt.%SelectMode = 0Set 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") = 0Quit$$$OK
}

/// Gets the next row, advancing to the next date if neededClassMethod AllFetch(ByRef qHandle As%Binary, ByRef Row As%List, ByRef AtEnd As%Integer = 0) As%Status [ PlaceAfter = AllExecute ]
{
    Set sc = $$$OKSet ns = $NamespaceNew$NamespaceSet$Namespace = "%SYS"If$Get(qHandle("dateResult")) = "" {
        // Advance to the next dateSet pointer = qHandle("pointer")
        If '$ListNext(qHandle("list"),pointer,oneDate) {
            Set AtEnd = 1Quit$$$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) {
        // If we have a row for the current date, add itSet Row = $ListBuild(qHandle("currentDate"),qHandle("dateResult").%GetData(1),qHandle("dateResult").%GetData(2),qHandle("dateResult").%GetData(6))
    } ElseIf$$$ISOK(sc) {
        // Otherwise, clear out the result set and call AllFetch to advanceSet qHandle("dateResult") = ""Set$Namespace = ns
        Set sc = ..AllFetch(.qHandle,.Row,.AtEnd)
    }
    Quit sc
}

ClassMethod AllClose(ByRef qHandle As%Binary) As%Status [ PlaceAfter = AllExecute ]
{
    New$NamespaceSet$Namespace = "%SYS"// This seems to be necessary sometimes to have %OnClose run properlyKill 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:

/// Gets a count by error message over the last <var>Days</var> days
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! 😅