Solved: QlikView User Audit Dashboard - Qlik Community

jbeierschmitt · ‎2009-11-25

I need to perform an audit of our QlikView user base and would like to develop an application to provide this in a daily report.

We currently utilize QlikView Publisher Enterprise 8.5 via AccessPoint to distribute QlikView documents to specific Active Directory Groups. I would like to build the application to tell me which Active Directory Group(s) are valid per published document as well as query AD to tell me who are the current users assigned to each group.

I'm uncertain if there is a way to query QlikView Publisher Enterprise to tell me which AD Groups are recipients for each document? Anyomne have any experience developing a similar application?

Jim

Permalink · ‎2011-09-29

Hi Bonar,

Below is the code for your ref, what it does is to retrieve the ACL info for each distributed QVW in the folders, and save them in the txt file,

then QV will read this txt file. you can integrate this with the audit dashboard made by Brad.

The info is sufficient for you to know who has access to which QVW files,

but i'm trying to also build who has not access which qvw and when.

I have some ideas but no time to work on. if you are interested, i could explain to u in detailed.

have fun with it...

' Read a File DACL

Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objTextFile = objFSO.OpenTextFile("F:\Qlikview Storage\Private Data\Source Documents\In Production\QVW_Access\QVW_AccessList.txt", ForWriting, True)

strFolder = "F:\Qlikview Storage\Public Data\AccessPoint Documents\"

Set FSO = CreateObject("Scripting.FileSystemObject")

set FLD = FSO.GetFolder(strFolder)

Set objWMIService = GetObject("winmgmts:")

For Each Fil In FLD.Files

'MsgBox Fil.Name

extFile = FSO.GetExtensionName(Fil.Name)

If FSO.GetExtensionName(Fil.Name) = "qvw" Then

strFileName = strFolder&Fil.Name

strFileName2 = Fil.Name

SE_DACL_PRESENT = &h4

ACCESS_ALLOWED_ACE_TYPE = &h0

ACCESS_DENIED_ACE_TYPE = &h1

Set objFileSecuritySettings = objWMIService.Get("Win32_LogicalFileSecuritySetting='" & strFileName & "'")

intRetVal = objFileSecuritySettings.GetSecurityDescriptor(objSD)

intControlFlags = objSD.ControlFlags

If intControlFlags AND SE_DACL_PRESENT Then

arrACEs = objSD.DACL

For Each objACE in arrACEs

WScript.Echo objACE.Trustee.Domain & "\" & objACE.Trustee.Name & " " & strFileName2

objTextFile.WriteLine(strFileName2 & vbTab & objACE.Trustee.Domain & "\" & objACE.Trustee.Name)

WScript.Echo "No DACL present in security descriptor"

End If

Else

End If

strFileName = ""

objFileSecuritySettings = ""

Permalink · ‎2010-01-12

Hi Jim,

I am looking for the same too.

http://community.qlik.com/forums/t/24385.aspx

Permalink · ‎2010-01-12

Brad Peterman created a fantastic Audit application to serve your needs to a 't'.

Email him at brad.peterman@qliktech.com. He may be willing to share it with you guys.

Permalink · ‎2010-01-13

Hi Jim and Nick,

hopefully the attached applications provide some useful information

Good luck!

Rainer

Permalink · ‎2010-01-13

The Operations Monitor in the ZIP file Rainer posted was the example I was referring to.

The Performance Monitor is pretty awesome too.

Brad_Peterman · ‎2010-01-13

Nick and Jim,

Did the zip file that was sent meet your needs? We do have a new Ops Monitor for QlikView 9 that will be released very soon. In the new Ops Monitor we have more information about receipients and you can more easily tie your AD groups to that. I believe you can see where the Publisher recipients data comes from if you look into the script of the Ops Monitor that Rainer sent to you. Let me know if you have questions or suggestions on new utilities that we should develop to make these kinds of things easier. Thanks!

Brad Peterman, QlikTech

Permalink · ‎2010-01-13

Hi Brad,

Thanks for your info. Currently, i'm still with version 8.2. Upgrading to version 9 is in planning.

We are also using the same Ops Monitor that Rainer attached, but i was thinking if there's any other Ops Monitoring app out here that provides more info?

Is the new Ops Monitor compatible with version 8.2? and whether there's any cost to have it?

Thanks.

Brad_Peterman · ‎2010-01-13

Nick,

The 8.5 version of the Ops Monitor should work with 8.2, but you might see a couple of fields missing that you'll need to comment out, like the CAL Type in the Sessions Log. Most of the scripting and charts will still work though.

Brad

Permalink · ‎2010-01-15

Thanks Brad.
So in every upgrade, the Ops Monitor will be modified?

At the mean time, I explored the QVPR db a bit. and the following query will allow you to see who has access to which QV file. not the best, but that my fastest solution for now.

if you have a better solution, feel free to share.

SELECT

Recipient. XSName, SourceDocument. Name, DistributionTask. Name AS Expr1, conn_DT_Rec. ModifiedByUser, conn_DT_Rec. ModifiedTime,

DistributionTask

. Enabled, conn_DT_Rec. CreatedByUser, conn_DT_Rec.

CreatedTime

FROM

conn_DT_Rec INNER

JOIN

DistributionTask

ON conn_DT_Rec. DistributionTaskID = DistributionTask. ID INNER

JOIN

Recipient

ON conn_DT_Rec. RecipientID = Recipient. ID INNER

JOIN

SourceDocument

ON DistributionTask. SourceDocumentID = SourceDocument.

ID

order

by SourceDocument.

Name

Brad_Peterman · ‎2010-01-15

Nick,

The query looks good. Of course, the trick here is when you distribute to a group, such as an Active Directory group, how do you determine the recipients? This is where a join into the AD table is needed.

To your question about new versions of the Ops Monitor. It's necessarily a new version with each QlikView release, but we will put out a new version each time the QVS logs or QVPR change. This happened with QV9, so we needed a new Ops Monitor. We will try to make this happen along side the next major release so there is not much lag time between the release and a new Ops Monitor.

Thanks!

brad