How to drive IE with Powershell to Automatic Delete unused QlikView CALs

Zürich, 30-03-2017

Well, that wasn’t easy… I wanted to help my client to manage his server CALs . So I tried to install some open source Qlikview management utility for this purpose.

Unfortunately, due to lack of time, administration rights … and knowledge… I wasn’t  very successful in my intent.

So, more as a fun then seriously, I started to build a  iE “robot” script for that.

I tried 1st with perl, but the WIn32::OLE module wasn’t very promising: after logging  in the QV server console, it was just … stacked.

So I don’t even know how, I had the idea to try with Powershell, ok but I never programmed power shell before, so after looking on the net I tried this 1st script:

$ie = new-object -ComObject "InternetExplorer.Application"
$ie.Width=1400
$requestUri = 'http://localhost:4780/QMC/Licenses.htm#'
$ie.Visible = $true
$ie.navigate($requestUri)
do {sleep 1} until (-not $ie.busy) 
$doc = $ie.document

and voilà my IE opened and showed me the QV server, as I would be myself doing it manually.

So, next step, open the dev-tools, and try to identify the QlikView server Element in the License page, which by clicking, gets you to the CAL section.

In this server, this element is called: ‘EntLicenses.Services.List.1.Name’

So I tried:

$QVServerLine = $doc.getElementById('EntLicenses.Services.List.1.Name')

But nothing worked, why? After some hours of investigation it turned round that for Windows Server 2012 with IE 10 and Powershell 3.0  preinstalled (which is quite old…) you have to do like that:

$QVServerLine = [System.__ComObject].InvokeMember(“getElementById”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $doc, 'EntLicenses.Services.List.1.Name')

….Sigh…

Nevertheless…, who cares?  I’m not easy to surrender…, so finally I got the script:

$ie = new-object -ComObject "InternetExplorer.Application"
$ie.Width=1400
$requestUri = 'http://localhost:4780/QMC/Licenses.htm#'
$ie.Visible = $false
$ie.navigate($requestUri)
do {sleep 1} until (-not $ie.busy) 
$doc = $ie.document
$QVServerLine = [System.__ComObject].InvokeMember(“getElementById”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $doc, 'EntLicenses.Services.List.1.Name')
$QVServerLine.click()
do {sleep 1} until (-not $ie.busy) 
$CalTab = [System.__ComObject].InvokeMember(“getElementById”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $doc,  'CALsContainer.AssignedTab')
do {sleep 1} until (-not $ie.busy) 
$CalTab.click()
do {sleep 1} until (-not $ie.busy) 
$TodayDate=(GET-DATE)
Write-Output "-- Today Date for checking licenses  $TodayDate  --"
$LicenseContainer = [System.__ComObject].InvokeMember(“getElementById”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $doc,  'pagingpEntLicenses.Properties.CalInfo.AssignedList.List')
$LicensePages =@([System.__ComObject].InvokeMember(“getElementsByClassName”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $LicenseContainer, 'searchClear'))
for($pg=0; $pg -lt $LicensePages.length; $pg++) {
    $LicensePages[$pg].click()
    do {sleep 1} until (-not $ie.busy)
    Write-Output "--------------------------------------"
    Write-Output "-- select page $pg  --"
    for($i=0; $i -le 100; $i++) {
        $FullName = [System.__ComObject].InvokeMember(“getElementById”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $doc,  'EntLicenses.Properties.CalInfo.AssignedList.List.'+$i+'.FullName')
        if (!$FullName.textContent){continue};
        Write-Output "-- examine $($FullName.textContent)-"
        $LastUsed = [System.__ComObject].InvokeMember(“getElementById”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $doc,  'EntLicenses.Properties.CalInfo.AssignedList.List.'+$i+'.LastUsed')
             if (!$LastUsed.textContent){continue};
             Write-Output "-- Last Used $($LastUsed.textContent)  --"
        $LastDate=[datetime]::parseexact($LastUsed.textContent,"dd.MM.yyyy HH:mm:ss",$null)
        $delta=NEW-TIMESPAN  $LastDate  $TodayDate
        if ($delta.Days -le 15.01) {continue};
        Write-Output "-- delta Days  $delta.Days  --"
        Write-Output " ----->  delete lease for $($FullName.textContent) --"
        $DeleteCal = [System.__ComObject].InvokeMember(“getElementById”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $doc,  'EntLicenses.Properties.CalInfo.AssignedList.List.'+$i+'.DeleteCal')
             if (!$DeleteCal){continue};
        $DeleteCal.click();
        do {sleep 1} until (-not $ie.busy)
    }
    $apply = [System.__ComObject].InvokeMember(“getElementById”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $doc,  'apply')
       do {sleep 1} until (-not $ie.busy)
       Write-Output "apply for page $pg "
       $apply.click()
       do {sleep 1} until (-not $ie.busy)
}
$ie.Quit()
$ie=$null
Write-Output "End  "

Isn’t ugly?  But it’s working! And it’s very useful! I installed the script on the server and let the script run every 12 hours.

Ciao!