Qlikview Server CAL Manager

    *** May, 02 2016: beta tester wanted ***


    I'm working on a new version of this tool with Active Directory integration. If you're using the DMS security mode and are managing your application access through Active Directory groups, it may be of interest for you.


    This new version will allow you to analyze:

    users and groups hierarchy (including nested groups) => in which groups does this user belong to ?



    Notes:

    • See at the end of the document for updates information.
    • These tools are provided free of charge and are not officially supported. Usage is entirely at the user's own risk.
    • There is a bug with QlikView 11.2 SR13 which prevents (at least) Named CAL deletion, it seems to be on Qlik side, ongoing analysis... Fixed, please upgrade to 1.4.2 if needed

     

    Many customers often ask if it's possible to have a global dashboard on CAL allocation/usage. The first question most of the time is "Is there Named/Document CALs allocated and not used for some time now ?" but there are of course many others.

     

    Some existing applications already do this (like Qlikview System Monitor here: http://community.qlik.com/docs/DOC-4307) but they are using Qlikview Server logs files as a source and so do lack some important info. For example a document CAL allocated but never used will obviously not be logged. This set of tools are connecting directly to Qlikview Server and can retrieve such information. With it you can even find out if there are deleted documents which still have allocated CALs (yes, it's possible, that's what I would call "lost CALs" because you don't see them anymore anywhere but they are "in use" and cannot be allocated anymore...). For more information on this, please read this blog post from stevedark: http://www.quickintelligence.co.uk/remove-orphaned-qlikview-apps/ , thanks Steve!

     

    Another interesting feature is the ability to automatically delete unused document CALs after a defined number of days. It can be really useful for applications with dynamic CAL assignment.

     

    Requirements

     

    These tools are compatible only with Qlikview Server v11.0 and v11.2. Moreover, QlikTech R&D confirmed me there is a known Qlikview Server bug with 11.0 which may result in the connection being lost during information retrieval. Please use version 11.2 if you can!

     

    Other requirements :

    • NET Framework 4.0 (same as Qlikview Server)
    • TCP port 4799 not filtered between the server and the client
    • The Windows account running the tools has to be a member of the following local groups on the Qlikview Server:
      • QlikView Administrators
      • Qlikview Management API

     

    Note: If the "Qlikview Management API" group doesn't exist you will have to create it. After creation it's advised to restart the server or, at least, the Qlikview services.

     

    Retrieving CAL/Document information

     

    The following information can be retrieved:

    • CALs in license (Named/Document/Session/Usage)
    • CALs allocated (Named CAL, Document CAL with associated document)
    • Session CALs and Usage CALs currently used (only with QVS 11.20 SR7+)
    • Documents attributes and authorisations (which user has access to which application - only if Qlikview Server security is set to DMS mode)

     

    Using a Qlikview application

     

    The easiest way to retrieve and analyse these info is through the provided Qlikview application but to do so you will have to installl a specific Qlikview connector (see at the end of this document for download links).

     

    The installation is pretty straightforward, just copy the files to this folder:

    %CommonProgramFiles%\QlikTech\Custom Data\QVSManager

     

    Notes:

    • %CommonProgramFiles% = 'C:\Program Files\Common Files' on most systems but it may be translated ('Fichiers communs' in french for example)
    • other folders may not exist, juste create them

     

    After having installed the connector, open the Qlikview application, change the server name if needed and just reload!

    QVSCALManager-1.png

    QVSCALManager.png

    QVSCALManager-1.png

    With a command line tool

     

    You can also retrieve these information with a command line tool (see at the end of this document for download links).

     

    Usage: QVSManagerBatch.exe get -<switch 1> -<switch n>

     

    Commands
      get                  Retrieve license/documents info
        Switches
          -license          Get CALs in license
          -cals              Get allocated Named/Document CALs
          -documents        Get documents
          -attributes        Get documents attributes
          -authorisations    Get documents authorisations
          -output=<path>    Save files in <path> (default value: current path)
    
      ...                  (other commands)
    
    
    Global switches
      -server=<srv>          QMS Server (default value: localhost)
      -qvsserver=<srv>      QVS Server (needed only if you have more than one
                            QlikView server registered on your Management Console)
      -log=<file>            Log file location (default value: no log)
      -test                  Test mode, do not actually modify CALs/documents
      -debug                Display debug information
    
    
    
    
    
    
    
    
    
    
    
    
    

     

    For example

     

    QVSManagerBatch.exe get -server=192.168.0.204 -license -log=d:\logs\qvscalmanager.log -debug
    
    
    
    
    
    
    
    
    
    
    
    
    

     

    Save license information of the QVS server '192.168.0.204' in the current directory and log actions in d:\logs\qvscalmanager.log

     

    Result

     

    Log file: d:\logs\qvscalmanager.log
    [Debug] Connecting to QMS server '192.168.0.204'...
    [Debug] OK
    [Debug] Retrieving Qlikview Server version...
    [Debug] 11.20.12018.0409.10
    [Debug] Retrieving server authorisation mode...
    [Debug] OK (DMS)
    Saving files to D:\Tools
    Retrieving License information...
    [Debug] Clearing CAL Configuration cache...
    [Debug] Retrieving CAL Configuration...
    OK
    
    
    
    
    
    
    
    
    
    
    
    
    

     

    Delete unused/specific CALs

     

    It is also possible to automatically delete unused CALs (Named or Document). It may be useful :

    • to delete CALs not used for some time
    • to delete a specific list of CALs
    • to periodically delete all CALs for documents having "dynamic CAL assignment" enabled

     

    Usage: QVSManagerBatch.exe clean -<switch 1> -<switch n>


    Commands
      ...                  (other commands)
    
      clean                Delete CALs
        Switches
          -caltype=<type>    CAL type to check
                            values: 'named', 'document' (default) or 'all'
          -days=<d>          Only if unused for <d> days
          -calslist=<list>  List of CALs to check, separated by ';'
                            (format: domain\user, case insensitive)
          -unusedcals        Also delete unused CALs
          -documents=<path>  Check only documents whose path/name match <path>
                            (* and ? wildcards accepted)
          -alldocuments      Also check documents without dynamic CAL assignment
    
    Global switches
      -server=<srv>          QMS Server (default value: localhost)
      -qvsserver=<srv>      QVS Server (needed only if you have more than one
                            QlikView server registered on your Management Console)
      -log=<file>            Log file location (default value: no log)
      -test                  Test mode, do not actually modify CALs/documents
      -debug                Display debug information
    
    
    
    
    
    
    
    
    
    
    
    
    


    For example

     

    QVSManagerBatch.exe clean -server=192.168.0.204 -caltype=document -days=30 -documents=*Golf* -test -debug
    
    
    
    
    
    
    
    
    
    
    
    
    
    

     

    Delete Document CALs not used for at least 30 days for documents whose path/name contains "Golf" on server '192.168.0.204', do it in test mode so that nothing is actually deleted.

     

    Result

     

    [Debug] Connecting to QMS server '192.168.0.204'...
    [Debug] OK
    [Debug] Checking services status...
    [Debug] OK
    [Debug] Retrieving Qlikview Server version...
    [Debug] OK (11.20.12018.0409.10)
    [Debug] Retrieving server authorisation mode...
    [Debug] OK (DMS)
    
    -------------- Deleting Document CALs --------------
      - not used for 30 days
      - unused CALs won't be deleted
      - only for the following documents: *Golf*
      - Test mode enabled
    
    [Debug] Checking Golf Quest.qvw...
    [Debug] Deleting DV-W2K8-VM\DVASSEUR CAL...
    [Debug] Movies Database.qvw is not matching filter '*Golf*', ignoring...
    Golf Quest.qvw - document CAL 'DV-W2K8-VM\DVASSEUR' deleted (not used since 04/09/2013 18:28:41)
    1 document(s) checked, 1 Document CAL(s) deleted!
    
    
    
    
    
    
    
    
    
    
    
    
    

     

    Allocate Document CALs

     

    It is also possible to allocate a number of Document CALs to a list of documents and to enable/disable dynamic CAL assignment

     

    Usage: QVSManagerBatch.exe set -<switch 1> -<switch n>

     

    Commands
      ...                  (other commands)
    
    
      set                  Set allocated CALs to documents
        Switches
          -documents=<path>  For documents whose path/name match <path>
                            (* as wildcard accepted)
          -allocatedcals=<x> Number of Document CALs to allocated
          -dynamicassign=<b> Enable/disable dynamic CAL assignment
                            values: 'true' or 'false'
    
    Global switches
      -server=<srv>          QMS Server (default value: localhost)
      -qvsserver=<srv>      QVS Server (needed only if you have more than one
                            QlikView server registered on your Management Console)
      -log=<file>            Log file location (default value: no log)
      -test                  Test mode, do not actually modify CALs/documents
      -debug                Display debug information
    
    
    
    
    
    
    
    
    
    
    
    
    

     

    For example

     

    QVSManagerBatch.exe set -server=192.168.0.12 -documents=*Golf* -allocatedcals=50 -dynamicassign=true -test -debug
    
    
    
    
    
    
    
    
    
    
    
    
    

     

    Allocate 50 Document CALs on documents whose path/name contains "Golf" on server '192.168.0.12', do it in test mode so that nothing is actually allocated.

     

    Result

     

    [Debug] Connecting to QMS server '192.168.0.12'...
    [Debug] OK
    [Debug] QlikView Server: QVS@xlcio-srv
    [Debug] Checking services status...
    [Debug] OK
    [Debug] Retrieving Qlikview Server version...
    [Debug] OK (11.20.12742.409)
    [Debug] Retrieving server authorisation mode...
    [Debug] OK (DMS)
    
    -------------- Configuring documents licenses --------------
      - for the following documents: *Golf*
      - allocate 50 document CALs
      - enable dynamic CAL assignment
      - Test mode enabled
    
    [Debug] Clearing CAL Configuration cache...
    [Debug] Retrieving CAL Configuration...
    [Debug] Clearing User Documents cache...
    [Debug] Checking Golf Quest.qvw...
    Allocating 50 CALs to 'Golf Quest.qvw'...
    Activating dynamic CAL assignment for 'Golf Quest.qvw'...
    
    
    
    
    
    
    
    
    
    
    
    

    Add CALs

     

    It is also possible to add a list of Named/Documents CALs.

     

    Usage: QVSManagerBatch.exe add -<switch 1> -<switch n>

     

    Commands
      ...                  (other commands)
    
    
      add                  Add CALs
        Switches
          -caltype=<type>    CAL type to add
                            values: 'named' or 'document'
          -calslist=<list>  List of CALs to add, separated by ';'
                            (format: domain\user, case insensitive)
          -documents=<path>  Add only to documents whose path/name match <path>
                            (* as wildcard accepted)
    
    Global switches
      -server=<srv>          QMS Server (default value: localhost)
      -qvsserver=<srv>      QVS Server (needed only if you have more than one
                            QlikView server registered on your Management Console)
      -log=<file>            Log file location (default value: no log)
      -test                  Test mode, do not actually modify CALs/documents
      -debug                Display debug information
    
    
    
    
    
    
    
    
    
    
    
    

    For example

     

    QVSManagerBatch.exe add -server=192.168.0.12 -caltype=named -calslist="XLCIO-SRV\DVASSEUR;XLCIO-SRV\PMICHEL" -test -debug
    
    
    
    
    
    
    
    
    
    
    
    

     

    Add 2 Named CALs "XLCIO-SRV\DVASSEUR" and "XLCIO-SRV\PMICHEL" on server '192.168.0.12', do it in test mode so that nothing is actually added.

     

    Result

     

    [Debug] Connecting to QMS server '192.168.0.12'...
    [Debug] OK
    [Debug] QlikView Server: QVS@xlcio-srv
    [Debug] Checking services status...
    [Debug] OK
    [Debug] Retrieving Qlikview Server version...
    [Debug] OK (11.20.12742.409)
    [Debug] Retrieving server authorisation mode...
    [Debug] OK (DMS)
    
    -------------- Adding 2 CALs --------------
      - CAL type: Named
      - CALs list: XLCIO-SRV\DVASSEUR, XLCIO-SRV\PMICHEL
      - Test mode enabled
    
    [Debug] Clearing CAL Configuration cache...
    [Debug] Retrieving Named CAL configuration...
    [Debug] Saving Named CAL configuration...
    2 Named CALs added: XLCIO-SRV\DVASSEUR, XLCIO-SRV\PMICHEL
    
    
    
    
    
    
    
    
    
    
    
    

     

    Updates

     

    version 1.1 - 2013-11-14
    • Initial release

     

    version 1.2 - 2014-01-08
    • Tools: Mapping between CAL and real user name added (lookup performed through Qlikview Server DSC, the account running this service has to be granted to query Active Directory)
    • Tools: debug switch added
    • Qlikview application: corrected a date format bug which may prevent US customers to see "last used" information
    • Qlikview application: CAL/User name switch added
    • Qlkview application: various small bugs fixed

     

    Note: Special thanks to Nathan Hardman for his feedback/ideas!

     

    version 1.2.1 - 2014-03-14
    • Qlkview application: fixed a stupid bug introduced in version 1.2 which prevented Documents CAL to be properly displayed

     

    version 1.3 - 2014-04-11
    • Tools: new option to also delete Named CALs
    • Tools: minor bugs fixed

     

    version 1.3.1 - 2014-06-16
    • Tools: new option ("unusedcals") to also delete unused CALs

    Design change: unused CALs are not automatically deleted anymore, you have to enable this new option if you want to delete them

    • Tools: It is now possible to delete CALs not used for only 1 day

     

    version 1.4 - 2015-05-26
    • Tools: Design change: the "filter" switch has been renamed to "documents" for the "clean" command
    • Tools: new "qvsserver" switch added to specify to which QVS server to connect to (useful if you have more than one QVS registered in your QMC)
      Tools: new "calslist" switch to define a specific list of CALs to check
    • Tools: new "set" command to allocate a number of CALs to documents
    • Tools: new "add" command to add Named CALs
    • Tools: minor bugs fixed
    • Tools: GUI not maintained anymore, it would require a lot a time to upgrade it to 1.4 and I'm not sure it's really used anyway
    version 1.4.1 - 2015-09-02
    • Tools: "add" command can also add Document CALs


    version 1.4.2 - 2016-02-05
    • Tools: fix for QVS 11.20 SR13

     

    Important Notice from Qlik: Please note that the use of this and other related scripts named and document CALs between users is prohibited under Qlik’s license terms and use may result in non-compliance and  a possible license audit. Specifically the QlikView License Metrics document (http://www.qlik.com/us/legal/license-terms) states: Named and Document CALs, and the NPrinting Designer, are assigned to an identified user and may not be transferred to another user unless the transfer is due to a i) termination of employment, or ii) a transfer of the prior user to a new business unit or group resulting in no access to the Qlik Software. When a Named User or Document CAL is transferred, there is a 24- hour quarantine before the CAL can be used by another user. If you have any questions please consult the QlikView License Metric document or contact Jimmie Gull (jimmie.gull@qlikcom).