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')
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:

/// Utility queries to help access the application error log from SQL
Class 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 qHandle
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
}

/// Gets the next row, advancing to the next date if needed
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")) = "" {
        // Advance to the next date
        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) {
        // If we have a row for the current date, add it
        Set 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 advance
        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"
    // This seems to be necessary sometimes to have %OnClose run properly
    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:

/// 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! 😅