Loading Doc Cals and User Access (Governance Dashboard)

    No idea if this is helpful for anyone else or not but I've been doing some work to the governance dashboard to show the Cal and access data.

    Obviously these changes will make your dashboard 'unsupported'

     

    Our primary drivers was to show:

    1- Where people have CALs and no NTFS access

    2- To show any Ghost cals, so cals attached to deleted qvw files

     

    Capture.PNG

     

    The CAL data is probably the most useful, this can obviously be loaded from the xml files. Of course you must have tweaked your settings.ini to generate the xml files (PGO to XML conversion to get the list of users in QVS ) but once you have done so then the code is pretty straightforward:

     

    // Start of [CalData.pgo.xml] LOAD statements
    
    
    NamedCals:
    LOAD rowno() as nc_key,
      Name as namedcalname,
        ToBeDeleted as namedcaltobedeleted,
        LastUsed as namedcallastused,
        %Key_CalData_A6E97F950E348A57   // Key to parent table: CalData
    FROM [$(vInputFilePath)\CalData.pgo.xml] (XmlSimple, Table is [CalData/NamedCalsAllocated/CalAllocEntry]);
    
    
    DocCals:
    LOAD rowno() as dc_key,
      Name as doccalname,
      UPPER(replace(Name,'DOMAIN\','')) as doccalbasename,
        ToBeDeleted as doccaltobedeleted,
      LastUsed as doccallastused,
        %Key_PerDocumentCalData_71E03D5A91FA7462   as CalDataKey,
        1 as doccalallocated // Key to parent table: CalData/PerDocumentCalData/PerDocumentCalData
    FROM [$(vInputFilePath)\CalData.pgo.xml] (XmlSimple, Table is [CalData/PerDocumentCalData/PerDocumentCalData/NamedCalsAllocated/CalAllocEntry]);
    
    
    PerDocumentCalData:
    LOAD
      rowno() as pdcd_key,
      DocumentName as CalFileName,
        NoOfNamedCals,
        AllowDynamicAllocation,
        NoOfEmbeddedNamedCals,
        %Key_CalData_A6E97F950E348A57,    // Key to parent table: CalData
        %Key_PerDocumentCalData_71E03D5A91FA7462  as CalDataKey   // Key for this table: CalData/PerDocumentCalData/PerDocumentCalData
    FROM [$(vInputFilePath)\CalData.pgo.xml] (XmlSimple, Table is [CalData/PerDocumentCalData/PerDocumentCalData]);
    
    
    CalData:
    LOAD
      rowno() as cd_key,
      AvailableUsageCals,
        UsageCalUpdateTimestamp,
        SessionsOnUsageCals,
        [PerServerCalData/PerServerCalData/ServerName] as ServerName,
        [PerServerCalData/PerServerCalData/LastUpdated] as LastUpdated,
        [LefCalInfo/Uncapped] as Uncapped,
        [LefCalInfo/NoOfSessionCals] as NoOfSessionCals,
        [LefCalInfo/NoOfUsageCals] as NoOfUsageCals,
        [LefCalInfo/NoOfNamedCals],
        [LefCalInfo/NoOfClusterNodes] as NoOfClusterNodes,
        [LefCalInfo/NoOfDocumentCals] as NoOfDocumentCals,
        [LefCalInfo/NoOfInfrequentNamedCals] as NoOfInfrequentNamedCals,
        [LefCalInfo/NoOfConcurrentInfrequentNamedCals] as NoOfConcurrentInfrequentNamedCals,
        %Key_CalData_A6E97F950E348A57    // Key for this table: CalData
    FROM [$(vInputFilePath)\CalData.pgo.xml] (XmlSimple, Table is [CalData]);
    // End of [CalData.pgo.xml] LOAD statements
    
    

     

     

     

     

    To link it in to the main dashboard I just made these changes to the filelist tab:

     

    filekey:
    MAPPING LOAD DISTINCT
    UPPER('$(vInputFilePath)'&'\'&DocumentName) as CalFileName,
        %Key_PerDocumentCalData_71E03D5A91FA7462    // Key to parent table: CalData
    
    
    FROM [$(vInputFilePath)\CalData.pgo.xml] (XmlSimple, Table is [CalData/PerDocumentCalData/PerDocumentCalData]);
    
    

     

     

    Then in the FileList load:

     

    ApplyMap('filekey',UPPER(FileName),Null()) as CalDataKey,
    
    

     

     

     

    Finally we can then do a check to see if the files in the filelist still exist or not using this code:

     

    FileListCheckPreloader:
    LOAD FileName as FileNameCheck
    RESIDENT FileList;
    
    LET CKNumRows=NoOfRows('FileListCheckPreloader');
    FOR p=1 to $(CKNumRows)
      LET vFileChecker=Peek('FileNameCheck',$(p),'FileListCheckPreloader');
      Let baseFileSizeCheck = IF(LEN(FileSize('$(vFileChecker)'))<1,0,1);
      TRACE Checking: $(vFileChecker);
      check:
      LOAD
      '$(vFileChecker)' as FileName,
      $(baseFileSizeCheck)   as FileExistsFlag
      AUTOGENERATE 1;
    NEXT p;
    
    

     

     

    The user access stuff will depend how you're set up to control this.

    We do it through AD so I just query AD to pull back the users in the various Qlik specific access groups and drag this into a SQL table.

    See here: Querying LDAP to pull back people in AD groups – Qlik and Dirty

     

    I then just call this into Qlik:

     

    LOAD* ,upper([samAccountName]) as samAccountNameUpper,rowno() as ad_key;
    SELECT UPPER([groupName]) as groupName
          ,[displayname]
          ,[samAccountName]
          ,[lastLogon]
          ,[mail]
          ,[inserted_date]
      FROM [dbo].[Active_Directory_Qlikview_Users_And_Groups];
    
    

     

     

    Great so we have a list of users in Qlikview who are in our AD groups, but how do we know if they have access to the folders?

    This is where a cheeky little powershell script can help you out. This one I just pulled from google and slightly adapted it for my needs:

     

    Param(
        [String]$path ="D:\QlikView\Published Apps",
        [String]$outfile = "D:\QlikView\Published Apps\SystemMonitoring\generated_files\folder_permissions\outfile.csv"
    )
    $output = @()
    
    ForEach ($item in (Get-ChildItem -Path $path -Recurse -Directory)) {
    
        ForEach ($acl in ($item.GetAccessControl().Access)){
    
    
            $output += $acl |
                Add-Member `
                    -MemberType NoteProperty `
                    -Name 'Folder' `
                    -Value $item.FullName `
                    -PassThru
          
        }
    }
    $output | Export-Csv -Path $outfile -NoTypeInformation
    
    

     

     

    rwunderlich has an excellent article on allowing Qlik to execute scripts:

    http://qlikviewcookbook.com/2016/01/authorizing-the-script-execute-statement/

     

    Then we can just pull this in to Qlik:

     

    EXECUTE powershell [D:\QlikView\Published Apps\SystemMonitoring\scripts\folder_permissions.ps1];//folder permissions
    
    folder_permissions:
    LOAD rowno() as fp_key,
      Folder as Path,
         FileSystemRights,
         AccessControlType,
         UPPER(Replace(IdentityReference,'MEARSDOM\','')) as groupName,
         IdentityReference as rawgroupname,
         IsInherited,
         InheritanceFlags,
         PropagationFlags
    FROM
    [.\generated_files\folder_permissions\outfile.csv]
    (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
    
    

     

     

     

    Finally, what about those people who might be members of local server groups?

    Again this is where a handy little powershell script will work:

     

    Param(
        [String]$computers ="fernqlikview01",
        [String]$outfile = "D:\QlikView\Published Apps\SystemMonitoring\generated_files\folder_permissions\local_groups.csv"
    )
    $computers | foreach {
    $computername = $_
    
    
    [ADSI]$S = "WinNT://$computername"
    $S.children.where({$_.class -eq 'group'}) |
    Select @{Name="Computername";Expression={$_.Parent.split("/")[-1] }},
    @{Name="Name";Expression={$_.name.value}},
    @{Name="Members";Expression={
    [ADSI]$group = "$($_.Parent)/$($_.Name),group"
    $members = $Group.psbase.Invoke("Members")
    ($members | ForEach-Object {
    $_.GetType().InvokeMember("Name", 'GetProperty', $null, $_, $null)
    }) -join ";"
    }}
    } |  Export-Csv -Path $outfile -NoTypeInformation
    
    

     

     

    You can then just concat this into the AD data:

     

    Concatenate
    LOAD groupName, samAccountName, UPPER(samAccountName) as samAccountNameUpper;
    
    LOAD upper(@2) as groupName, SubField(@3,';') as samAccountName;
    
    LOAD @1,
         UPPER('BUILTIN\'&@2) as @2,
         @3,
         substringcount( @3 , ';') as splitcount
    FROM
    [D:\QlikView\Published Apps\SystemMonitoring\generated_files\folder_permissions\local_groups.csv]
    (txt, codepage is 1252, no labels, delimiter is ',', msq, header is 1 lines)
    WHERE len(@3) >0;
    
    

     

     

     

     

    From here on out it is basically just sorting out your data and mapping it all together!