topic QlikView User Audit Dashboard in QlikView

QlikView User Audit Dashboard

jbeierschmitt — Thu, 26 Nov 2009 01:46:53 GMT

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

QlikView User Audit Dashboard

Wed, 13 Jan 2010 03:45:05 GMT

Hi Jim,

I am looking for the same too.

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

QlikView User Audit Dashboard

Wed, 13 Jan 2010 04:58:15 GMT

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.

QlikView User Audit Dashboard

Wed, 13 Jan 2010 08:39:33 GMT

Hi Jim and Nick,

hopefully the attached applications provide some useful information

Good luck!

Rainer

QlikView User Audit Dashboard

Wed, 13 Jan 2010 08:53:17 GMT

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

The Performance Monitor is pretty awesome too.

QlikView User Audit Dashboard

Brad_Peterman — Wed, 13 Jan 2010 13:24:10 GMT

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

QlikView User Audit Dashboard

Wed, 13 Jan 2010 14:55:24 GMT

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.

QlikView User Audit Dashboard

Brad_Peterman — Wed, 13 Jan 2010 22:55:03 GMT

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

QlikView User Audit Dashboard

Fri, 15 Jan 2010 05:37:06 GMT

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.

order

by SourceDocument.

Name

QlikView User Audit Dashboard

Brad_Peterman — Fri, 15 Jan 2010 14:32:13 GMT

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

QlikView User Audit Dashboard

Fri, 19 Feb 2010 02:27:42 GMT

Brad, let us know when you've got the SR3 Ops Monitor ready to go.

Cheers.

QlikView User Audit Dashboard

Tue, 02 Mar 2010 12:29:12 GMT

Hello,

Does anyone know if the QVPR database stores user's email address?

I could find the NT acct name, but unfortunetely, it's just initial and hard to tell names.

Any suggestion?

Hi Brad,

Do you think it's possible for you to make a screen shot for us of the new Audit dashboard?

Thanks.

QlikView User Audit Dashboard

Brad_Peterman — Tue, 02 Mar 2010 15:15:18 GMT

Here is a screenshot of new v9 Ops Monitor application. This is being tested for v9 SR3 compatibility right now and will be put out there on QlikCommunity soon.

QlikView User Audit Dashboard

Tue, 02 Mar 2010 16:01:18 GMT

Thanks Brad! Looks good.

QlikView User Audit Dashboard

Wed, 10 Mar 2010 04:39:59 GMT

Hi Guys,

If you are still working on this. I have some info to share.

There's a site by Rob Wunderlich, download the cookbook, and go to the ADextract folder.

http://robwunderlich.com/Download.html

The script allow you to see name from the AD directory, but you can add the following to get more, then you can import data you need from QVPR.


SQL select
distinguishedName,
cn,
displayName,
givenName,
sn,
mail,
userAccountControl,
name,
c,
company,
department,
telephoneNumber

QlikView User Audit Dashboard

jbeierschmitt — Thu, 01 Apr 2010 01:03:06 GMT

Trying to utilize the Qperations Monitor to support our requirement to identify which AD Groups/email users have authorization to access the various QlikView documents on our AccessPoint.

Think I understand how to put together the xml pieces through the Operations Monitor and the other posts, however....

Hit a dead end when trying to load the DistributionReport.xml into QlikView - error states "Switch from current encoding to specified encoding not supported. On line number: 1. On column number: 40"

C:\ProgramData\QlikTech\Publisher\Qlikview Distribution Service\1\Log\20100224\190847 - IT_QV_008_UserDocument1

xml header: <?xml version="1.0" encoding="utf-16"?>

It appears that QlikView Desktop is not able to load this as xml? The data that I need is the document name in AccessPoint as well as the recipients - both called out in the DistributionReport.xml document

We are on Windows Server 2008 64-bit, QlikView 9.00.7436.8 (x64)

Thansk in advance for your help!

QlikView User Audit Dashboard

Brad_Peterman — Thu, 01 Apr 2010 14:09:55 GMT

Jim,

This definitely looks like it was written using a different coding format. If you are still having an issue with this please send me a copy of your DistributionReport.xml file and I will see if I can get it to read into QV correctly.

http://www.w3schools.com/XML/xml_encoding.asp

Thanks,

brad.peterman@qlik.com

QlikView User Audit Dashboard

Wed, 10 Aug 2011 02:04:47 GMT

Hello all,

Finally, there's a way to really see "who has access to which QVWs".

With the help of this vbscript.

It read the DACL of QVW document, extract them into txt with this format >> QVWName | LOGON

http://community.qlik.com/docs/DOC-2293

WIth this file, you can easily integrate into V9/V10 Ops Monitor dashboard and link it with Sessions table.

QlikView User Audit Dashboard

bonarasido — Thu, 08 Sep 2011 03:36:02 GMT

Hi nicksatch do i need specific permission to view this document? i try to view but can't

QlikView User Audit Dashboard

Thu, 08 Sep 2011 11:28:00 GMT

Hi Bonar

In fact, i lost all my published doc, i don't know why, i sent a message to the community manager Jason Long, he didn't reply, and i don't know who else to contact.

If you want to see the doc, you can give me your email address, i'll send a copy to you. it's a simple vbscript.

-Nick