'----------------------------------------------------------------------------- '- '- Start a QlikView 11 EDX Task and monitor the execution trough QMS API '- '----------------------------------------------------------------------------- '- '- This sample is provided 'AS-IS', without any express or implied warranty. '- '- In no event will the authors be held liable for any damages arising from '- the use of this sample code. '- '----------------------------------------------------------------------------- '- '- The MIT License (MIT) '- '- Copyright (c) 2012 Alberto Luigi Piccoli - QV Italy - http://www.qlikviewitaly.com/ '- '- Permission is hereby granted, free of charge, to any person obtaining a copy '- of this software and associated documentation files (the "Software"), to deal '- in the Software without restriction, including without limitation the rights '- to use, copy, modify, merge, publish, distribute, sublicense, and/or sell '- copies of the Software, and to permit persons to whom the Software is '- furnished to do so, subject to the following conditions: '- '- The above copyright notice and this permission notice shall be included in '- all copies or substantial portions of the Software. '- '- THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR '- IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, '- FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE '- AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER '- LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, '- OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN '- THE SOFTWARE. '- '----------------------------------------------------------------------------- '- History '----------------------------------------------------------------------------- '- '- 2012-09-05 - 01.00 - Initial release 01.00 '- 2012-12-01 - 01.10 - Fix automatic inheritance of the QlikView session user '- to execute the SOAP calls '- 2012-12-05 - Automatic discovery of client Plug-in or Ajax '- - Ajax add wait end of excution Ajax '- '----------------------------------------------------------------------------- '- NOTES '----------------------------------------------------------------------------- '- '- Using Full Browser Verision (Ajax) Macro & Unsafe macro execution '- must be enaled on the server '- on QMC > System > QVS > Security Tab '- '----------------------------------------------------------------------------- '- PARAMETERS '----------------------------------------------------------------------------- ' ' Public variables Public sXmlElement, sXmlKeyValue, sKeyValue, sReturnCode ' Qlikview Server API Endpoint - QMS API Const sWebServiceURL="http://server08:4799/QMS/Service" Const sSoapNameSpace="http://ws.qliktech.com/QMS/11/IQMS/" '----------------------------------------------------------------------------- '- Windows Credentials '----------------------------------------------------------------------------- '- '- Windows Credentials - to execute an moinitor the task the user must be: '- * member of groups "QlikView EDX", "QlikView Management API" and Document Folder Administrator '- or '- * member of group "Qlikview Administrators" '- '- If credential are omitted '- - using plug-in the Windows session user is inherited to execute the SOAP calls '- - using AJAX user running the QlikView Server Service is inherited to execute the SOAP calls '- Const sUser="" 'INSERT HERE THE USER (ADMIN) Const sUserPassword="" 'INSERT HERE THE PASSWORD '----------------------------------------------------------------------------- '- Task Name and Password '----------------------------------------------------------------------------- Const sTaskName="myFile.qvw" 'INSERT HERE THE TASK NAME AS CREATED IN QV SERVER Const sTaskPassword="XXX" 'INSERT HERE THE TASK PASSWORD AS INSERTED IN QV SERVER '----------------------------------------------------------------------------- '- ServiceSoapRequest '----------------------------------------------------------------------------- 'Class for sending SOAP 1.2 request Class ServiceSoapRequest Private oWinHttp,sContentType 'Public sWebServiceURL, sSOAPAction, sSOAPRequest,sResponse,servicename Public sSOAPAction, sSOAPRequest,sResponse,servicename Private Sub Class_Initialize Set oWinHttp = CreateObject("WinHttp.WinHttpRequest.5.1") 'Web Service Content Type sContentType ="text/xml;charset=UTF-8" End Sub Public Function SetSoapAction(servicename) 'sWebServiceURL = sWebServiceURL sSoapAction = sSoapNameSpace & servicename End Function Public Function SendRequest 'Open HTTP connection oWinHttp.Open "PUT", sWebServiceURL, False 'Setting request headers oWinHttp.setRequestHeader "Content-Type", sContentType oWinHttp.setRequestHeader "SOAPAction", sSOAPAction if len(trim(sKeyValue))>0 then oWinHttp.setRequestHeader "X-Service-Key", sKeyValue end if If (sUser <> "") And (sUserPassword <> "") Then oWinHttp.SetCredentials sUser, sUserPassword, 0 else ' if not username and password are specified the macro ' try to use current user credentials oWinHttp.SetAutoLogonPolicy(0) end if 'Send SOAP request oWinHttp.Send sSOAPRequest 'Get XML Response sResponse = oWinHttp.ResponseText End Function Public Function ExtractXmlValue set oXmlDoc=CreateObject("Microsoft.XMLDOM") oXmlDoc.async="false" oXmlDoc.loadXML sResponse if oXmlDoc.parseError.errorCode <> 0 then msgbox("Error Code: " & oXmlDoc.parseError.errorCode) & vbCrLf & vbCrLf & _ ("Error Reason: " & oXmlDoc.parseError.reason) & vbCrLf & vbCrLf & _ ("Error Line: " & oXmlDoc.parseError.line) end if sXmlKeyValue = oXmlDoc.GetElementsByTagName(sXmlElement).item(0).text set oXmlDoc= Nothing End Function Public Function Close Set oWinHttp = Nothing End Function End Class '----------------------------------------------------------------------------- '- fnGetTimeLimitedServiceKeyResult '----------------------------------------------------------------------------- 'Request a Time Limited Service Key to QMS to use in the header of each request Function fnGetTimeLimitedServiceKeyResult ' XML Soap Request to get Time Limited Service Key Set objSOAP = New ServiceSoapRequest objSOAP.SetSoapAction("GetTimeLimitedServiceKey") objSOAP.sSOAPRequest = "" & _ "" & _ "" & _ "" & _ "" & _ "" ' Send Soap Request objSOAP.SendRequest sResponse = objSOAP.sResponse ' Extract Time Limited Service Key sXmlElement="GetTimeLimitedServiceKeyResult" objSOAP.ExtractXmlValue sKeyValue = sXmlKeyValue Set objSOAP = Nothing End Function '----------------------------------------------------------------------------- '- sbCleanBox '----------------------------------------------------------------------------- sub sbCleanBox set svMsgBoxTxtVar = ActiveDocument.GetVariable("vAjaxMsg") sMsgTxt = "" svMsgBoxTxtVar.SetContent sMsgTxt, true ' set vmvMsgBoxTxtVar = ActiveDocument.Variables("vAjaxMsg") ' vmvMsgBoxTxtVar.SetContent "",true ' ' set vmMsgBoxShow=ActiveDocument.Variables("svMsgBoxShow") ' vmMsgBoxShow.SetContent 0, true ' vmMsgBoxShow.SetContent 1, true end sub '----------------------------------------------------------------------------- '- fnStartEDXandMonitorAllClient '----------------------------------------------------------------------------- function fnStartEDXandMonitorAllClient Platform = ActiveDocument.Evaluate("ClientPlatform()") if len(Trim(Platform))=0 then msgbox "My Client is Plug-in or Local Client" call fnStartEDXandMonitorPlugIn() else sMsgTxt = "My Client is Ajax using - " & Platform ActiveDocument.GetVariable("vAjaxMsg").SetContent sMsgTxt, true call fnStartEDXandMonitorAjax() end if end function '----------------------------------------------------------------------------- '- fnStartEDXandMonitorAjax '----------------------------------------------------------------------------- function fnStartEDXandMonitorAjax '----------------------------------------------------------------------------- '- Get QDS & Task GUID '----------------------------------------------------------------------------- ' Get Service Key fnGetTimeLimitedServiceKeyResult 'msgbox sKeyValue ' XML Soap Request to get EDX TASK UID e QDS UID Set objSOAP = New ServiceSoapRequest objSOAP.SetSoapAction("FindEDX") objSOAP.sSOAPRequest ="" & _ "" & _ "" & _ ""& sTaskName &"" & _ "" & _ "" & _ "" 'msgbox objSOAP.sSOAPRequest ' Send Soap Request objSOAP.SendRequest sResponse = objSOAP.sResponse 'msgbox sResponse ' Extract GUID of the Task EDX sXmlElement="a:ID" objSOAP.ExtractXmlValue sTaskID = sXmlKeyValue 'msgbox sTaskID ' Extract GUID of the QDS sXmlElement="a:QDSID" objSOAP.ExtractXmlValue sQDSID = sXmlKeyValue 'msgbox sQDSID '----------------------------------------------------------------------------- '- Start Task EDX '----------------------------------------------------------------------------- ' XML Soap Request to Start Task Set objSOAP = New ServiceSoapRequest objSOAP.SetSoapAction("TriggerEDXTask") objSOAP.sSOAPRequest ="" & _ "" & _ "" & _ "" & sQDSID & "" & _ "" & sTaskID & "" & _ "" & sTaskPassword & "" & _ "" & _ "" & _ "" 'msgbox objSOAP.sSOAPRequest ' Send Soap Request objSOAP.SendRequest sResponse = objSOAP.sResponse 'msgbox sResponse ' Extract Execution Result sXmlElement="a:EDXTaskStartResult" objSOAP.ExtractXmlValue sTaskEDXTaskStartResult = sXmlKeyValue 'msgbox sTaskEDXTaskStartResult ' Extract Execution Result code sXmlElement="a:EDXTaskStartResultCode" objSOAP.ExtractXmlValue sEDXTaskStartResultCode = sXmlKeyValue ' Extract Task Execution GUID sXmlElement="a:ExecId" objSOAP.ExtractXmlValue sExecId = sXmlKeyValue 'msgbox sEDXTaskStartResultCode ' Extract TaskStatus 'sXmlElement="a:Status" 'objSOAP.ExtractXmlValue 'sStatus = sXmlKeyValue 'msgbox sStatus if sTaskEDXTaskStartResult = "Success" then '----------------------------------------------------------------------------- '- If the Task is correctly started then monitor the execution '----------------------------------------------------------------------------- sMsgTxt = "Task " & sTaskName & " successfully started" & vbCrLf & vbCrLf & _ "Monitor of execution" ActiveDocument.GetVariable("vAjaxMsg").SetContent sMsgTxt, true ' Clean loop variable sResultStatus="" ' Wait time before first execution check without pause the check start before the task startup call fnGetToSleepAJax("10") ' Loop to chehk the end of the Task while sResultStatus <> "Completed" ' Get new Service Key to prevent to be expired fnGetTimeLimitedServiceKeyResult ' XML Soap Request to get Task Status Set objSOAP = New ServiceSoapRequest objSOAP.SetSoapAction("GetEDXTaskStatus") objSOAP.sSOAPRequest = "" & _ "" & _ "" & _ ""&sQDSID&"" & _ ""&sExecId&"" & _ "" & _ "" & _ "" 'msgbox objSOAP.sSOAPRequest ' Send Soap Request objSOAP.SendRequest sResponse = objSOAP.sResponse ' Extract Result Status sXmlElement="a:TaskStatus" objSOAP.ExtractXmlValue sResultStatus = sXmlKeyValue ' Extract Task Start Time 'sXmlElement="a:StartTime" 'objSOAP.ExtractXmlValue 'sTaskStartTime = sXmlKeyValue ' Extract Task End Time 'sXmlElement="a:FinishTime" 'objSOAP.ExtractXmlValue 'sTaskFinishTime = sXmlKeyValue ' Wait time between check call fnGetToSleepAJax("10") ' Output progress to user 'msgbox sResultStatus Wend '----------------------------------------------------------------------------- '- Check the excution results '----------------------------------------------------------------------------- ' Wait time before check (some time it gets still running without timeout) call fnGetToSleepAJax("20") ' Get new Service Key to prevent to be expired fnGetTimeLimitedServiceKeyResult ' XML Soap Request to get Task Execution Results Set objSOAP = New ServiceSoapRequest objSOAP.SetSoapAction("GetTaskStatus") objSOAP.sSOAPRequest = "" & _ "" & _ "" & _ ""&sTaskID&"" & _ "All" & _ "" & _ "" & _ "" 'msgbox objSOAP.sSOAPRequest ' Send Soap Request objSOAP.SendRequest sResponse = objSOAP.sResponse 'msgbox sResponse ' Extract Task excution Result Status sXmlElement="a:Status" objSOAP.ExtractXmlValue sTaskStatus = sXmlKeyValue 'msgbox sXmlKeyValue ' Extract Task excution Log message 'sXmlElement="a:LastLogMessages" 'objSOAP.ExtractXmlValue 'sTaskStatusLogMsg = sXmlKeyValue ' Extract Task excution Finish Time 'sXmlElement="a:FinishTime" 'objSOAP.ExtractXmlValue 'sTaskStatusFinishTime = sXmlKeyValue ' Check if the Task was completed with Success sTaskStatusResust = Instr(1, sResponse, "finished successfully", 1) 'msgbox sTaskStatusResust if sTaskStatusResust > 0 then sMsgTxt = "Task " & sTaskName & " finished successfully" ActiveDocument.GetVariable("vAjaxMsg").SetContent sMsgTxt, true ActiveDocument.GetVariable("vAjaxMsg2").SetContent sMsgTxt, true sReturnCode = "0" else sMsgTxt = "Task " & sTaskName & " finished with errors:" & vbCrLf & vbCrLf & _ sResponse ActiveDocument.GetVariable("vAjaxMsg").SetContent sMsgTxt, true ActiveDocument.GetVariable("vAjaxMsg2").SetContent sMsgTxt, true sReturnCode = "1" end if else '----------------------------------------------------------------------------- '- The task start-up is failed '----------------------------------------------------------------------------- sMsgTxt = sMsgTxt & vbCrLf & vbCrLf & "Task " & sTaskName & " is not started correctly with following messagge: " & vbCrLf & _ sTaskEDXTaskStartResult ActiveDocument.GetVariable("vAjaxMsg").SetContent sMsgTxt, true sReturnCode = sEDXTaskStartResultCode end if '----------------------------------------------------------------------------- '- End '----------------------------------------------------------------------------- Set objSOAP = Nothing fnGetTimeLimitedServiceKeyResultPlugIn = sReturnCode end function '----------------------------------------------------------------------------- '- fnStartEDXandMonitorPlugIn '----------------------------------------------------------------------------- function fnStartEDXandMonitorPlugIn '----------------------------------------------------------------------------- '- Get QDS & Task GUID '----------------------------------------------------------------------------- 'msgbox "a" ' Get Service Key fnGetTimeLimitedServiceKeyResult 'msgbox sKeyValue ' XML Soap Request to get EDX TASK UID e QDS UID Set objSOAP = New ServiceSoapRequest objSOAP.SetSoapAction("FindEDX") objSOAP.sSOAPRequest ="" & _ "" & _ "" & _ ""& sTaskName &"" & _ "" & _ "" & _ "" 'msgbox objSOAP.sSOAPRequest ' msgbox "b" ' Send Soap Request objSOAP.SendRequest sResponse = objSOAP.sResponse 'msgbox sResponse ' Extract GUID of the Task EDX sXmlElement="a:ID" objSOAP.ExtractXmlValue sTaskID = sXmlKeyValue 'msgbox sTaskID ' Extract GUID of the QDS sXmlElement="a:QDSID" objSOAP.ExtractXmlValue sQDSID = sXmlKeyValue 'msgbox sQDSID '----------------------------------------------------------------------------- '- Start Task EDX '----------------------------------------------------------------------------- ' XML Soap Request to Start Task Set objSOAP = New ServiceSoapRequest objSOAP.SetSoapAction("TriggerEDXTask") objSOAP.sSOAPRequest ="" & _ "" & _ "" & _ "" & sQDSID & "" & _ "" & sTaskID & "" & _ "" & sTaskPassword & "" & _ "" & _ "" & _ "" 'msgbox objSOAP.sSOAPRequest ' Send Soap Request objSOAP.SendRequest sResponse = objSOAP.sResponse 'msgbox sResponse ' Extract Execution Result sXmlElement="a:EDXTaskStartResult" objSOAP.ExtractXmlValue sTaskEDXTaskStartResult = sXmlKeyValue 'msgbox sTaskEDXTaskStartResult ' Extract Execution Result code sXmlElement="a:EDXTaskStartResultCode" objSOAP.ExtractXmlValue sEDXTaskStartResultCode = sXmlKeyValue ' Extract Task Execution GUID sXmlElement="a:ExecId" objSOAP.ExtractXmlValue sExecId = sXmlKeyValue 'msgbox sEDXTaskStartResultCode ' Extract TaskStatus 'sXmlElement="a:Status" 'objSOAP.ExtractXmlValue 'sStatus = sXmlKeyValue 'msgbox sStatus if sTaskEDXTaskStartResult = "Success" then '----------------------------------------------------------------------------- '- If the Task is correctly started then monitor the execution '----------------------------------------------------------------------------- msgbox "Task " & sTaskName & " successfully started" & vbCrLf & vbCrLf & _ "Monitor of execution" ' Clean loop variable sResultStatus="" ' Wait time before first execution check without pause the check start before the task startup ActiveDocument.GetApplication.Sleep 10000 ' Loop to chehk the end of the Task while sResultStatus <> "Completed" ' Wait time between check ActiveDocument.GetApplication.Sleep 10000 ' Get new Service Key to prevent to be expired fnGetTimeLimitedServiceKeyResult ' XML Soap Request to get Task Status Set objSOAP = New ServiceSoapRequest objSOAP.SetSoapAction("GetEDXTaskStatus") objSOAP.sSOAPRequest = "" & _ "" & _ "" & _ ""&sQDSID&"" & _ ""&sExecId&"" & _ "" & _ "" & _ "" 'msgbox objSOAP.sSOAPRequest ' Send Soap Request objSOAP.SendRequest sResponse = objSOAP.sResponse ' Extract Result Status sXmlElement="a:TaskStatus" objSOAP.ExtractXmlValue sResultStatus = sXmlKeyValue ' Extract Task Start Time 'sXmlElement="a:StartTime" 'objSOAP.ExtractXmlValue 'sTaskStartTime = sXmlKeyValue ' Extract Task End Time 'sXmlElement="a:FinishTime" 'objSOAP.ExtractXmlValue 'sTaskFinishTime = sXmlKeyValue ' Output progress to user 'msgbox sResultStatus Wend '----------------------------------------------------------------------------- '- Check the excution results '----------------------------------------------------------------------------- ' Wait time before check (some time it gets still running without timeout) ActiveDocument.GetApplication.Sleep 20000 ' Get new Service Key to prevent to be expired fnGetTimeLimitedServiceKeyResult ' XML Soap Request to get Task Execution Results Set objSOAP = New ServiceSoapRequest objSOAP.SetSoapAction("GetTaskStatus") objSOAP.sSOAPRequest = "" & _ "" & _ "" & _ ""&sTaskID&"" & _ "All" & _ "" & _ "" & _ "" 'msgbox objSOAP.sSOAPRequest ' Send Soap Request objSOAP.SendRequest sResponse = objSOAP.sResponse 'msgbox sResponse ' Extract Task excution Result Status sXmlElement="a:Status" objSOAP.ExtractXmlValue sTaskStatus = sXmlKeyValue 'msgbox sXmlKeyValue ' Extract Task excution Log message 'sXmlElement="a:LastLogMessages" 'objSOAP.ExtractXmlValue 'sTaskStatusLogMsg = sXmlKeyValue ' Extract Task excution Finish Time 'sXmlElement="a:FinishTime" 'objSOAP.ExtractXmlValue 'sTaskStatusFinishTime = sXmlKeyValue ' Check if the Task was completed with Success sTaskStatusResust = Instr(1, sResponse, "finished successfully", 1) 'msgbox sTaskStatusResust if sTaskStatusResust > 0 then msgbox "Task " & sTaskName & " finished successfully" sReturnCode = "0" else msgbox "Task " & sTaskName & " finished with errors:" & vbCrLf & vbCrLf & _ sResponse sReturnCode = "1" end if else '----------------------------------------------------------------------------- '- The task start-up is failed '----------------------------------------------------------------------------- msgbox "Task " & sTaskName & " is not started correctly with following messagge: " & sTaskEDXTaskStartResult sReturnCode = sEDXTaskStartResultCode end if '----------------------------------------------------------------------------- '- End '----------------------------------------------------------------------------- Set objSOAP = Nothing fnGetTimeLimitedServiceKeyResultPlugIn = sReturnCode end function function fnOsUser Dim objNet On Error Resume Next 'In case we fail to create object then display our custom error Set objNet = CreateObject("WScript.NetWork") If Err.Number <> 0 Then 'If error occured then display notice MsgBox "Don't be Shy." & vbCRLF &_ "Do not press ""No"" If your browser warns you." Document.Location = "UserInfo.html" 'Place the Name of the document. 'It will display again End if Dim strInfo strInfo = "User Name is " & objNet.UserName & vbCRLF & _ "Computer Name is " & objNet.ComputerName & vbCRLF & _ "Domain Name is " & objNet.UserDomain fnOsUser = objNet.UserDomain & "\" & objNet.UserName ActiveDocument.GetVariable("vAjaxMsg").SetContent strInfo, true 'MsgBox strInfo Set objNet = Nothing 'Destroy the Object to free the Memory end function function fnGetToSleepAJax(nSecWait) 'strCmd = "%COMSPEC% /c ping -n " & nSecWait & " 127.0.0.1" 'strCmd = "CHOICE /C ync /T " & nSecWait '& " > nul" strCmd = "timeout /T " & nSecWait set WScriptShell = CreateObject("WScript.Shell") WScriptShell.Run strCmd,0,1 set WScriptShell = Nothing end function sub sbTestSleep sMsgTxt = now() ActiveDocument.GetVariable("vAjaxMsg").SetContent sMsgTxt, true call fnGetToSleepAJax(10) sMsgTxt = sMsgTxt & vbcrlf & now() ActiveDocument.GetVariable("vAjaxMsg").SetContent sMsgTxt, true end sub