Cognos Analytics

I have a client who has recently begun deploying Dashboards.  I was asked to run a query using the Audit database to gather some usage statistics.

What I've noticed is that when a user opens a dashboard many records are written to the COGIPF_RUNREPORT table.  As they interact with the dashboard, many more records are added there.  These appear to track the execution of the individual Visualizations on the Dashboard tabs.

What I'm looking for is a way to just count "opened a dashboard" events.  I can count unique dashboards listed in the RUNREPORT table by session.  But if someone opens the same dashboard 3x in the same session I can't easily tell.  Ideally I'd count that as 3 uses, but today I think I'm stuck counting that as 1 use.

An examination of COGIPF_ACTION has not yielded any records that appear to definitively identify that a user has opened a dashboard either.

Has anyone come up with a clever way to identify each Dashboard "open" to come up with accurate usage statistics?

Thank you!

Rich

------------------------------
Richard Chester
Director of Business Intelligence
LPA Software Solutions
------------------------------

#CognosAnalyticswithWatson

I am interested to know the answer as well. We have been in the same boat in the case of dashboards AND workspaces and have been unsuccessful to date in finding the no. of times our customers are using the dashboards and workspaces. 

Regards

------------------------------
Kiran Passumarthi
www.linkedin.com/in/kiranpassumarthi
------------------------------

Original Message:
Sent: Mon February 01, 2021 11:43 AM
From: Richard Chester
Subject: Counting Number of Dashboards Accessed

I have a client who has recently begun deploying Dashboards.  I was asked to run a query using the Audit database to gather some usage statistics.

What I've noticed is that when a user opens a dashboard many records are written to the COGIPF_RUNREPORT table.  As they interact with the dashboard, many more records are added there.  These appear to track the execution of the individual Visualizations on the Dashboard tabs.

What I'm looking for is a way to just count "opened a dashboard" events.  I can count unique dashboards listed in the RUNREPORT table by session.  But if someone opens the same dashboard 3x in the same session I can't easily tell.  Ideally I'd count that as 3 uses, but today I think I'm stuck counting that as 1 use.

An examination of COGIPF_ACTION has not yielded any records that appear to definitively identify that a user has opened a dashboard either.

Has anyone come up with a clever way to identify each Dashboard "open" to come up with accurate usage statistics?

Thank you!

Rich

------------------------------
Richard Chester
Director of Business Intelligence
LPA Software Solutions
------------------------------

#CognosAnalyticswithWatson

Hi Richard,

Auditing has always been needlessly difficult in Cognos.

I also use the COGIPF_RUNREPORT table to do this counting. What I do is:
count ( distinct [Request ID] + [Report name] )

where I perform a trim on the [Report name].
In case of [Report type] <> 'Exploration queryservice' then [Request ID] is: [Audit].[Run Reports].[Request ID]
But in case [Report type] = 'Exploration queryservice' the [Request ID] is as follows:

substring ( [Audit].[Run Reports].[Request ID]; 1 ; position ( ':'; [Audit].[Run Reports].[Request ID] ) )
+
substring (
[Audit].[Run Reports].[Request ID];
position ( ':'; [Audit].[Run Reports].[Request ID] ) + 1 ;
position ( ':';
substring ( [Audit].[Run Reports].[Request ID];
position ( ':'; [Audit].[Run Reports].[Request ID] ) + 1
)
)
)

What I noticed is that the RequestID for a dashboard contains two colons. When you take the part before the second colon,
you have the unique request ID for the dashboard. I have discovered it myself, but I'm pretty sure it is correct.

Example:
-157673f9:174f853e0d3:7bdd
Convert it to:
-157673f9:174f853e0d3:

------------------------------
Thomas van der Meer
------------------------------

Original Message:
Sent: Mon February 01, 2021 11:43 AM
From: Richard Chester
Subject: Counting Number of Dashboards Accessed

I have a client who has recently begun deploying Dashboards.  I was asked to run a query using the Audit database to gather some usage statistics.

What I've noticed is that when a user opens a dashboard many records are written to the COGIPF_RUNREPORT table.  As they interact with the dashboard, many more records are added there.  These appear to track the execution of the individual Visualizations on the Dashboard tabs.

What I'm looking for is a way to just count "opened a dashboard" events.  I can count unique dashboards listed in the RUNREPORT table by session.  But if someone opens the same dashboard 3x in the same session I can't easily tell.  Ideally I'd count that as 3 uses, but today I think I'm stuck counting that as 1 use.

An examination of COGIPF_ACTION has not yielded any records that appear to definitively identify that a user has opened a dashboard either.

Has anyone come up with a clever way to identify each Dashboard "open" to come up with accurate usage statistics?

Thank you!

Rich

------------------------------
Richard Chester
Director of Business Intelligence
LPA Software Solutions
------------------------------

#CognosAnalyticswithWatson

Thank you Thomas - I'll give that a try!

Rich

------------------------------
Richard Chester
Director of Business Intelligence
LPA Software Solutions
------------------------------

Original Message:
Sent: Tue February 02, 2021 03:38 AM
From: Thomas van der Meer
Subject: Counting Number of Dashboards Accessed

Hi Richard,

Auditing has always been needlessly difficult in Cognos.

I also use the COGIPF_RUNREPORT table to do this counting. What I do is:
count ( distinct [Request ID] + [Report name] )

where I perform a trim on the [Report name].
In case of [Report type] <> 'Exploration queryservice' then [Request ID] is: [Audit].[Run Reports].[Request ID]
But in case [Report type] = 'Exploration queryservice' the [Request ID] is as follows:

substring ( [Audit].[Run Reports].[Request ID]; 1 ; position ( ':'; [Audit].[Run Reports].[Request ID] ) )
+
substring (
[Audit].[Run Reports].[Request ID];
position ( ':'; [Audit].[Run Reports].[Request ID] ) + 1 ;
position ( ':';
substring ( [Audit].[Run Reports].[Request ID];
position ( ':'; [Audit].[Run Reports].[Request ID] ) + 1
)
)
)

What I noticed is that the RequestID for a dashboard contains two colons. When you take the part before the second colon,
you have the unique request ID for the dashboard. I have discovered it myself, but I'm pretty sure it is correct.

Example:
-157673f9:174f853e0d3:7bdd
Convert it to:
-157673f9:174f853e0d3:

------------------------------
Thomas van der Meer

Original Message:
Sent: Mon February 01, 2021 11:43 AM
From: Richard Chester
Subject: Counting Number of Dashboards Accessed

I have a client who has recently begun deploying Dashboards.  I was asked to run a query using the Audit database to gather some usage statistics.

What I've noticed is that when a user opens a dashboard many records are written to the COGIPF_RUNREPORT table.  As they interact with the dashboard, many more records are added there.  These appear to track the execution of the individual Visualizations on the Dashboard tabs.

What I'm looking for is a way to just count "opened a dashboard" events.  I can count unique dashboards listed in the RUNREPORT table by session.  But if someone opens the same dashboard 3x in the same session I can't easily tell.  Ideally I'd count that as 3 uses, but today I think I'm stuck counting that as 1 use.

An examination of COGIPF_ACTION has not yielded any records that appear to definitively identify that a user has opened a dashboard either.

Has anyone come up with a clever way to identify each Dashboard "open" to come up with accurate usage statistics?

Thank you!

Rich

------------------------------
Richard Chester
Director of Business Intelligence
LPA Software Solutions
------------------------------

#CognosAnalyticswithWatson

Hi Richard,
After upgrading to version 11.2.0 I noticed that the audit logging for dashboards has changed (again).
I still use the same logic as before, but the RequestID for 11.2.0 dashboards has only 1 colon. In version 11.1.2 it had two.
So I now check for 1 of 2 occurences of a colon. In case there are two colons I take the part before the second colon. In case there is one colon I take the part before the first colon. And that works as expected.

If the RequestID has a format like below, then the dashboard is created in a earlier version and converted to 11.2.0. After saving the dashboard it will no longer having this format.
rootId=2C99wjCswvssGd9jq4qjhv98sy, parentId=2C99wjCswvssGd9jq4qjhv98sy, currentId=hvhw8h2jMG8Gv8488jlC9svw9w9MsMsqd

------------------------------
Thomas van der Meer
------------------------------

Original Message:
Sent: Wed February 03, 2021 07:50 AM
From: Richard Chester
Subject: Counting Number of Dashboards Accessed

Thank you Thomas - I'll give that a try!

Rich

------------------------------
Richard Chester
Director of Business Intelligence
LPA Software Solutions

Original Message:
Sent: Tue February 02, 2021 03:38 AM
From: Thomas van der Meer
Subject: Counting Number of Dashboards Accessed

Hi Richard,

Auditing has always been needlessly difficult in Cognos.

I also use the COGIPF_RUNREPORT table to do this counting. What I do is:
count ( distinct [Request ID] + [Report name] )

where I perform a trim on the [Report name].
In case of [Report type] <> 'Exploration queryservice' then [Request ID] is: [Audit].[Run Reports].[Request ID]
But in case [Report type] = 'Exploration queryservice' the [Request ID] is as follows:

substring ( [Audit].[Run Reports].[Request ID]; 1 ; position ( ':'; [Audit].[Run Reports].[Request ID] ) )
+
substring (
[Audit].[Run Reports].[Request ID];
position ( ':'; [Audit].[Run Reports].[Request ID] ) + 1 ;
position ( ':';
substring ( [Audit].[Run Reports].[Request ID];
position ( ':'; [Audit].[Run Reports].[Request ID] ) + 1
)
)
)

What I noticed is that the RequestID for a dashboard contains two colons. When you take the part before the second colon,
you have the unique request ID for the dashboard. I have discovered it myself, but I'm pretty sure it is correct.

Example:
-157673f9:174f853e0d3:7bdd
Convert it to:
-157673f9:174f853e0d3:

------------------------------
Thomas van der Meer

Original Message:
Sent: Mon February 01, 2021 11:43 AM
From: Richard Chester
Subject: Counting Number of Dashboards Accessed

I have a client who has recently begun deploying Dashboards.  I was asked to run a query using the Audit database to gather some usage statistics.

What I've noticed is that when a user opens a dashboard many records are written to the COGIPF_RUNREPORT table.  As they interact with the dashboard, many more records are added there.  These appear to track the execution of the individual Visualizations on the Dashboard tabs.

What I'm looking for is a way to just count "opened a dashboard" events.  I can count unique dashboards listed in the RUNREPORT table by session.  But if someone opens the same dashboard 3x in the same session I can't easily tell.  Ideally I'd count that as 3 uses, but today I think I'm stuck counting that as 1 use.

An examination of COGIPF_ACTION has not yielded any records that appear to definitively identify that a user has opened a dashboard either.

Has anyone come up with a clever way to identify each Dashboard "open" to come up with accurate usage statistics?

Thank you!

Rich

------------------------------
Richard Chester
Director of Business Intelligence
LPA Software Solutions
------------------------------

#CognosAnalyticswithWatson

Original Message:
Sent: 2/1/2021 11:44:00 AM
From: Richard Chester
Subject: Counting Number of Dashboards Accessed

I have a client who has recently begun deploying Dashboards.  I was asked to run a query using the Audit database to gather some usage statistics.

What I've noticed is that when a user opens a dashboard many records are written to the COGIPF_RUNREPORT table.  As they interact with the dashboard, many more records are added there.  These appear to track the execution of the individual Visualizations on the Dashboard tabs.

What I'm looking for is a way to just count "opened a dashboard" events.  I can count unique dashboards listed in the RUNREPORT table by session.  But if someone opens the same dashboard 3x in the same session I can't easily tell.  Ideally I'd count that as 3 uses, but today I think I'm stuck counting that as 1 use.

An examination of COGIPF_ACTION has not yielded any records that appear to definitively identify that a user has opened a dashboard either.

Has anyone come up with a clever way to identify each Dashboard "open" to come up with accurate usage statistics?

Thank you!

Rich

------------------------------
Richard Chester
Director of Business Intelligence
LPA Software Solutions
------------------------------

#CognosAnalyticswithWatson

Thank you Peter. I'll give that a look!

Rich

------------------------------
Richard Chester
Director of Business Intelligence
LPA Software Solutions
------------------------------

Original Message:
Sent: Tue February 02, 2021 07:18 AM
From: Peter Chan
Subject: Counting Number of Dashboards Accessed

Richard Chester – I agree, counting access is a bit of a challenge.  What we ended up doing is counting the distinct times a dashboard was accessed within a second and get rid of the milliseconds.  It is not accurate, but does reduce the access count of a dashboard dramatically.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Peter Chan

613-295--0428

 

Original Message:
Sent: 2/1/2021 11:44:00 AM
From: Richard Chester
Subject: Counting Number of Dashboards Accessed

I have a client who has recently begun deploying Dashboards.  I was asked to run a query using the Audit database to gather some usage statistics.

What I've noticed is that when a user opens a dashboard many records are written to the COGIPF_RUNREPORT table.  As they interact with the dashboard, many more records are added there.  These appear to track the execution of the individual Visualizations on the Dashboard tabs.

What I'm looking for is a way to just count "opened a dashboard" events.  I can count unique dashboards listed in the RUNREPORT table by session.  But if someone opens the same dashboard 3x in the same session I can't easily tell.  Ideally I'd count that as 3 uses, but today I think I'm stuck counting that as 1 use.

An examination of COGIPF_ACTION has not yielded any records that appear to definitively identify that a user has opened a dashboard either.

Has anyone come up with a clever way to identify each Dashboard "open" to come up with accurate usage statistics?

Thank you!

Rich

------------------------------
Richard Chester
Director of Business Intelligence
LPA Software Solutions
------------------------------

#CognosAnalyticswithWatson