Qlik Community

QlikView Documents

Documents for QlikView related information.

Loading Doc Cals and User Access (Governance Dashboard)

adamdavi3s
Honored Contributor

Loading Doc Cals and User Access (Governance Dashboard)

Version2- Slight tweak to hash the document name and key for CAL data as Qlik re-uses old key numbers for cals

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:

calkey:

MAPPING

load  %Key_PerDocumentCalData_71E03D5A91FA7462, Hash128(CalFileName, %Key_PerDocumentCalData_71E03D5A91FA7462) as calkey;

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]);

// 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,'MEARSDOM\','')) as doccalbasename,

    ToBeDeleted as doccaltobedeleted,

  LastUsed as doccallastused,

    ApplyMap('calkey',%Key_PerDocumentCalData_71E03D5A91FA7462,Null())   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

   ApplyMap('calkey',%Key_PerDocumentCalData_71E03D5A91FA7462,Null())  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 CalFileName, Hash128(CalFileName,calkey) as calkey;

LOAD DISTINCT

UPPER('$(vInputFilePath)'&'\'&DocumentName) as CalFileName,

    %Key_PerDocumentCalData_71E03D5A91FA7462 as calkey    // 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 ;//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

(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!

Version history
Revision #:
1 of 1
Last update:
‎2016-08-25 07:46 AM
Updated by: