How to see for each session opened apps and sheets, and how many time the user spent in any sheet

[LDR]

Hi to everyone,

I'm writing you because I need to find/build an app for monitorizing the next below:

- For each user session I need to know the apps she/he has opened, also sheets and finally how long the user was working on each sheet.

I was playing with the app "Operations Monitor" and the sheet "Sheet Usage". More or less it's what I want, however  inside the column "Latest Activity" I only have data for the last 7 days (I'm not applying any filter by date or whatever, just for the app that I want to track).

I also made a copy of this app and changed "Last Activity" measure by the dimension "Sheet latest activity Date" with no luck.

So, did anyone have the same requirement than me and solved it? how?

Thanks in advance

[Jack_Guo]

Hi @LDR ,

Please try Session monitor app, import via the below path.

C:\ProgramData\Qlik\Sense\Repository\DefaultApps 

I hope this helps.

[LDR]

Hi @Jack_Guo 

I'm working with that app and also Opeartions Monitor. What I saw is:

- From Session Monitor --> I can easily obtain how much time each user session was working in any app. That information can be checked inside the sheet "Session Details".

- From Operations Monitor -->  Inside the sheet "Sheet Usage" if I include UserId I can see who opened any sheet but I can't see the timestamp when any sheet was opened, etc. I tried using SessionStart and SessionFinish but for some records are empty. 

At the end I'd like to know: User, SessionId, App Name, Open app Timestamp, Close App Timestamp, Sheet Name, Open Sheet timestamp, Close Sheet Timestamp, ...

Like I said, at an App level I know when it was opened and closed, however I would need the same detail level for sheets.

Do you have any clue or suggestion?

Thanks for your help

[LDR · Accepted solution]

Hi Everyone,

Finally working with the qvd below I was able to obtain the data I was searching for:

ServerLogFolder/governanceLogContent_7.18.0_file.qvd

Thus for each user session I obtained:

SessionId

App Name

Stream

UserId

OpenApp TimeStamp

CloseApp TimeStamp

Duration

and

SessionId

Sheet

OpenSheet TimeStamp

CloseSheet TimeStamp

Duration

Real Duration

I know that my next step is to adapt the code below for being run without filtering by a specific app id but if it's usefull for anybody here you have:

Unqualify *;

[GovernanceLogContentTemp]:
LOAD
[Id],
[LogEntryPeriodStart],
[LogTimeStamp],
[Service],
[Hostname],
[Message],
[Export Count],
[Export Store Count],
[Description],
[ProxyPackageId],
[RequestSequenceId],
[_date_time_link],
[_proxySessionPackage],
[Reload from Hub],
[TaskExecution],
[TaskId],
[sheet_audit_indicator],
[UserId],
[ObjectId],
Left([ObjectId],36) AS [ObjectIdParent],
Right([ObjectId],Len([ObjectId]) - Index([ObjectId],'|')) AS [ObjectIdSon],
[ObjectName],
Left([ObjectName],Index([ObjectName],'|')-1) AS [ObjectNameParent],
Right([ObjectName],Len([ObjectName]) - Index([ObjectName],'|')) AS [ObjectNameSon],
[ProxySessionId],
[Context],
[Command],
[Result],
[App Name],
[ProductVersion],
[Sequence#],
[qmc_change],
[QMC Resource Type],
[SecurityClass],
[ClientHostAddress],
[Severity],
[ActiveDocSessions],
[ActiveDocs],
[ActiveUsers],
[CPULoad],
[LoadedDocs],
[Selections],
[VMCommitted],
[VMAllocated],
[VMFree],
[VMPctCommitted],
[Cache Hits],
[Cache Lookups],
[Cache Bytes Added],
[Session Count],
[Session Duration],
[Session CPU Spent (ms)],
[Session KBytes Sent+Received],
[Session Selections],
[Reload CPU Spent (ms)],
[Reload KBytes Sent+Received]
FROM [lib://ServerLogFolder/governanceLogContent_7.18.0_file.qvd] (qvd)
WHERE WildMatch([ObjectId],'MYAPPID') > 0
;

[GovernanceLogContentTemp2]:
NoConcatenate
Load
*
RESIDENT [GovernanceLogContentTemp]
WHERE Len(ObjectIdSon) = 36
AND (
(
[ObjectNameSon] = 'Not available'
AND WildMatch([Description],'Command=Open*','Command=Close*') > 0
)
OR
(
Match([ObjectNameSon], 'Not available','Production Report') = 0
AND WildMatch([Description],'Command=Get*') > 0
)
)
ORDER BY [ProxyPackageId] ASC, [LogEntryPeriodStart] DESC
;

DROP TABLE [GovernanceLogContentTemp];

[GovernanceLogContentTemp3]:
NoConcatenate
LOAD *,
Peek([LogEntryPeriodStart]) AS NextLogEntryPeriodStart,
[ProxyPackageId] & '-' & [ObjectIdSon] AS Key
RESIDENT [GovernanceLogContentTemp2];

DROP TABLE [GovernanceLogContentTemp2];

[GovernanceLogContent]:
NoConcatenate
LOAD *, Num(NextLogEntryPeriodStart) - Num([LogEntryPeriodStart]) AS Duration
RESIDENT [GovernanceLogContentTemp3];

DROP TABLE [GovernanceLogContentTemp3];

[Session]:
NoConcatenate
Load [ProxyPackageId],
Min([LogEntryPeriodStart]) As OpenApp,
Max([LogEntryPeriodStart]) As CloseApp
RESIDENT [GovernanceLogContent]
GROUP BY [ProxyPackageId];

[SheetUsage]:
NoConcatenate
LOAD Key,Min([LogEntryPeriodStart]) AS OpenSheet,
Max([NextLogEntryPeriodStart]) AS CloseSheet,
SUM(Duration) AS DurationSheet
RESIDENT [GovernanceLogContent]
WHERE WildMatch([Description],'Command=Open*','Command=Close*') = 0
AND Match([ObjectNameSon], 'Not available','Production Report') = 0
GROUP BY Key;

Regards