This VBScript example shows how to use the "RunCustomSQL" function to obtain information from the tamper-evident MOVEit Automation task run, file activity and audit logs (i.e., the "taskruns", "stats" and "audit" database tables). Remember that this call can only be made by users in the MOVEit Log and/or MOVEit Admin groups.
See your MOVEit Automation manual ("Advanced Topics - Database - Schema" section) for information about the log table schemas.
Option Explicit ' Echo a message to the console Sub ShowMsg(MyText) Dim TheSplit TheSplit = Split(Now()," ") WScript.Echo TheSplit(1) & " - " & MyText End Sub ' Echo a message to the console along with last API error (if any) Sub ShowMsg2(MyText,oAPI) Dim TheSplit TheSplit = Split(Now()," ") if oAPI.GetErrorCode = 0 then WScript.Echo TheSplit(1) & " - " & MyText else WScript.Echo TheSplit(1) & " - " & MyText & " Error#" & oAPI.GetErrorCode & ": " & oAPI.GetErrorDescription end if End Sub '--- Sub Main ------------------------------------------------ Sub Main ShowMsg "runcustomsql.vbs running" Dim bOK Dim TaskHnd, WaitResult, TaskName, WaitSecs Dim oAPI Set oAPI = CreateObject("MICentralAPICOM.MICentralAPI") On Error Resume Next oAPI.SetHost("localhost") oAPI.SetUser("miadmin") oAPI.SetPassword("password") bOK = oAPI.Connect() if NOT bOK then ShowMsg2 "Could not sign on to MOVEit Automation!",oAPI Exit Sub end if ShowMsg("Running API version " & oAPI.GetAPIVersion() & _ " on Central version " & oAPI.GetCentralVersion() ) dim reply, SQLSelect, SpecialDelim, SpecialDelim2, HTab, FindPoint SpecialDelim = "Ç" ' XML version SpecialDelim2 = chr(199) ' VB version HTab = chr(9) ' See how many records are currently in the audit table SQLSelect = "SELECT COUNT(*) AS MyCount FROM audit;" ShowMsg "SQL: " & SQLSelect reply = oAPI.RunCustomSQL(SQLSelect, 1, 0, False, "ThisWillBeIgnored") If "" = reply OR oAPI.GetErrorCode > 0 Then ShowMsg2 "Failed to count records: ", oAPI Exit Sub Else ShowMsg "Current Audit Records: " & oAPI.GetValue(reply, "MyCount", 1) End If ' Compose a SQL query to read a few audit entries for a particular user SQLSelect = "SELECT ID,LogTime,Action,TargetType,TargetID,TargetName,Error,ErrorText " & _ " FROM audit " & _ " WHERE username='elroy_admin' " & _ " and action LIKE 'task_%' OR action LIKE 'cfgsec_%' " & _ " ORDER BY ID DESC;" ShowMsg "SQL: " & SQLSelect ' Run the query, limit it to 3 entries, and then format the results ' in "Excel-friendly" tab-delimited lines reply = oAPI.RunCustomSQL(SQLSelect, 3, 0, True, SpecialDelim) If "" = reply Then ShowMsg "Error: " & oAPI.GetErrorCode ShowMsg oAPI.GetErrorDescription Exit Sub Else ' Format for ready import into Excel (strip leading tabs and entry tags, tab-delimit) reply = Replace(reply, HTab & HTab & HTab & "<Record>", "") reply = Replace(reply, "</Record>", "") 'CRLF is already at the end of each line reply = Replace(reply, SpecialDelim2, HTab) FindPoint = InStr(reply, "<Records>") reply = Mid(reply, Findpoint + Len("<Records>")) FindPoint = InStr(reply, HTab & HTab & HTab & "<More>") reply = Mid(reply, 1, Findpoint) ShowMsg "Ready for Excel Reply: " & reply End If ' Run the query, limit it to 3 entries, and then dump the XML-formatted results reply = oAPI.RunCustomSQL(SQLSelect, 3, 0, False, "ThisWillBeIgnored") If "" = reply Then ShowMsg "Error: " & oAPI.GetErrorCode ShowMsg oAPI.GetErrorDescription Exit Sub Else reply = Replace(reply, HTab, " ") ShowMsg "Normal (XML) Reply: " & reply End If ShowMsg "runcustomsql.vbs complete" End Sub Main
Sample Output (With Proper Credentials)
6:29:52 - runcustomsql.vbs running 6:29:52 - Running API version 4.9.1.0 on Central version 4.9.2.5 6:29:52 - SQL: SELECT COUNT(*) AS MyCount FROM audit; 6:29:52 - Current Audit Records: 3824 6:29:52 - SQL: SELECT ID,LogTime,Action,TargetType,TargetID,TargetName,Error,Err orText FROM audit WHERE username='elroy_admin' and action LIKE 'task_%' OR ac tion LIKE 'cfgsec_%' ORDER BY ID DESC; 6:29:52 - Ready for Excel Reply: 43311 2007-10-19 18:08:49.83 task_run task 174565303 Get and Parse X File Paths 0 43310 2007-10-19 18:08:39.41 task_run task 598707145 AS2 Rece ive from Shemp to Moe 0 43309 2007-10-19 18:08:35.83 cfgsec_update task 598707145 AS2 Rece ive from Shemp to Moe 0 6:29:52 - Normal (XML) Reply: <Response> <CmdName>RUNCUSTOMSQL</CmdName> <ReqID>6</ReqID> <RepCode>0</RepCode> <RepText></RepText> <Time>2007-10-19 18:29:52</Time> <Output><TimeOfLastUpdate></TimeOfLastUpdate> <Records> <Record><ID>43311</ID> <LogTime>2007-10-19 18:08:49.83</LogTime> <Action>task_run</Action> <TargetType>task</TargetType> <TargetID>174565303</TargetID> <TargetName>Get and Parse X File Paths</TargetName> <Error>0</Error> <ErrorText></ErrorText> </Record> <Record><ID>43310</ID> <LogTime>2007-10-19 18:08:39.41</LogTime> <Action>task_run</Action> <TargetType>task</TargetType> <TargetID>598707145</TargetID> <TargetName>AS2 Receive from Shemp to Moe</TargetName> <Error>0</Error> <ErrorText></ErrorText> </Record> <Record><ID>43309</ID> <LogTime>2007-10-19 18:08:35.83</LogTime> <Action>cfgsec_update</Action> <TargetType>task</TargetType> <TargetID>598707145</TargetID> <TargetName>AS2 Receive from Shemp to Moe</TargetName> <Error>0</Error> <ErrorText></ErrorText> </Record> <More>1</More> </Records> </Output> </Response> 6:29:52 - runcustomsql.vbs complete
Sample Output (Without Proper Credentials)
6:32:39 - runcustomsql.vbs running 6:32:39 - Running API version 4.9.1.0 on Central version 4.9.2.5 6:32:39 - SQL: SELECT COUNT(*) AS MyCount FROM audit; 6:32:39 - Failed to count records: Error#2020: You do not have sufficient permissi ons for this command.