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 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 users 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

    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 = 'RPTRUN'    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 = 'RPTRUN'    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 = 'RPTRUN'    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

    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"

 

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 22.3