Analysis object usage

[perumal_41]

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

[perumal_41]

Please any one Replay  .I waiting for Reply.

Regards ,

Perumal.A

[#3]

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. 

[#4]

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

next

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

Hit OK

[#5]

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

[#6]

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

[perumal_41]

Hi Holly M.

Please upload Sample file is Possible . for my reference

Regards

Perumal A

[#8]

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

[#9]

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.

[#10]

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