ReportServer Forum

DISSTeam

Member

Registered: 2020-09-11

Produce an internal report?

Can you produce internal statistics to show, for example, how many times a report has been ran in total and break it down by users, weeks, months, years, etc if necessary?
We like to see what reports are being effectively utilised a lot and which ones aren't. We also would like to be able to see when a user specifically ran a report if we delve in to individual users.

Can we do this natively or through a module? Or even a report?

We are very close to buying this software and we are keen to get these ironed out before spending the money on full enterprise.

Thanks,

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Produce an internal report?

Hi DISSTeam,

all these statistics can be already found in the audit logs of ReportServer. In your ReportServer installation you should already have an "Audit Log" dynamic list.
If not, you can create one with this query:

 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

Pls check the tables in order to see what information are available.

Currently, we are working on a statistics-dashboard (the ticket for this is RS-4352), which will show some report execution statistics, like "top 10 execution duration", or "top 10 system load", which will show slow / reports being executed very frequently, etc, so exactly what you are asking for I will update here when we have more information on this statistics-dashboard, we are currently working on it.

In the meanwhile, you can take a look at the query this dashboard will be based on (you can create a dynamic list based on this query):

You only need ${startDate} and  ${endDate} parameters for analyzing the executions during a given time interval (you can enter the values in this format: '2020-05-14 16:16:01'). In the script below you have four cases:

Case 1: Reports that started and ended execution inside given interval
Case 2: Reports that started execution inside given interval and didn't finish execution inside this interval
Case 3: Reports that start execution before given interval and end execution inside given interval
Case 4: Report that started execution before the interval and ended execution after interval

SELECT * FROM

(

  SELECT DISTINCT 

            D.REPORT_ID AS EXECUTION_REPORT_ID
          , D.REPORT_NAME AS EXECUTION_REPORT_NAME
          , D.REPORT_BEGIN AS EXECUTION_BEGIN
          , D.REPORT_END AS EXECUTION_END
          , D.REPORT_SUCCESS AS EXECUTION_SUCCESS
          , D.OUTPUT_FORMAT AS EXECUTION_OUTPUT_FORMAT
          , D.LEFTSIDEINFO
          , D.RIGHTSIDEINFO
          , CASE WHEN R.REPORT_ID IS NULL THEN D.REPORT_ID ELSE R.REPORT_ID END AS REPORT_ID
          , CASE WHEN R.REPORT_NAME IS NULL THEN D.REPORT_NAME ELSE R.REPORT_NAME END AS REPORT_NAME

          , CASE 
            WHEN D.LEFTSIDEINFO = 'START AFTER BEGINDATE'   AND D.RIGHTSIDEINFO = 'END BEFORE ENDDATE'                THEN 'CASE 1' 
            WHEN D.LEFTSIDEINFO = 'START AFTER BEGINDATE'   AND D.RIGHTSIDEINFO IN ('END AFTER ENDDATE', 'NO END')    THEN 'CASE 2'
            WHEN D.LEFTSIDEINFO = 'START BEFORE BEGINDATE'  AND D.RIGHTSIDEINFO = 'END BEFORE ENDDATE'                THEN 'CASE 3'
            WHEN D.LEFTSIDEINFO = 'START BEFORE BEGINDATE'  AND D.RIGHTSIDEINFO IN ('END AFTER ENDDATE', 'NO END')    THEN 'CASE 4'
          ELSE
            'NOT_RELEVANT'
          END AS EXECUTION_TYPE

  FROM

  (
  	SELECT DISTINCT
  		  E.REPORT_ID
  		, R.NAME_FIELD AS REPORT_NAME
  		, E.REPORT_BEGIN
  		, E.REPORT_END
  		-- , E.REPORT_END - E.REPORT_BEGIN   AS RUNTIME
  		, E.REPORT_SUCCESS
  		, E.OUTPUT_FORMAT

          , CASE 
              WHEN E.REPORT_BEGIN >=   ${endDate} THEN   'START AFTER ENDDATE' 
              WHEN E.REPORT_BEGIN >= ${startDate} THEN   'START AFTER BEGINDATE'  
          ELSE 'START BEFORE BEGINDATE' END LEFTSIDEINFO

          , CASE 
              WHEN E.REPORT_END <= ${startDate} THEN 'END BEFORE STARTDATE' 
              WHEN E.REPORT_END <= ${endDate}   THEN 'END BEFORE ENDDATE' 
              WHEN E.REPORT_END IS NULL THEN 'NO END'
          ELSE 'END AFTER ENDDATE' END RIGHTSIDEINFO

  		
  	FROM
  	(
  	 SELECT
  		  E.UUID
  		, E.REPORT_ID
  		, MAX( CASE WHEN E.ACTION = 'BEGIN_REPORT_EXECUTION' THEN E.DATE_FIELD ELSE NULL END ) 		AS REPORT_BEGIN
  		, MAX( CASE WHEN E.ACTION = 'END_REPORT_EXECUTION'   THEN E.DATE_FIELD ELSE NULL END )  	AS REPORT_END
  		, MAX( CASE WHEN E.ACTION = 'END_REPORT_EXECUTION'   THEN E.SUCCESS ELSE NULL END )     	AS REPORT_SUCCESS
  		, MAX( CASE WHEN E.ACTION = 'REPORT_EXECUTION'       THEN E.OUTPUT_FORMAT ELSE NULL END ) 	AS OUTPUT_FORMAT
  	 FROM
  		(
  			SELECT 
  				  E.ENTITY_ID
  				, E.ACTION 
  				, E.DATE_FIELD
  				, MAX( CASE WHEN P.KEY_FIELD = 'report_id'
  					AND CAST(P.VALUE AS CHAR) <> 'NULL'			THEN CAST(P.VALUE AS CHAR) ELSE NULL END )  	AS REPORT_ID
  				, MAX( CASE WHEN P.KEY_FIELD = 'success'			THEN CAST(P.VALUE AS CHAR) ELSE NULL END )  													AS SUCCESS
  				, MAX( CASE WHEN P.KEY_FIELD = 'uuid'				THEN CAST(P.VALUE AS CHAR) ELSE NULL END )  													AS UUID
  				, MAX( CASE WHEN P.KEY_FIELD = 'output_format'			THEN CAST(P.VALUE AS CHAR) ELSE NULL END )  													AS OUTPUT_FORMAT
  			  FROM RS_AUDIT_LOG_ENTRY E INNER JOIN RS_AUDIT_LOG_PROPERTY P ON E.ENTITY_ID = P.LOG_ENTRY_ID
  			 WHERE E.ACTION in ('REPORT_EXECUTION','BEGIN_REPORT_EXECUTION','END_REPORT_EXECUTION'/*,'REPORT_EXECUTED_FAILED'*/) 
  			   AND P.KEY_FIELD IN ( 'report_id', 'success', 'uuid', 'output_format')
  			 GROUP BY 
  				  E.ENTITY_ID
  				, E.ACTION 
  				, E.DATE_FIELD
  		) E
  	GROUP BY 
  		  E.UUID
  		, E.REPORT_ID
  	) E 


  	LEFT OUTER JOIN RS_REPORT R ON CAST( R.ID AS CHAR ) = E.REPORT_ID
    
    WHERE
  	E.OUTPUT_FORMAT NOT IN( 'RS_DATACOUNT', 'RS_METADATA' )  
    
  ) D

  LEFT OUTER JOIN (
      SELECT 
        R.ID                    AS VARIANT_ID, 
        R.NAME_FIELD            AS VARIANT_NAME, 
        R_P.ID                  AS REPORT_ID, 
        R_P.NAME_FIELD          AS REPORT_NAME FROM

        RS_REPORT_MNGR_NODE N
        LEFT OUTER JOIN RS_REPORT R ON N.ENTITY_ID = R.ID
        LEFT OUTER JOIN RS_REPORT R_P ON N.PARENT_ID = R_P.ID

  ) R 
  ON D.REPORT_ID = R.VARIANT_ID

  WHERE 
      CASE 
          WHEN D.LEFTSIDEINFO = 'START AFTER BEGINDATE'   AND D.RIGHTSIDEINFO = 'END BEFORE ENDDATE'              THEN 'CASE 1' 
          WHEN D.LEFTSIDEINFO = 'START AFTER BEGINDATE'   AND D.RIGHTSIDEINFO IN ('END AFTER ENDDATE', 'NO END')  THEN 'CASE 2'
          WHEN D.LEFTSIDEINFO = 'START BEFORE BEGINDATE'  AND D.RIGHTSIDEINFO = 'END BEFORE ENDDATE'              THEN 'CASE 3'
          WHEN D.LEFTSIDEINFO = 'START BEFORE BEGINDATE'  AND D.RIGHTSIDEINFO IN ('END AFTER ENDDATE', 'NO END')  THEN 'CASE 4'
      ELSE
          'NOT_RELEVANT'
      END 
      <> 'NOT_RELEVANT'

) V

Regards,
Eduardo

DISSTeam

Member

Registered: 2020-09-11

Re: Produce an internal report?

Thank you very much!