Skip to Content

Assessing the BMC Helix ITSM: Smart Reporting environment

As a database administrator, before migrating the reports to BMC Helix Dashboards, assess the BMC Helix ITSM: Smart Reporting environment to count the total number of reports, the total number of private and public reports, and so on.

You can run a set of SQL queries in the Smart Reporting database to collect various metrics. If you are unable to access the Smart Reporting database, as a BMC Helix ITSM: Smart Reporting SaaS user, use Advanced Authoring to access the Smart Reporting database. For more information, see How to get a list of reports for the Conversion Tool with Advanced Authoring.

Sample queries to collect metrics

  1. Log in to the BMC Helix ITSM: Smart Reporting database by providing the following details:
    • Database type
    • Database hostname
    • Database instance
    • Database port
    • Database name
    • Database username
    • Database user's password

  2. In the database query panel, run the following queries based on the database type:

    Metrics

    Description

    Microsoft SQL/PostgreSQL/Oracle SQL queries

    report_permissions_groups

    Displays the report permissions by groups.

    select ReportId, ReportName ,GroupTypeCode, ShortDescription
    from ReportHeader,  AccessGroup
    where ReportHeader.IpOrg = AccessGroup.IpOrg AND ReportStatusCode ='OPEN' AND ReportName !='Draft Report' AND RoleCode !='SUBQUERY'

    report_permissions_users

    Displays the report permissions by users.

    select ReportId, ReportName,  AccessLevelCode, AccessSubjectCode
    from ReportHeader, Person, AccessLevel
    where ReportHeader.IpOwner = Person.IpPerson and ReportHeader. ReportId = AccessLevel.EntityId AND ReportStatusCode ='OPEN' AND ReportName !='Draft Report' AND RoleCode !='SUBQUERY'

    public_or_private

    Displays the reports by report types.

    select ReportId, ReportName ,  'Public' as Public_Or_Private
    from ReportHeader Left Outer Join Person On ReportHeader.Ipcreator = Person.IpPerson
    where ExternalAccessCode ='CORPORATE' AND ReportStatusCode ='OPEN' AND ReportName !='Draft Report' AND RoleCode !='SUBQUERY'
    Union
    Select ReportId, ReportName , 'Private' as Public_Or_Private
    from ReportHeader Left Outer Join Person On ReportHeader.Ipcreator = Person.IpPerson
    where ExternalAccessCode ='PERSONAL' AND ReportStatusCode ='OPEN' AND ReportName !='Draft Report' AND RoleCode !='SUBQUERY'

    view_name

    Displays the list of views.

    select distinct Viewat.VIEWID AS Viewid, Viewat.VIEWDESCRIPTION AS ViewName, Report.REPORTID AS Reportid , Report.ReportName
    FROM REPORTHEADER Report LEFT OUTER JOIN REPORTVIEW Viewat ON (    Report.VIEWID = Viewat.VIEWID )
    where  ReportStatusCode ='OPEN' AND ReportName !='Draft Report' AND RoleCode !='SUBQUERY'

    list_of_top_users

    Displays the list of the top user count who accessed the reports.

    select distinct COUNT(Events.EVENTID) As Usage_Count  
    FROM ( select * from Event UNION ALL  select * from EventArchive ) Events INNER JOIN PERSON  ON (    Events.IPSOURCE = Person.IPPERSON )
    where (     Events.EVENTCODE = 'LOGIN' )
    GROUP BY     Person.FULLNAME  Order By COUNT(Events.EVENTID) DESC

    list of top users with user name

    Displays the list of the top users who accessed the reports.

    Microsoft SQL query:

    SELECT DISTINCT COUNT(Events.EVENTID) As Usage_Count , Person.FULLNAME
    FROM ( select * from Event UNION ALL  select * from EventArchive ) Events INNER JOIN PERSON  ON (    Events.IPSOURCE = Person.IPPERSON )
    WHERE (     Events.EVENTCODE = 'LOGIN' )
    GROUP BY     Person.FULLNAME  Order By COUNT(Events.EVENTID) DESC

    Oracle SQL query:

    SELECT DISTINCT COUNT(Events.EVENTID) As Usage_Count , Person.FULLNAME
    FROM ( select * from Event UNION ALL  select * from EventArchive ) Events INNER JOIN PERSON  ON (    Events.IPSOURCE = Person.IPPERSON )
    WHERE (     Events.EVENTCODE = 'LOGIN' )
    GROUP BY     Person.FULLNAME  Order By COUNT(Events.EVENTID) DESC

    reports_not_run_last_days

    Displays reports when it was last run.

    Microsoft SQL query:

    SELECT DISTINCT Report.REPORTID, Report.HITCOUNT, Report.PUBLISHUUID, Report.REPORTNAME, Max(Events.EVENTDATE ) AS 'Max EventDate'
    FROM (
    select * from Event
    UNION ALL  
    Select * from EventArchive ) Events LEFT OUTER JOIN REPORTHEADER Report ON (    Events.CONTENTID = Report.REPORTID ) AND (    Report.ROLECODE != 'SUBQUERY' )
    WHERE ( Events.EVENTDATE <= GETUTCDATE()-365  AND   Events.EVENTTYPECODE != 'SYSTEMTASK' ) AND (    Events.EVENTTYPECODE = 'REPORT'    AND Events.EVENTCODE in ('RPTRUN','DASHRUN')    AND Report.REPORTSTATUSCODE = 'OPEN' )
    group by Report.REPORTID,  Report.HITCOUNT ,    Report.PUBLISHUUID ,    Report.REPORTNAME  
    Order By 1 DESC

    PostgreSQL query:

    SELECT DISTINCT Report.REPORTID, Report.HITCOUNT, Report.PUBLISHUUID, Report.REPORTNAME, Max(Events.EVENTDATE ) AS 'Max EventDate'
    FROM (
    select * from Event
    UNION ALL  
    Select * from EventArchive
    ) Events LEFT OUTER JOIN REPORTHEADER Report ON (    Events.CONTENTID = Report.REPORTID ) AND (    Report.ROLECODE != 'SUBQUERY' )
    WHERE ( Events.EVENTDATE <= (NOW() - interval '365 day')  AND   Events.EVENTTYPECODE != 'SYSTEMTASK' ) AND (    Events.EVENTTYPECODE = 'REPORT'    AND Events.EVENTCODE in ('RPTRUN','DASHRUN')    AND Report.REPORTSTATUSCODE = 'OPEN' ) group by Report.REPORTID,  Report.HITCOUNT ,    Report.PUBLISHUUID ,    Report.REPORTNAME  
    Order By 1 DESC

    Oracle SQL query:

    SELECT DISTINCT Report.REPORTID, Report.HITCOUNT, Report.PUBLISHUUID, Report.REPORTNAME, Max(Events.EVENTDATE ) AS "Max EventDate"
    FROM (
    select * from Event
    UNION ALL  
    Select * from EventArchive ) Events LEFT OUTER JOIN REPORTHEADER Report ON (    Events.CONTENTID = Report.REPORTID ) AND (    Report.ROLECODE != 'SUBQUERY' )
    WHERE ( Events.EVENTDATE <= SYSDATE-365  AND   Events.EVENTTYPECODE != 'SYSTEMTASK' ) AND (    Events.EVENTTYPECODE = 'REPORT'    AND Events.EVENTCODE in ('RPTRUN','DASHRUN')    AND Report.REPORTSTATUSCODE = 'OPEN' )
    group by Report.REPORTID,  Report.HITCOUNT ,    Report.PUBLISHUUID ,    Report.REPORTNAME  
    Order By 1 DESC

    reports_most_frequent_Schedule_broadcast_run

    Displays the list of reports by the number of scheduled broadcasts.

    select Count(Broadcastid) as Broadcast_Count, Reportname ,Reportheader.Reportid
    from ReportBroadcast,ReportHeader  
    where ReportBroadcast.ReportId=ReportHeader.ReportId  AND ReportStatusCode ='OPEN' AND ReportName !='Draft Report' AND RoleCode !='SUBQUERY'
    group by ReportHeader.ReportName,Reportheader.Reportid

    list_of_least_used_dashboards

    Displays the list of least used dashboards.

    select distinct Dashboard.SHORTDESCRIPTION, Dashboard.HITCOUNT
    FROM ReportGroup Dashboard
    where ( Dashboard.STATUSCODE IN ('OPEN') ) GROUP BY Dashboard.HITCOUNT, Dashboard.SHORTDESCRIPTION
    ORDER BY Dashboard.HITCOUNT ASC

    list_of_least_used_reports

    Displays the list of least used reports.

    select distinct Report.REPORTID, Report.HITCOUNT, Report.PUBLISHUUID, Report.REPORTNAME
    FROM (    
    select * from Event
    UNION ALL  Select * from EventArchive
    ) Events LEFT OUTER JOIN REPORTHEADER Report ON (    Events.CONTENTID = Report.REPORTID) AND (    Report.ROLECODE != 'SUBQUERY' )
    where (   Events.EVENTTYPECODE != 'SYSTEMTASK' ) AND (    Events.EVENTTYPECODE = 'REPORT'    AND Events.EVENTCODE in ('RPTRUN','DASHRUN')    AND Report.REPORTSTATUSCODE = 'OPEN' )
    Order By 1 ASC

    list_of_most_used_dashboards

    Displays the list of most used dashboards.

    select distinct Dashboard.SHORTDESCRIPTION,    Dashboard.HITCOUNT
    FROM ReportGroup Dashboard
    where (    Dashboard.STATUSCODE IN ('OPEN') )
    GROUP BY     Dashboard.HITCOUNT,    Dashboard.SHORTDESCRIPTION
    ORDER BY Dashboard.HITCOUNT DESC

    list_of_most_used_reports

    Displays the list of most used reports.

    Microsoft SQL query:

    select  distinct    Report.REPORTID, Report.HITCOUNT,    Report.PUBLISHUUID,    Report.REPORTNAME
    FROM    (
            select * from Event
            UNION ALL
            Select * from EventArchive
            ) Events LEFT OUTER JOIN REPORTHEADER Report ON (    Events.CONTENTID = Report.REPORTID ) AND (    Report.ROLECODE != 'SUBQUERY' )
    where   Events.EVENTTYPECODE != 'SYSTEMTASK'  AND (Events.EVENTDATE <= GETUTCDATE()-31)  AND Events.EVENTTYPECODE = 'REPORT'    AND
            Events.EVENTCODE in ('RPTRUN','DASHRUN')    AND Report.REPORTSTATUSCODE = 'OPEN'
    Order By 1 DESC

    PostgreSQL query:

    select distinct Report.REPORTID,   Report.HITCOUNT,    Report.PUBLISHUUID,    Report.REPORTNAME  
    FROM (    
    select * from Event
    UNION ALL  
    Select * from EventArchive
    ) Events LEFT OUTER JOIN REPORTHEADER Report ON (    Events.CONTENTID = Report.REPORTID ) AND (    Report.ROLECODE != 'SUBQUERY' )
    where (     Events.EVENTTYPECODE != 'SYSTEMTASK' ) AND Events.EVENTDATE <=  (NOW() - interval '31 day') AND (    Events.EVENTTYPECODE = 'REPORT'    AND Events.EVENTCODE in ('RPTRUN','DASHRUN')    AND Report.REPORTSTATUSCODE = 'OPEN' )
    Order By 1 DESC

    Oracle SQL query:

    SELECT  DISTINCT    Report.REPORTID, Report.HITCOUNT,    Report.PUBLISHUUID,    Report.REPORTNAME
    FROM    (
            select * from Event
            UNION ALL
            Select * from EventArchive
            ) Events LEFT OUTER JOIN REPORTHEADER Report ON (    Events.CONTENTID = Report.REPORTID ) AND (    Report.ROLECODE != 'SUBQUERY' )
    WHERE   Events.EVENTTYPECODE != 'SYSTEMTASK'  AND (Events.EVENTDATE <= SYSDATE-31)  AND Events.EVENTTYPECODE = 'REPORT'    AND
            Events.EVENTCODE in ('RPTRUN','DASHRUN')    AND Report.REPORTSTATUSCODE = 'OPEN'
    Order By 1 DESC

    dashboard_permissions

    Displays the permission details of each dashboard.

    select distinct RoleCode,FunctionCode,staffmemberrole.enddate
    from StaffMemberRole,OrgFunction
    where StaffMemberRole.IpEmployer = OrgFunction.IpOrg and OrgFunction.FunctionCode IN ('DASHREFRESH','DASHPUBLIC') AND EndDate='31-DEC-9999'

    public_or_private_dashboards

    Displays the dashboards by type.

    select ShortDescription,AccessCode
    FROM ReportGroup
    where ShortDescription like '%Dashboard%'  and AccessCode='ACCESSLEVEL' and StatusCode='OPEN'

    associated_reports

    Displays the list of reports that have co-display reports.

    select distinct Reportgroup.GroupId, Reportgroup.ShortDescription, Reportheader.reportid,ReportHeader.ReportName
    from ReportGroup, ReportAssociate, ReportHeader
    where ReportGroup. GroupId= ReportAssociate.ReportGroupId AND ReportAssociate.ChildReportId = ReportHeader.ReportId
    Order by ReportGroup.ShortDescription

    dashboard_status

    Displays dashboard details with their status codes, such as OPEN/ARCHIVE/DELETED, and so on.

    Microsoft SQL query:

    SELECT DISTINCT    Dashboard.STATUSCODE,   Dashboard.SHORTDESCRIPTION,     Dashboard.STARTDATE,    MAX(Dashboard.LASTMODIFIEDGMTDATETIME) AS 'Max LastModifiedDate',   dashboard.groupid
    FROM REPORTGROUP Dashboard LEFT OUTER JOIN PERSON Dashboard_Modifier ON (    Dashboard.IPLASTMODIFIER = Dashboard_Modifier.IPPERSON ) LEFT OUTER JOIN PERSON Dashboard_Creator ON (    Dashboard.IPCREATOR = Dashboard_Creator.IPPERSON )
    WHERE (    Dashboard.GROUPTYPECODE != 'SUBTAB'    AND Dashboard.STATUSCODE != 'ARCHIVED'    AND Dashboard.STATUSCODE = 'OPEN' )
    GROUP BY     Dashboard.STATUSCODE,   Dashboard_Creator.FULLNAME,   Dashboard_Modifier.FULLNAME,    Dashboard.SHORTDESCRIPTION,   Dashboard.STATUSCODE,   Dashboard.STARTDATE,   dashboard.groupid

    PostgreSQL query:

    SELECT DISTINCT    Dashboard.STATUSCODE,   Dashboard.SHORTDESCRIPTION,     Dashboard.STARTDATE,    MAX(Dashboard.LASTMODIFIEDGMTDATETIME) AS 'Max LastModifiedDate',   dashboard.groupid
    FROM REPORTGROUP Dashboard LEFT OUTER JOIN PERSON Dashboard_Modifier ON (    Dashboard.IPLASTMODIFIER = Dashboard_Modifier.IPPERSON ) LEFT OUTER JOIN PERSON Dashboard_Creator ON (    Dashboard.IPCREATOR = Dashboard_Creator.IPPERSON )
    WHERE (    Dashboard.GROUPTYPECODE != 'SUBTAB'    AND Dashboard.STATUSCODE != 'ARCHIVED'    AND Dashboard.STATUSCODE = 'OPEN' )
    GROUP BY     Dashboard.STATUSCODE,   Dashboard_Creator.FULLNAME,   Dashboard_Modifier.FULLNAME,    Dashboard.SHORTDESCRIPTION,   Dashboard.STATUSCODE,   Dashboard.STARTDATE,   dashboard.groupid

    Oracle SQL query:

    SELECT DISTINCT    Dashboard.STATUSCODE,   Dashboard.SHORTDESCRIPTION,     Dashboard.STARTDATE,    MAX(Dashboard.LASTMODIFIEDGMTDATETIME) AS "Max LastModifiedDate",   dashboard.groupid
    FROM REPORTGROUP Dashboard LEFT OUTER JOIN PERSON Dashboard_Modifier ON (    Dashboard.IPLASTMODIFIER = Dashboard_Modifier.IPPERSON ) LEFT OUTER JOIN PERSON Dashboard_Creator ON (    Dashboard.IPCREATOR = Dashboard_Creator.IPPERSON )
    WHERE (    Dashboard.GROUPTYPECODE != 'SUBTAB'    AND Dashboard.STATUSCODE != 'ARCHIVED'    AND Dashboard.STATUSCODE = 'OPEN' )
    GROUP BY     Dashboard.STATUSCODE,   Dashboard_Creator.FULLNAME,   Dashboard_Modifier.FULLNAME,    Dashboard.SHORTDESCRIPTION,   Dashboard.STATUSCODE,   Dashboard.STARTDATE,   dashboard.groupid

    parent_child_report

    Displays the list of reports that have child reports associated with it.

    select distinct reportheader.Reportid, reportheader.Reportname,reportheader.DrillCode,reportassociate.childreportid,childreport.reportname
    from Reportheader, reportassociate ,(select reportid,reportname from reportheader)  childreport
    where reportassociate.childreportid=childreport.reportid and reportheader.reportid=reportassociate.parentreportid and reportheader.Drillcode NOT IN ('NODRILL') AND reportheader.ReportStatusCode ='OPEN' AND reportheader.RoleCode !='SUBQUERY' AND reportheader.ReportName !='Draft Report' 

    report_origin

    Displays view details of the reports.

    select distinct Viewat.VIEWID AS Viewid, Viewat.VIEWDESCRIPTION AS ViewName, Report.REPORTID AS Reportid , Report.ReportName
    FROM REPORTHEADER Report LEFT OUTER JOIN REPORTVIEW Viewat ON (    Report.VIEWID = Viewat.VIEWID )
    where  ReportStatusCode ='OPEN' AND ReportName !='Draft Report' AND RoleCode !='SUBQUERY'

    report_owner

    Displays the list of the report owners.

    select ReportId, ReportName, FullName  
    from ReportHeader Left Outer Join Person On ReportHeader.IpOwner = Person.IpPerson where  ReportStatusCode ='OPEN' AND Reportheader.ReportName != 'Draft Report' AND RoleCode !='SUBQUERY'

    broadcasts

    Displays the schedule broadcast details of each report.

    select   reportheader.reportid, reportheader.reportname, ReportBroadcast.BroadcastId, ReportBroadcast.SubjectLine,  TaskSchedule.FrequencyTypeCode,reportbroadcast.broadcastcode
    from ReportHeader, ReportBroadcast ,TaskSchedule
    where reportheader.reportid = ReportBroadcast.ReportId  and ReportBroadcast.BroadcastId=TaskSchedule.ScheduleUnitId and ReportBroadcast.EndDate ='31-DEC-9999'

    part_of_codisplay_report

    Displays the names of the reports and their associated reports.

    select Reportheader.ReportName, Reportheader.reportid, ReportAssociate.ParentReportId, Parent_report.ReportName
    from ReportHeader, ReportAssociate, (select reportname,ReportId from reportheader ) Parent_report
    where ReportHeader.ReportId=ReportAssociate.ChildReportId and Parent_report.ReportId=ReportAssociate.ParentReportId

    part_of_dashboards

    Displays the names of the dashboards and their associated reports.

    select  distinct report.ReportId, Report.ReportName,Reportgroup.GroupId, Reportgroup.ShortDescription as Dashboard_Name from ReportGroup, ReportAssociate, ( Select ReportId, ReportName
    from ReportHeader) AS Report
    where ReportGroup. GroupId= ReportAssociate.ReportGroupId AND ReportAssociate.ChildReportId = Report.ReportId
    Order by ReportGroup.ShortDescription

    filter_conditions

    Displays the filter details of a specific report.

    Warning

    Make sure to change the report id in the query.

    select  rv.ViewName,ft.ColumnName,ft.ShortDescription,ft.DataTypeCode,rf.ColumnOperator,rf.ValueDataOne,rf.ValueUnitCode,rf.ValueDataTwo
    from    ReportFilter rf join ReportFieldTemplate ft on (ft.FieldTemplateId = rf.FieldTemplateId) left join ReportView rv on (rv.ViewId = ft.SubViewId)
    where rf.ReportId = 73620 and rf.FilterAlertCode = 'FILTER'

    reports_with_only_tabular_data

    Displays the list of the tabular reports

    select Reportid,Reportname
    from Reportheader
    where Reportid NOT IN (select reportheader.reportid from ReportHeader ,Chart
    where Reportheader.ReportId = Chart.Reportid ) AND ReportStatusCode ='OPEN'  AND RoleCode !='SUBQUERY' AND ReportName !='Draft Report' 

    count_of_reports_with_only_tabular_data

    Displays the count of reports with tabular data.

    select Count(*) As Total
    from Reportheader
    where Reportid NOT IN (select  reportheader.reportid
    from ReportHeader ,Chart Where   Reportheader.ReportId = Chart.Reportid ) AND ReportStatusCode ='OPEN'  AND
                RoleCode !='SUBQUERY' AND ReportName !='Draft Report'

    count_of_reports_with_charts

    Displays the count of reports with chart types.

    select COUNT(*) AS Total
    from Reportheader
    where   Reportid  IN (select  reportheader.reportid from ReportHeader ,Chart Where   Reportheader.ReportId = Chart.Reportid AND ReportStatusCode ='OPEN'  AND RoleCode !='SUBQUERY' AND ReportName !='Draft Report' )

    chart_type

    Displays the list of reports and their chart types.

    select  distinct reportheader.reportid,ReportName,ChartType
    from ReportHeader ,Chart
    where Reportheader.ReportId = Chart.Reportid AND ReportStatusCode ='OPEN'  AND RoleCode !='SUBQUERY' AND ReportName !='Draft Report'

    report_status

    Displays the list of reports by status.

    select distinct report.reportid, Report.REPORTNAME , Report.REPORTSTATUSCODE
    FROM    REPORTHEADER Report
    where   ( Report.ROLECODE != 'SUBQUERY' ) AND (    Report.REPORTSTATUSCODE = 'OPEN')

    count_of_reports

    Displays the total count of reports

    select Count(distinct Report.ReportId) AS C1
    FROM REPORTHEADER Report
    where Report.ROLECODE != 'SUBQUERY' AND Report.REPORTSTATUSCODE = 'OPEN'  And Report.ReportName != 'Draft Report'

    Broadcast details on open reports

    Displays the scheduled broadcast details of open reports.

    "select b.ReportId,b.BroadcastId,c.ReportName, b.BroadcastTypeCode,b.BroadcastCode,b.SubjectLine,b.BodyText,d.FrequencyCode,d.FrequencyTypeCode,d.FrequencyUnit
        from ReportBroadcast b, ReportHeader c, TaskSchedule d
        where  b.ReportId=c.ReportId and ReportStatusCode='OPEN'
        and d.ScheduleUnitId = b.BroadcastId"

    Calculated fields used in report columns and filters

    Displays the list of reports with calculated fields in report columns and filters.

    "select * from (
    select a.ReportId, a.ReportName, b.RefRltshpTypeCode, b.ValidationFunctionName, AssociatedReportId,'ReportField' UsedIn  
    from reportheader a , ReportFieldTemplate b, Reportfield c
    where a.ReportStatusCode='OPEN'
        and a.viewid=b.viewid
        and c.reportid=a.ReportId
        and c.FieldTemplateId=b.FieldTemplateId
        and (b.ValidationFunctionName is not null or b.SQLClause is not null or (b.AssociatedReportId!=0 and b.AssociatedReportId is not null))
    union
    select a.ReportId, a.ReportName, b.RefRltshpTypeCode, b.ValidationFunctionName, AssociatedReportId, 'ReportFilter' UsedIn  
    from reportheader a , ReportFieldTemplate b, ReportFilter c
    where a.ReportStatusCode='OPEN'
        and a.viewid=b.viewid
        and c.reportid=a.ReportId
        and c.FieldTemplateId=b.FieldTemplateId
        and (b.ValidationFunctionName is not null or b.SQLClause is not null or (b.AssociatedReportId!=0 and b.AssociatedReportId is not null))
    )s order by reportid, UsedIn"

    DBFN Usage

    Displays the list of reports with DBFN.

    "select * from (
    select a.ReportId, a.ReportName, b.RefRltshpTypeCode, b.ValidationFunctionName, AssociatedReportId,'ReportField' UsedIn  
    from reportheader a , ReportFieldTemplate b, Reportfield c
    where a.ReportStatusCode='OPEN'
        and a.viewid=b.viewid
        and c.reportid=a.ReportId
        and c.FieldTemplateId=b.FieldTemplateId
        and (b.ValidationFunctionName is not null or b.SQLClause is not null or (b.AssociatedReportId!=0 and b.AssociatedReportId is not null))
    union
    select a.ReportId, a.ReportName, b.RefRltshpTypeCode, b.ValidationFunctionName, AssociatedReportId, 'ReportFilter' UsedIn  
    from reportheader a , ReportFieldTemplate b, ReportFilter c
    where a.ReportStatusCode='OPEN'
        and a.viewid=b.viewid
        and c.reportid=a.ReportId
        and c.FieldTemplateId=b.FieldTemplateId
        and (b.ValidationFunctionName is not null or b.SQLClause is not null or (b.AssociatedReportId!=0 and b.AssociatedReportId is not null))
    )s where upper(s.ValidationFunctionName) like '%DBFN%' order by reportid, UsedIn"

    most used reports within 30 days by user name

    Displays the list of reports which are used mostly in last  30 days along with the user names.

    Microsoft SQL query:

    SELECT  DISTINCT    count(events.contentid) as hitcount, Report.REPORTNAME,Report.REPORTID, person.fullname
    FROM    (
            select * from Event
            UNION ALL
            Select * from EventArchive
            ) Events LEFT OUTER JOIN REPORTHEADER Report ON (    Events.CONTENTID = Report.REPORTID ) AND (    Report.ROLECODE != 'SUBQUERY' )
            Left outer join person on (events.ipsource=person.ipperson)
    WHERE   Events.EVENTTYPECODE != 'SYSTEMTASK' AND (Events.EVENTDATE <= GETUTCDATE()-31) AND Events.EVENTTYPECODE = 'REPORT'    AND
            Events.EVENTCODE in ('RPTRUN','DASHRUN')    AND Report.REPORTSTATUSCODE = 'OPEN'
    group by  Report.REPORTNAME, Report.REPORTID, person.fullname
    order by 2,1 desc

    PostgreSQL query:

    SELECT  DISTINCT    count(events.contentid) as hitcount, Report.REPORTNAME,Report.REPORTID, person.fullname
    FROM    (
            select * from Event
            UNION ALL
            Select * from EventArchive
            ) Events LEFT OUTER JOIN REPORTHEADER Report ON (    Events.CONTENTID = Report.REPORTID ) AND (    Report.ROLECODE != 'SUBQUERY' )
            Left outer join person on (events.ipsource=person.ipperson)
    WHERE   Events.EVENTTYPECODE != 'SYSTEMTASK' AND (Events.EVENTDATE <= SYSDATE-31) AND Events.EVENTTYPECODE = 'REPORT'    AND
            Events.EVENTCODE in ('RPTRUN','DASHRUN')    AND Report.REPORTSTATUSCODE = 'OPEN'
    group by  Report.REPORTNAME, Report.REPORTID, person.fullname
    order by 2,1 desc

Where to go from here

Running-the-migration-tool

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC Helix Dashboards 25.4