topic Re: Analysis object usage in QlikView

Analysis object usage

perumal_41 — Tue, 23 Aug 2011 04:40:07 GMT

Hi Everybody ,

I want analysis object usage Per userwise through Macro .I am using Qlikview 9 Server .Its not have Audit Log file.

how analysis object usage Per userwise through Macro.I Don't Know is possible or not

Analysis object usage

perumal_41 — Tue, 23 Aug 2011 06:16:12 GMT

Please any one Replay .I waiting for Reply.

Regards ,

Perumal.A

Analysis object usage

Thu, 08 Mar 2012 17:04:22 GMT

Hi Perumal.A,

As far as I can tell, even in QlikView 10 where you have the Audit log file, there is still not any detail on maximize or activate for sheet objects (charts, etc.). I have been working on this one myself for version 10 in macros. I will let you know if I find an answer.

Holly M.

Re: Analysis object usage

Fri, 09 Mar 2012 16:11:31 GMT

There is a lot of work behind this, but I have tested and this will work for QlikView 10. I can't confirm if this works for QlikView 9 or earlier. I tried to use roughly the same log formatting that QlikTech uses in order to integrate the log files into any existing reporting. You will need to repeat this process for each document on your server that you want to track for it to work. If you haven't already done so, you will need to make sure your server allows (unsafe?) macros to execute.

Step 1 - Create Macro:

Go to Tools (menu) -> Edit Module

Paste in the following (VBScript macro code):

sub logChart

set doc=ActiveDocument
strDocPath = doc.GetPathName
set s=doc.ActiveSheet
objs = s.GetActiveSheetObjects
for i=lbound(objs) to ubound(objs)

strObjectName = objs(i).GetObjectId
strfileLine = "" & chr(9) & Now() & chr(9)& strDocPath
strfileLine = strfileLine & chr(9) & "Activate" & chr(9)
strfileLine = strfileLine & Application.GetProperties().UserName & chr(9)
strfileLine = strfileLine & "Sheet Object Document\" & strObjectName
WriteLog(strfileLine)
msgbox strfileLine

end sub

Function openOutputFile(byval filename)

CONST ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(filename) Then

boolNewFile = false

else

boolNewFile = true

end if
Set openedFile = objFSO.OpenTextFile(filename, ForWriting, True)
If boolNewFile then

openedFile.WriteLine("Server Started TimeStamp Document Type User Message")

end if
Set openOutputFile = openedFile

End Function

Function WriteLog(byval message)

strYM = Cstr(Year(Now())) & "-" & Right("0" & CStr(Month(Now())),2)
logfilename = "C:\ProgramData\QlikTech\QlikViewServer\AuditObjectUsage_ServerName_" & strYM & ".log"
Set logFile = openOutputFile(logfilename)
logFile.WriteLine(message)
logFile.Close

End Function

Step 2 - Associate Macros:

On each sheet of the document, you will need to do the following steps.

First, right-click on sheet and select Properties

Go to the Triggers tab of the window that pops up

For each Object ID, do the following steps (if you haven't already done so, you may also wish to go in to properties of each object and give your objects more meaningful IDs)

Click on the object ID

Click Add Action

Click Add from the Actions Window

Select Action Type 'External' and Action 'Run Macro', then hit OK

For Macro Name type 'logChart'

Hit OK

Re: Analysis object usage

Fri, 09 Mar 2012 18:59:02 GMT

I just noticed that I left the msgbox line in there! You will want to take that out for production.

Re: Analysis object usage

Thu, 15 Mar 2012 17:55:11 GMT

As I continue to test this, I have found a few more things. I'm still working through the fact that it only seems to log from the QlikView Desktop and not from the server ajax ZFP client. I've also found a few other things, so here is the updated code (sorry about the indentation - it was a real pain to indent properly last time through).

sub logChart
set doc=ActiveDocument
strDocPath = doc.GetPathName
set s=doc.ActiveSheet
objs = s.GetActiveSheetObjects
for i=lbound(objs) to ubound(objs)
strObjectName = objs(i).GetObjectId
strfileLine = "" & chr(9) & euroDateTimeFormat(Now()) & chr(9)& strDocPath
strfileLine = strfileLine & chr(9) & "Activate" & chr(9)
strfileLine = strfileLine & Application.GetProperties().UserName & chr(9)
strfileLine = strfileLine & "Sheet Object " & strObjectName
WriteLog(strfileLine)
next
end sub

Function openOutputFile(byval filename, byref boolNewFile)
CONST ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(filename) Then
boolNewFile = false
else
boolNewFile = true
end if
Set openedFile = objFSO.OpenTextFile(filename, ForAppending, True)
Set openOutputFile = openedFile
End Function

Function euroDateTimeFormat(byVal dateString)
euroDateTimeFormat = Cstr(Year(Now())) & "-" & Right("0" & CStr(Month(Now())),2) _
& "-" & Right("0" & Cstr(Day(Now())),2) & " " & FormatDateTime(dateString, 4) _
& ":" & Right("0" & Cstr(Second(Now())),2)
End Function

Function getServerStartTime(byval fileName)
CONST ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set openedFile = objFSO.OpenTextFile(fileName, ForReading)
Do While openedFile.AtEndOfStream <> True
auditLine = openedFile.ReadLine()
Loop
openedFile.Close
startTime = Left(auditLine, 19)
getServerStartTime = startTime
End Function

Function WriteLog(byval message)
strYM = Cstr(Year(Now())) & "-" & Right("0" & CStr(Month(Now())),2)
objlogfilename = "C:\ProgramData\QlikTech\QlikViewServer\AuditObjectUsage_yourServerNameHere_" & strYM & ".log"
audLogFileName = "c:\ProgramData\QlikTech\QlikViewServer\Audit_yourServerNameHere_" & strYM & ".log"
Set objlogFile = openOutputFile(objlogfilename,newFile)
ServerStartTime = getServerStartTime(audLogFileName)
If newFile then
objlogFile.WriteLine("Server Started" & chr(9) & "Timestamp" & chr(9) & "Document" & chr(9) & "Type" & chr(9) & "User" & chr(9) & "Message")
end if
objlogFile.WriteLine(cstr(ServerStartTime) & message)
objlogFile.Close
End Function

Re: Analysis object usage

perumal_41 — Fri, 16 Mar 2012 04:18:53 GMT

Hi Holly M.

Please upload Sample file is Possible . for my reference

Regards

Perumal A

Re: Analysis object usage

Fri, 16 Mar 2012 14:10:48 GMT

Our internal QlikView files all contain HIPAA Protected Health Information, so I will have to put something new together.

Re: Analysis object usage

Fri, 16 Mar 2012 15:14:20 GMT

I have attached a pared-down copy of the movies database demo with the object logging macros set up on four of the charts. You will need to put your server name in to the macros for it to work. As is, you will get a "file not found" when you try to run it until you change the location to point to your server name in the macros. Let me know if you have any questions.

Re: Analysis object usage

Mon, 03 Sep 2012 11:03:54 GMT

Hi, I am trying to get this to work for my health based reports Holly but it keeps tripping over at line 19...

Set objFSO = CreateObject("Scripting.FileSystemObject")

Do you know what may be causing this?

Thanks

Ian

Re: Analysis object usage

Tue, 04 Sep 2012 13:12:41 GMT

Hi Ian,

It has been a while since I looked at this because the good people at QlikTech have now confirmed that it only works when users open the QVW files in the QlikView client software (anyone who opens the dashboard through the publisher site isn't logged with the script). This particular script uses (requires) some advanced permissions to make that logging happen, so you have to change the scripting engine security settings to make it work (and I suspect that is where you're getting your error). The module security needs to be set to system access and the current local security needs to allow system access. If you make these two changes, I suspect that will fix the problem (when you open the file, once those are set, it will ask you if you're sure you want it to have those access levels - you'll want to select yes or it won't work!

Hope that helps.

Holly M.

Re: Analysis object usage

Tue, 04 Sep 2012 13:24:21 GMT

Thanks Holly, looks like I’ll join the queue in waiting for a decent logging mechanism built into the software itself