This Java 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.
To run this example you should have had run at least 4 tasks in MOVEit Automation.
Assuming you are in the samples directory
MICentralAPIJava\samples>java -cp .;..\MICentralAPIJava.jar RunCustomReports
Executing SQL query: SELECT COUNT(*) AS MyCount FROM audit;
Current Audit Records: 1633
Executing SQL query: SELECT ID,LogTime,Action,TargetType,TargetID,TargetName,Err
or,ErrorText FROM audit WHERE (Action LIKE 'task_%' OR Action LIKE 'cfgsec_%') O
RDER BY ID DESC;
ID LogTime Action TargetType TargetID TargetName Error
ErrorText
1620 2007-10-25 15:15:01.87 task_run task 104961658 API Test
0
1615 2007-10-25 15:01:09.75 task_run task 12345678 Temporar
y Task Test Using Existing Host 0
1612 2007-10-25 14:54:10.49 task_run task 12345678 Temporar
y Task Test Using Ad Hoc Host 0
Full XML Reply:
<Response>
<CmdName>RUNCUSTOMSQL</CmdName>
<ReqID>4</ReqID>
<RepCode>0</RepCode>
<RepText></RepText>
<Time>2007-10-26 10:21:17</Time>
<Output><TimeOfLastUpdate></TimeOfLastUpdate>
<Records>
<Record>1620|2007-10-25 15:15:01.87|task_run|task|104961658|API Test|0|</Record>
<Record>1615|2007-10-25 15:01:09.75|task_run|task|12345678|Temporary Task Test U
sing Existing Host|0|</Record>
<Record>1612|2007-10-25 14:54:10.49|task_run|task|12345678|Temporary Task Test U
sing Ad Hoc Host|0|</Record>
<More>1</More>
</Records>
</Output>
</Response>
See your MOVEit Automation manual ("Advanced Topics - Database - Schema" section) for information about the log table schemas.
The main body of the example highlights how to run a custom SQL SELECT and then display the results.
Here is an execution of custom SQL:
String sql = "SELECT COUNT(*) AS MyCount FROM audit;";
showMsg("Executing SQL query: " + sql);
String replyXML = oAPI.runCustomSQL(sql, 1, 0, false, "");
if (replyXML.equals("")) {
showMsg("Error executing database query: " + oAPI.getErrorDescription());
return;
}
The results of executing custom SELECT will be an XML document. In this example the XML is parsed into a DOM Document object and then XPath is used to select the Record nodes. The XPath to get the Task nodes is: /Response/Output/Records/Record. Only one record is returned from this SELECT, so the example shows the first record. The column name "MyCount" can be found in the Record element - the full XPath for that count is: /Response/Output/Records/Record/MyCount.
Document replyDoc = parse_xml(replyXML);
Node outputNode = get_node_xpath(replyDoc.getDocumentElement(),
"/Response/Output");
showMsg("Current Audit Records: " + get_node_value_xpath((Element)outputNode,
"Records/Record/MyCount"));
The next part of the example runs another custom SELECT and uses the Delimiter option of "runCustomSQL" to delimit the columns using a "|" instead of with column names as elements of Record.
// This query reads audit records for a particular user
sql = "SELECT ID,LogTime,Action,TargetType,TargetID,TargetName,Error,ErrorText " +
"FROM audit " +
"WHERE (Action LIKE 'task_%' OR Action LIKE 'cfgsec_%') " +
"ORDER BY ID DESC;";
showMsg("Executing SQL query: " + sql);
// Limit the results to three entries, and delimit the results with a vertical bar
replyXML = oAPI.runCustomSQL(sql, 3, 0, true, "|");
if (replyXML.equals("")) {
showMsg("Error executing database query: " + oAPI.getErrorDescription());
return;
}
replyDoc = parse_xml(replyXML);
outputNode = get_node_xpath(replyDoc.getDocumentElement(), "/Response/Output");
// Display output in tab-delimited format
NodeList recordNodes = get_nodes_xpath((Element)outputNode, "Records/Record");
if (recordNodes.getLength() > 0) {
showMsg("ID\tLogTime\tAction\tTargetType\tTargetID\tTargetName\tError\tErrorText");
Element recordEle = null;
String recordString = "";
for (int i = 0; i < recordNodes.getLength(); i++) {
recordEle = (Element)(recordNodes.item(i));
recordString = get_node_value_xpath(recordEle,".");
showMsg(recordString.replace(
("|".toCharArray())[0],
("\t".toCharArray())[0]));
}
}