ReportServer Forum

zach.mathew

Member

Registered: 2015-04-17

How do I check stats on report execution ?

For example which is the most used report so far, most used report daily etc. Need to gather stats on report usage.

Is there a particular table in the DB that I can query for this ?

Thanks,

Zach

Sai Prasad

Member

Registered: 2017-09-14

Re: How do I check stats on report execution ?

@Zach.mathew

I'm using below query to see report execution results

SELECT DISTINCT (E.DATE_FIELD) AS ex_end, P.VALUE, R.NAME_FIELD, r.KEY_FIELD
FROM
bitnami_reportserver.RS_AUDIT_LOG_ENTRY E,
bitnami_reportserver.RS_AUDIT_LOG_PROPERTY P,
bitnami_reportserver.RS_REPORT R ,
bitnami_reportserver.RS_DATASOURCE_CONTAINER_A Q,
bitnami_reportserver.RS_DATABASE_DATASOURCE_A T
WHERE E.ENTITY_ID = P.LOG_ENTRY_ID
AND P.VALUE IS NOT NULL
AND P.VALUE = CAST(R.ID AS CHAR)
AND Q.ENTITY_ID = R.DATASOURCE_CONTAINER_ID
AND T.ID = Q.DATASOURCE_ID AND T.URL IS NOT null
AND E.ACTION = 'END_REPORT_EXECUTION'
AND T.url <> 'rs:demodata'

zach.mathew

Member

Registered: 2015-04-17

Re: How do I check stats on report execution ?

Hi Sai,

Thanks for you reply. I ran your query in my DB with the default schema name (public) and have no results. Am I missing something here ??

Thanks,

Zach

eduardo

Administrator

Registered: 2016-11-01

Website

Re: How do I check stats on report execution ?

Hi Zach,

you can use the audit log tables for this. A query is:

select E.*, P.KEY_FIELD, P.VALUE
FROM RS_AUDIT_LOG_ENTRY E,
RS_AUDIT_LOG_PROPERTY P
WHERE
E.ENTITY_ID = P.LOG_ENTRY_ID

You should already have an audit dynamic list in your reportserver installation. If not, you can easily create one with the query I posted.

Regards,
Eduardo

zach.mathew

Member

Registered: 2015-04-17

Re: How do I check stats on report execution ?

Hi Eduardo,

Thanks for your reply. I used both your queries and wrote this to check counts of report execution.

Does this look ok ?

SELECT DISTINCT R.id, R.name_field AS report_name ,count (R.id)
  
  FROM RS_AUDIT_LOG_ENTRY E,
           RS_AUDIT_LOG_PROPERTY P,
           RS_REPORT_A R
 WHERE E.ENTITY_ID = P.LOG_ENTRY_ID
   AND E.ACTION LIKE 'END_REPORT_EXECUTION'
   AND R.id::text = P.value
   AND P.key_field LIKE 'report_id'
   AND name_field IS NOT NULL
   AND date_field > '2019-09-01'
  
   GROUP BY 1,2
   ORDER BY 3 DESC 

Thanks,

Zach