Examples - Run Custom Reports

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.

VBScript Code

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 = "&#199;"   ' 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

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