Query Aborted processes in LiquidOffice

We are running LiquidOffice 6.2.4. We have been having some network issues that sometimes cause our web service tasks to abort. We are working on a retry sequence to prevent this from becoming too big a problem.

However, we need to know when there are LO processes with aborted tasks so that we can take appropriate action (This can be done with the Management Console, but there is no alerting). I've been looking through the LiquidOffice SOAP API to try and find something, but have come up short so far. Once I have a viable solution we will be integrating this check into a SCOM alert.

Has anyone else done anything similar (ran a process search through SOAP)开发者_开发问答? Any guidance or hints would be greatly appreciated. We plan to implement the SOAP calls in C#.

Although I can see the SOAP queries and responses coming from the Management Console, I couldn't find the endpoint in order to connect to it myself. Overall this seemed like to big a hassle anyway. So then I came up with the idea of hitting the database directly. We have the LiquidOffice database being stored in a SQL 2005 instance, so I wrote the following query:

SELECT  P.PROC_ID AS [ID],
        REVERSE(LEFT(REVERSE(PDef.PROC_DFN_PATH),CHARINDEX('/',REVERSE(PDef.PROC_DFN_PATH))-1)) AS [Process],
        T.TASK_NAME AS [Task],
        DATEADD(millisecond, DATEDIFF(millisecond,GETUTCDATE(),GETDATE()), DATEADD(MINUTE, (P.START_DATE/60000), '1/1/1970')) AS [Started]
    FROM dbo.CS_PROCESSES AS P
        INNER JOIN dbo.CS_TASKS AS T
            ON P.PROC_ID = T.PROC_ID
        INNER JOIN dbo.CS_PROCESS_DFN AS PDef
            ON P.PROC_DFN_ID = PDef.PROC_DFN_ID
    WHERE T.[STATE] = 3

This returns ALL failed tasks and gives you the Process ID, Process Name, Failed Task Name, and the date/time the process was started. It's not perfect, but it works for us!