Skip navigation
1 2 3 Previous Next

BMC Helix

86 posts
Share:|

BMC Helix Customer News header.jpg

 

Welcome to Edition 1 of our BMC Helix Newsletter. Stay in the know on all things BMC Helix and get the most value for your organization.

 

CUSTOMER SPOTLIGHT

 

Learn how other customers used BMC Helix to transform their service experience to the next level with digital transformation.

 

HappyManWomanOffice.jpg

 

 

If you would like to be featured on Customer Spotlight with your BMC Helix Success story tweet us @BMCHelix.

Screen Shot 2019-06-25 at 4.24.13 PM.png

THINGS YOU NEED TO KNOW

 

BMC Helix 19.05 Release

 

Our latest BMC Helix release 19.05 continues to drive a superior experience for the enterprise across End Users, Agents, IT Operations and Developers. Check out the latest enhancements:


 

Get a Customized Estimate on the Value of Cognitive Service Management

 

354% ROI on your digital transformation investment sounds good, right? A recent Forrester study found BMC Helix customers achieved just that. Quickly calculate the blockbuster results you could achieve at Forrester Total Economic Impact Calculator for BMC Helix.

 

Forrester calculator thumbnail.jpg

 

New Go-Live Kits

 

Let your internal stakeholders know the value of your cognitive service initiatives as you roll out BMC Helix solutions. Ask your sales representative for more information on our BMC Helix Go-Live Kits.

 

Go-Live Kit thumbnail.jpg

 

Screen Shot 2019-06-25 at 4.24.13 PM.png

EVENTS

 

 

Screen Shot 2019-06-25 at 4.24.13 PM.pngEDUCATION

 

Check out BMCHelix.CIO.com

 

Learn more about digital transformation, the workplace of the future, the intersection of DevOps and Service Management and more from industry experts and your peers on BMCHelix.CIO.com. This is a joint effort between BMC Helix and CIO.com.

 

Screen Shot 2019-06-25 at 4.32.36 PM.png

 

Screen Shot 2019-06-25 at 4.24.13 PM.png

FOLLOW BMC HELIX


Get Social!

BMC Helix is active on Twitter, LinkedIn and BMC Communities. Follow so you can get timely details soon on our get social customer contest! #ITSMtoCSM #BMCHelix

 

 

page1image17626048page1image17626432page1image17627584

 

 

 

Share:|

Please join Daniel and Bharath as they present on Smart Reporting Performance best practices during the August 2019 webinar.

 

In this session, they will provide an overview of the architecture and sizing, best practice configuration setting for optimal performance including cluster configuration, and will review the benefits of a replicated database architecture for performance.

 

Bharath Kumar Chitrala is a Staff Specialist Technical Support Analyst

Daniel Marrujo is a Staff Technical Support Analyst

 

Event Registration Details

 

Date: Wednesday, August 21, 2019

Time: 10 a.m. Central Daylight Time (GMT-5)

Registration Link: https://bmc.webex.com/bmc/onstage/g.php?MTID=e72628a7a0dc1d6b7e7eafef1638536f1

Registration Password: helix

 

After registration, you will receive a confirmation email.

 

For more information, or if you have questions, please contact Gregory Kiyoi or Jeff Hudson

Remedy AR System  Remedy ITSM  CMDB  Discovery  Digital Workplace BMC Helix

Share:|

Please join Dolly as she discusses FTS Indexing in this technical deep dive recorded webinar session.

 

In this session, she will review the FTS architecture focusing on the FTS Indexing process, FTS Management Console, ft_pending, and look at troubleshooting indexing issues.

 

Dolly Parrillo is a Senior Technical Support Analyst

 

Below is the recording of the webinar and it is also available as a podcast on iTunes.

 

 

Presentation References

 

Subscribe to the BMC Helix Remedy & Discovery Youtube Channel

https://www.youtube.com/c/BMCRemedyandDiscovery

 

Full Text Search (FTS) indexing: The magic with ft_pending table and what its contents mean - 000152517

 

Full Text Search (FTS) Deployment and Configuration Best Practices (Whitepaper) - 000159890

 

Full Text Search (FTS) Best Practice Configuration for 9.1.x - 000153129

 

Questions on FTS optimization and Collection Directory Size post re-index - 000030915

 

Troubleshooting FTS Indexing Issues

 

 

For more information, or if you have questions, please contact Gregory Kiyoi or Jeff Hudson

Remedy AR System  Remedy ITSM  CMDB  Discovery  Digital Workplace

Share:|

Want to stay connected with what's happening in BMC Helix events, customer tips and analyst insights?  Join the new BMC Helix LinkedIn group. 

It's simple, just click here and  Join the BMC Helix LinkedIn group

Be sure to also Follow us on Twitter

 

Join now because BMC Helix will be kicking off promotions and prizes to reward our most social customers.

Have a great summer. See you on BMC Helix social media!

Share:|

BMC Helix Chabot is getting hotter with the new 19.05 release.  Here's a summary of 19.05.

 

BMC Helix Chatbot 19.05 focuses on both the end user experience and the organization’s ability to measure and improve the capabilities of the Chatbot.

 

Support for New Channels

Users can now access BMC Helix Chatbot through two additional messaging platforms: MS Teams and Skype for Business On-Premises.

 

  • Interact with the BMC Helix Chatbot through MS Teams or Skype for Business On-Premises.
  • Find the Helix Chatbot in your contacts list and message with the user the same way as any other contact in the organization.
  • Find resolution to your issue or transfer to a live agent.

New End User Web User Interface

New contemporary chat and messaging experience for end users across all devices.

  • Progressive Web App works across all form factors-laptop, iPhone, tablet to provide a contemporary experience.
  • Helix comes to life via animation and is ready to help the end user. At the start of chat, The Helix image pops up and asks how it can help.
  • New interactive buttons and ‘show more’ feature for long button lists.
  • New Knowledge view
  • Easily view all Helix recommended articles via carousel without going back and forth from the chat window.
  • View attachments from knowledge articles.
  • Results are returned with relevant passages.

End User Surveys and Feedback

BMC Helix Chatbot now has out of the box Surveys that are presented to the users at the right times in a conversation.  IT can use this feedback to help improve the end-user experience with BMC Helix.

  • Easy to use survey presented to the user after a service request is made, or at any time that the user requests to give feedback
  • Survey is only presented to the user once in the conversation, unless the user specifically asks to provide feedback again.
  • Results of the survey are tracked in the Telemetry microservice service.

 

Smart Reporting for Helix Chatbot

Using Smart Reporting, IT can report against data in the Telemetry service using out-of-the box or custom reports.

  • Out of the box reports include:
    • Service Requests created by Helix Chatbot
    • Transfers to Live Agent
    • Entitlements Check by Helix Chatbot
  • Build your own reports on any data in the Telemetry Microservice
  • Use reporting to measure and monitor the experience and effectiveness of the Chatbot in your organization.

Cognitive Insights for BMC Helix Chatbot

BMC Helix Chatbots can now provide knowledge to users through Cognitive Insights, leveraging the enterprise Insight Engine.

Chatbots can consume this information to provide more useful results.

  • Provide insights from structured and unstructured knowledge sources
  • Provide insights from federated and unfederated knowledge
  • Natural language search
  • Continuous relevancy training. Using Machine Learning, Chatbot returns Knowledge articles. The Cognitive Search microservice learns from users and their feedback to improve Chatbot accuracy. 
Share:|

In this session Salman will review how to integrate RSSO with TSPS and how to troubleshoot common configuration issues.

 

Salman Shaikh is a Technical Support Analyst

 

Below is the recording from the live webinar including Q&A asked during the event.  The recording is also available as a podcast on iTunes.

 

 

Presentation References

 

Subscribe to the BMC Helix Remedy & Discovery Youtube Channel

https://www.youtube.com/c/BMCRemedyandDiscovery

 

RSSO LDAP Users & Groups Configuration

 

Manual Integration of Remedy SSO with TSPS

 

TrueSight 11.00 & Remedy Single Sign-on 9.1.03.01 Install & Configuration

 

Configuring the authorization profile

 

For more information, or if you have questions, please contact Gregory Kiyoi or Jeff Hudson

Remedy AR System  Remedy ITSM  CMDB  Discovery  Digital Workplace

Share:|

Please join John as he will review the updates in Digital Workplace 19.02 including an example of how-to rebrand Digital Workplace in this short webinar.

 

John Gallagher is a Lead Technical Support Analyst

 

Below is the recording from the live webinar including Q&A asked during the event.  The recording is also available as a podcast on iTunes.

 

 

 

Presentation References

 

Subscribe to the BMC Helix Remedy & Discovery Youtube Channel

https://www.youtube.com/c/BMCRemedyandDiscovery

 

BMC Communities: What’s new in DWP 19.02

 

BMC Documentation: 19.02 enhancements

 

For more information, or if you have questions, please contact Gregory Kiyoi or Jeff Hudson

Remedy AR System  Remedy ITSM  CMDB  Discovery  Digital Workplace

Raj Cheruvu

Agile Change Management

Posted by Raj Cheruvu Employee Apr 19, 2019
Share:|

Reposting based on the interest in recent SKO on the whole DevOps paradigm

 

 

I recently read a story about a teacher asking her class, why are there brakes in a car. As expected, there were several answers

-          To slow car down

-          To avoid collision

-          Etc..

One student replied, “ To enable you to go faster “

That response was truly profound. Can you imagine how fast would you drive a car if there were no brakes in them?

With the increase in the adoption of Agile practices in companies, the development teams are resisting the idea of Change management and are pushing the service owners to remove the onerous Change approval process to enable rapid delivery.

Now if you think about it, is the Change approval process any different than the brake in a car ? Just like a brake enables the driver to confidently drive faster and reach their destination sooner, a proper change management enables changes to be deployed faster and brings agility to the organizations.

With Helix ITSM , this is the exact philosophy we embraced by introducing a Risk analysis module in our offering that calculates the risk score for every change that’s being produced from the agile development tools like Jira, Azure for DevOps, Rally etc.. and automatically creates the change in our change management module with that score. The service owner now has the flexibility to define which changes can be auto approved and the changes that need additional diligence before deployment.

This process allows organizations to deliver rapid changes without risking the availability of the services that they offer.

Vidhya Srinivasan

Share:|

In this session, Rajeev will cover database considerations for the Remedy Platform Solution in July’s monthly webinar.

 

This presentation will discuss various database considerations based on your Remedy deployment to optimize performance.  The following topic areas will be included:

 

  *   Oracle Case Insensitivity and the LIKE operator

  *   Oracle cursor_sharing & Remedy 9.x/18/x versions

  *   Oracle SQL Plan Directives, 12c optimizer features & Remedy

  *   Oracle optimizer_features_enable parameter and Remedy

  *   MSSQL’s PARAMETERIZATION & Remedy 9.x, 18.x, and 19.x versions

  *   MSSQLs Maximum Degree of Parallelism and the Cost Threshold for Parallelism parameters for tuning

  *   MSSQL’s Schema Binding and Indexing of Views

 

Rajeev Patel is a Lead Product Developer

 

 

Event Registration Details

 

Date: Wednesday, July 24, 2019

Time: 10 a.m. Central Daylight Time (GMT-5)

Registration Link: https://bmc.webex.com/bmc/onstage/g.php?MTID=e655ef222b75f7bdef820d7e4a1fe6849

Registration Password: helix

 

After registration, you will receive a confirmation email.

 

For more information, or if you have questions, please contact Gregory Kiyoi or Jeff Hudson

Remedy AR System  Remedy ITSM  CMDB  Discovery  Digital Workplace BMC Helix

Share:|

In this session, Vipul will cover optimizing the Remedy Platform Solution in June’s monthly webinar.

 

A successful implementation of the Remedy ITSM suite applications can positively impact the business processes and provide a great experience to the end users.  Proper deployment architecture decisions will help keep end users productive and focused on the business and reduce overall maintenance.

 

This presentation will discuss various factors to consider while designing an optimal Remedy deployment including some of the critical system attributes of performance, scalability, availability and flexibility. Including the following topic areas:

 

1. Remedy deployment architecture design approach

2. Remedy deployment architecture design validation via performance/load testing

3. Post implementation monitoring and optimization

 

Vipul Jain is a Senior Director in Product Development Performance Team

 

Below is the recording from the live webinar including Q&A asked during the event.  The recording is also available as a podcast on iTunes.

 

 

Additionally, you can find questions discussed during the webinar session in an attachment or on this online document.

 

Presentation References

 

Subscribe to the BMC Helix Remedy & Discovery Youtube Channel

https://www.youtube.com/c/BMCRemedyandDiscovery

 

Configuration checklist for BMC Remedy 9.x and 18XX – KA# 000114508

 

Performance tuning for BMC Remedy

 

Performance benchmarking BMC Remedy applications

 

JVM Monitoring

 

FTS: Deployment  and configuration best practices – KA#000159890

 

For more information, or if you have questions, please contact Gregory Kiyoi or Jeff Hudson

Remedy AR System  Remedy ITSM  CMDB  Discovery  Digital Workplace BMC Helix

Share:|

In this session Corey will review troubleshooting Data Management tool’s job console.  He will provide an overview of DMT/UDM and why the benefits of using this tool.  Also will review what forms to inspect based on each step (load, validation, promotion) of the job, what logs to gather to understand the failure, and how to resolve common issues using the job console.

 

Corey Rolen is a Senior Technical Support Analyst

 

Below is the recording from the live webinar.  The recording is also available as a podcast on iTunes.

 

 

Presentation References

 

Subscribe to the BMC Helix Remedy & Discovery Youtube Channel

https://www.youtube.com/c/BMCRemedyandDiscovery

 

DMT/UDM Documentation

 

More information around the configuration checker

Support maintained knowledge base articles

Troubleshooting DMT/UDM Load balance and Server Group Issues - KA#000163160

Troubleshooting DMT/UDM Job console and load step issues - KA#000163011

Troubleshooting DMT/UDM Promote step issues - KA#000163210

Troubleshooting DMT/UDM Validate step issues - KA#000163487

 

 

For more information, or if you have questions, please contact Gregory Kiyoi or Jeff Hudson

Remedy AR System  Remedy ITSM  CMDB  Discovery  Digital Workplace BMC Helix

Share:|

Starting with Helix version 18.08, Digital Workplace and Digital Workplace Advanced enhancements will be pushed to Helix customers in our Chicago, Amsterdam, AWS Oregon, London and Sydney data centers. Azure US will shortly follow.

 

The upgrades will be pushed 4 times a year around February, May, August and November. Periodically, patches may also be pushed. The maintenance window will be shared with you, via email, approximately a month before. As a general rule, these should be zero-downtime upgrades and patches. Any changes to this will be communicated to you as part of the notification process. The “maintenance” notification type will be used to send notifications about upgrades. Please ensure your customer contacts are updated on i.onbmc.com.

 

A calendar is maintained by BMC with updated upgrade dates and is a good reference for to bookmark: https://communities.bmc.com/docs/DOC-111268. For any issues identified after an upgrade, please contact BMC Technical Support via i.onbmc.com or phone call.

 

Release notes for the BMC Helix Platform enhancements can be found here: https://docs.bmc.com/docs/display/public/helixsubscriber/Release+notes+and+notices

 

If you have any questions about the above or how to update your customer contacts, please reach out to your BMC Helix Business Relationship Manager.

Share:|

In this session, learn how BMC’s cognitive solutions can help run a more efficient service desk.

 

Product management will share our cognitive strategy and then demonstrate our current cognitive capabilities. See how cognitive automations like issue categorization and automated incoming email handling can save you time in Remedy. You will also see how your end users can use conversation to self-heal with our Helix Chatbot!

 

Lorna Russell is a Senior Manager, Product Management

Daren Goeson is a Principal Product Manager

Pradeep Kumar is a Principal Product Manager

 

Below is the recording from the live webinar.  The recording is also available as a podcast on iTunes.

 

 

Presentation References

 

Subscribe to the BMC Helix Remedy & Discovery Youtube Channel

https://www.youtube.com/c/BMCRemedyandDiscovery

 

Cognitive Service Management

 

Cognitive Service Management Whitepaper

 

 

For more information, or if you have questions, please contact Gregory Kiyoi or Jeff Hudson

Remedy AR System  Remedy ITSM  CMDB  Discovery  Digital Workplace BMC Helix

Share:|

In this month’s blog, I want to share insights on Smart Reporting Database Repository. Occasionally while working on the Smart Reporting DB, you may be stymied about the data here and its relation in context.

 

Let’s discuss in greater detail about the Smart Reporting DB Repository, the data that gets stored and how it is linked. To make your job even easier I have listed some of the most useful SQL queries you will require while working on the DB. The Smart Reporting DB is a place where all the meta data is stored and includes:

    • Report Metadata
    • User accounts
    • Dashboards
    • Configuration options
    • Events

 

Let's look at each of these comprehensively:

 

Stores information related to Users, Orgs and Groups.

  • IpClass, Person, IpContact -  Stores User information
  • Organisation - Organisation details reside in this table.
  • IpRltShp - user to org, org to primary org relationship.
  • AccessGroup - Groups are stored in this db table.
  • AccessGroupFlat - groupId to memberID relation.
  • AccessGroupMembers - Members in group.

 

Audit data

  • Event - This table stores all Smart Reporting usage data, such as; User logins, Running reports, Imports/exports. This data is all used for auditing.
  • EventArchive - This table stores all the archived event data, so the data here is simply data that was moved from the 'Event' table after a specified period.
  • ReportInstance - This table stores a record for each report, each time it is run or edited. If you run the same report multiple times, you will get multiple rows for that report in this table. This is used for things like; Getting ‘average report run time' and also used to 'Remember Filter Values' options, editing a report (the draft copy is a separate record so you can roll back to the original activated version), Results used for KPI reporting.
  • ReportBroadcastResult - Information about broadcast run like recipient information and whether broadcast has been delivered or not.

 

Stores information about Roles and Functions.

  • OrgRole - All available roles in the system
  • AccessClassList - Roles to functions + Access Level (Create, Read, Update and Delete – CRUD)
  • StaffMemberRole - User to Role information.

 

Information about DataSource and Views

  • ReportViewSource - Stores DataSource related details.
  • ReportViewSourceParameter - Data Source parameters e.g. Custom parameters
  • ReportView - Information about views.
  • ReportViewRltshp - Info about table joins.
  • ReportFieldTemplate - Objects in the view.

 

Reports and Charts

  • ReportHeader - Main table which holds report metadata.
  • ReportField - linked to fieldtemplateid. Stores fields present in a report.
  • ReportFilter - This is linked to fieldtemplate. Stores view level cached filters, grouped data filters, set analysis, variance etc.
  • ReportFormat - Stores report, field, filter, chart formats.
  • Chart, ChartField, ChartAnalyticField - core tables which hold chart information.
  • ReportAlert - conditional format information.
  • ReportBroadcast - Stores broadcast information.
  • ReportBroadcastRecipient - Stores broadcast recipient list.
  • ReportFieldConverter - analytic functions, view level converters, formatters.
  • ReportFieldParameter - parameters for these objects.
  • CachedFilter - links to ReportFilter, stores report definition.
  • CachedFilterValue - filter values, links to parent filters.
  • ReportGroup - Information about dashboard
  • ReportGroupEntity - items on the dashboard.
  • ReportGroupFormat - filters, analytic links.

 

Blobs & Clobs

  • TextEntity, TextData - Stores discussion on Report, dashboard, storyboard etc.
  • DocumentData - This table stores actual data of reports, like cached report result sets (this includes cached report, and snapshots created at the report level) , Freehand SQL (used for virtual tables, custom query filters, freehand SQL reports) , Cached filter results from older releases (we now store the filter results in different tables), CSV reports.
  • ReportImageItem, ReportImageData - thumbnail images, backgrounds reportimagemapzone - raster map zones - colour to ref code.

 

System

  • TaskSchedule - System tasks and background tasks.
  • Configuration - System-wide settings per org.

 

 

Access Filter

  • ReportSourceFilter – Stores access filter values.

 

SQL queries Walk through

1) List all active reports:

       SELECT * FROM ReportHeader WHERE ReportStatusCode='OPEN' 

 

ReportStatusCode can be DELETED, PENDING, ARCHIVED, OPEN and DRAFT'.

  • DELETED means you clicked the delete button from within the UI.
  • PENDING means it has been created, but there is content approval on the content folder, and the report requires approval.
  • ARCHIVED means that you have edited this report (each time you edit it creates a new record), and this specific row is no longer the active version of the report
  • OPEN means the report is active
  • DRAFT means that it has been edited but not published

 

 

2) List all active private reports:

       SELECT * FROM ReportHeader WHERE ExternalAccessCode='PERSONAL' AND ReportStatusCode='OPEN'

 

3) List all active public reports:

       SELECT * FROM ReportHeader WHERE ExternalAccessCode=' CORPORATE' AND ReportStatusCode='OPEN'

 

4) List of all active Users:

SELECT p.firstname,

       p.lastname,

       c.emailaddress,

       i.emailleft,

       s.rolecode

FROM   person p

       INNER JOIN ipcontact c

ON p.ipperson = c.ipid

       INNER JOIN ipclass i

ON p.ipperson = i.ipid

       INNER JOIN staffmemberrole s

ON p.ipperson = s.ipemployee

WHERE  i.enddate > Getdate()

       AND s.enddate > Getdate();

 

5) List of Enabled functions for a role:

SELECT *

FROM   accessclasslist

WHERE  rolecode LIKE '%YFADMIN%'

       AND accesslevelcode IS NOT NULL

 

  

    Above query will list the enabled functions for the Admin Role.

 

6) User details with Role and Client Org Name

SELECT p.fullname as "User",

       ic.emailright as "Cleint Org",

       sm.rolecode as Role

FROM   ipclass ic

       INNER JOIN person p

ON ( ic.ipid = p.ipperson )

       INNER JOIN staffmemberrole sm

ON ( ic.ipid = sm.ipemployee )

WHERE  ic.emailright = 'calbro'

       AND ic.enddate = '99991231 00:00:00.000'

       AND sm.enddate = '99991231 00:00:00.000'

 

 

 

7) List of Reports with View Name

SELECT rh.reportname,

       rv.viewdescription AS "View Name"

FROM   reportheader rh

       INNER JOIN reportview rv

ON ( rh.viewid = rv.viewid )

 

 

8) View Object Usage:

SELECT rf.reportid,

       rh.reportname,

       rh.reportstatuscode

FROM   reportfield rf

       INNER JOIN reportfieldtemplate rft

ON ( rf.fieldtemplateid = rft.fieldtemplateid )

       INNER JOIN reportheader rh

ON ( rh.reportid = rf.reportid )

WHERE  rft.columnname LIKE 'Incident Number'

       AND rft.viewid = 108662

 

The above query will list all reports where “Incident Number” field is used.

 

 

9) List of active Views:

SELECT *

FROM   reportview

WHERE  viewname = 'New View'

       AND viewstatuscode = 'OPEN'

 

 

10) Fields deleted from the View:

SELECT *

FROM   reportfieldtemplate

WHERE  viewid IN (SELECT viewid

FROM reportview

WHERE viewstatuscode = 'OPEN'

                         AND viewname = 'New View')

       AND statuscode = 'DISCONNECTED';

 

 

11) Fields present in specific Report:

SELECT distinct rh.reportname,

       rft.shortdescription

FROM   reportfield rf

       INNER JOIN reportheader rh

ON rf.reportid = rh.reportid

       INNER JOIN reportfieldtemplate rft

ON rf.fieldtemplateid = rft.fieldtemplateid

WHERE  rh.reportname = 'Incident Details'

GROUP  BY rh.reportname,

rft.shortdescription

The above query will list all objects /columns present in the “Incident Details” Report

 

12) Reports that need update on hyperlink object:

SELECT *

FROM   reportheader

WHERE  reportid IN (SELECT reportid

                    FROM   reportfieldparameter

                    WHERE  viewid = '84506'

                           AND entitytypecode = 'REPORTFIELD'

                           AND classname = 'com.hof.mi.custom.format.LinkFormatter'

                           AND parameterkey = 'URL')

 

 

13) List of private reports that belong to deleted Users:

SELECT *

FROM   reportheader R

       INNER JOIN ipclass I

ON ipid = ipowner

WHERE  I.enddate != '9999-12-31'

       AND R.reportstatuscode = 'OPEN'

       AND R.externalaccesscode = 'PERSONAL'

 

14) List of all active Dashboards:

SELECT *

FROM   reportgroup

WHERE  enddate = '9999-12-31'

       AND grouptypecode = 'ANALYTIC'

       AND statuscode = 'OPEN'

 

15) List of all PRIVATE Dashboards:

SELECT *

FROM   reportgroup

WHERE  grouptypecode = 'ANALYTIC'

       AND accesscode = 'ACCESSLEVEL'

 

16) List of all active Client Orgs:

SELECT     org.orgname,

org.iporg,

rvc.databaseurl,

rvc.username

FROM       organisation org 

INNER JOIN iprltshp rlt

ON         (

                      rlt.ipchild = org.iporg

AND        rlt.endreasoncode IS NULL

AND        rlt.rltshptypecode = 'CUSTOMER')

INNER JOIN reportviewsource rvc

ON         (

                      rvc.iporg = org.iporg

AND        databasetypecode = 'BMCACTIONREQUEST')

 

 

17) Report and System tasks scheduled in minutes:

SELECT *

FROM   taskschedule

WHERE  frequencytypecode = 'MINUTES'

       AND scheduleison = 1

 

 

 

18) Entries created by Report Refresh in DocumentItem, DocumentRevision and DocumentData table:

SELECT *

FROM   documentitem

WHERE  documenttypecode = 'REPORTXML'

 

 

SELECT *

FROM   documentrevision

WHERE  documentid IN (SELECT documentid

                      FROM   documentitem

                      WHERE  documenttypecode = 'REPORTXML')

 

SELECT *

FROM   documentdata

WHERE  revisionid IN (SELECT revisionid

                      FROM   documentrevision

                      WHERE  documentid IN (SELECT documentid

                                            FROM documentitem

                                            WHERE

                             documenttypecode = 'REPORTXML'))

 

19) List of Broadcasts with Report Info:

SELECT RB.broadcastid,

       RB.reportid,

          RH.reportname,

          RB.SubjectLine,

       RB.broadcasttypecode,

       RB.broadcastcode,

       RB.startdate,

       RB.enddate,

       TS.frequencytypecode,

       TS.frequencyunit,

       TS.lastruntime,

       TS.lastrunstatus

FROM   reportbroadcast RB,

reportheader RH,

taskschedule TS

WHERE  RB.reportid = RH.reportid

       AND TS.scheduleunitid = RB.broadcastid

 

 

 

20)  Entry page for existing users:

SELECT person.firstname as "First Name",

person.lastname as "Last Name",

ipclass.emailleft as "Login ID",

personconfiguration.configcode,

personconfiguration.configdata

FROM   personconfiguration,

ipclass,

person

WHERE  configcode = 'ENTRYPAGE'

       AND person.ipperson = ipclass.ipid

       AND personconfiguration.ipperson = ipclass.ipid

 

 

21) Number of reports that are currently cached and the category they belong to:

SELECT a.categorycode,

       a.subcategorycode,

       Count (DISTINCT a.reportid) as "Report Count"

FROM   reportheader a,

reportinstance b,

documentitem c

WHERE  a.reportid = b.reportid

       AND a.reportid = c.subjectid

GROUP  BY a.categorycode,

a.subcategorycode

 

22) List of cached reports in the document data table and count of reports copies per category:

SELECT a.reportid,

       a.reportname,

       Count (b.reportinstanceid) AS ReportInstance#,

       a.categorycode,

       a.subcategorycode,

       C.subjectid

FROM   reportheader a,

reportinstance b,

documentitem c

WHERE  a.reportid = b.reportid

       AND a.reportid = c.subjectid

GROUP  BY a.categorycode,

a.subcategorycode,

a.reportname,

a.reportid,

c. subjectid

 

23) Count of different types of Dashboard Events:

SELECT DISTINCT

"Events"."EventDate",

"Events"."EventCode",

   COUNT(DISTINCT("Events"."EventId")) as "Event Count"

FROM (

   select * from Event

UNION ALL

Select * from EventArchive

 

) AS "Events"

WHERE (

"Events"."EventCode" IN (N'DASHRUN', N'EXPORTDASHBOARD', N'DASHEDIT', N'DASHCREATE', N'IMPORTDASHBOARD')

)

GROUP BY

"Events"."EventDate",

"Events"."EventCode"

 

 

 

24) Count of Users belonging to a Specific Group:

SELECT DISTINCT

"User Group Details"."ShortDescription" as "Group Name",

   COUNT(DISTINCT("User"."IpPerson")) as "Users"

FROM "Person" AS "User"

LEFT OUTER JOIN "AccessGroupFlat" AS "User Group"

ON (

"User"."IpPerson" = "User Group"."IpPerson"

)

LEFT OUTER JOIN "AccessGroup" AS "User Group Details"

ON (

"User Group"."AccessGroupId" = "User Group Details"."AccessGroupId"

)

WHERE (

"User Group Details"."ShortDescription" IS NOT NULL

)

GROUP BY  "User Group Details"."ShortDescription"

 

 

 

25) Count of Users belonging to a specific Role:

SELECT DISTINCT

   "User Role Details"."ShortDescription" as "Role Name",

   COUNT(DISTINCT("User"."IpPerson")) as "Users"

FROM "Person" AS "User"

INNER JOIN "StaffMemberRole" AS "User Role"

ON (

   "User"."IpPerson" = "User Role"."IpEmployee"

)

INNER JOIN "OrgReferenceCodeDesc" AS "User Role Details"

ON (

   "User Role"."RoleCode" = "User Role Details"."RefCode"

)

GROUP BY

   "User Role Details"."ShortDescription"

 

 

26) Avg Report Run Time:

SELECT DISTINCT

"Report"."ReportName",

   AVG("Report Instance"."ReportDuration") as "Average Runtime",

   AVG("Report Instance"."RowsProcessed") as "Average Rows",

"Report"."HitCount"

FROM "ReportHeader" AS "Report"

LEFT OUTER JOIN "ReportInstance" AS "Report Instance"

ON (

"Report"."ReportId" = "Report Instance"."ReportId"

)

WHERE (

"Report"."RoleCode" != N'SUBQUERY'

)

AND (

"Report"."ReportStatusCode" = N'OPEN'

   AND "Report"."RoleCode" != N'SUBQUERY'

)

GROUP BY

"Report"."ReportName",

"Report"."HitCount",

"Report"."PublishUUID"

HAVING AVG("Report Instance"."ReportDuration") IS NOT NULL

 

Note - ReportDuration Column present in ReportInstance table represents the time report query takes to return from the database. This is NOT the total time the report takes to fully render in the browser. Rendering in the browser will take longer for reports with large amounts of post processing elements like cross-tabs, analytical functions, etc. This value is measured in whole seconds. The datatype is an INT, and therefore can't show less than 1 second with precision. This is why a large number of values in this column may be 0.

 

 

27) Available Fonts in Smart Reporting:

SELECT *

FROM   configuration

WHERE configcode = 'ADDITIONALFONTS'

 

 

 

28) List of Views in a specific Org:

SELECT *

FROM   reportview v,

reportviewsource s

WHERE  v.sourceid = s.sourceid

       AND v.viewstatuscode NOT IN ( 'DELETED', 'REPLACED', 'ROLLEDBACK' )

       AND s.accesscode != 'DELETED'

       AND ( s.iporg = (SELECT iporg

                        FROM   organisation

                        WHERE  orgname = 'calbro_Calbro Services') )

       AND viewname = 'New View'

 

The above query will list all active Views present in 'calbro_Calbro Services' Client Org.

 

29) Private Reports accessible by a Specific User:

SELECT entityid,

entitytypecode,

accesslevelcode,

accesssubjectcode,

       accessgroupid

FROM   accesslevel

WHERE  entitytypecode = 'PRIVATEREPORT'

       AND ( ( accesssubjectcode = 'PERSON'

AND accessgroupid = 12251 )

OR ( accesssubjectcode = 'GROUP'

AND accessgroupid IN (SELECT accessgroupid

                                         FROM accessgroupflat

                                         WHERE  ipperson = (SELECT ipid

FROM ipclass

                                                            WHERE

emailleft = 'sradmin'))

) )

 

The above query will list all accessible private reports for “sradmin” User.

 

30) Query to verify the EXTERNALURLPARAMS (Share Link Parameters):

select * from configuration  where configcode = 'EXTERNALURLPARAMS'

 

31) List of all PRIVATE Content folders:

SELECT * FROM   contentmanagement WHERE  loginaccesscode = 'ACCESSLEVEL'

 

32) List of all Content folders where Expert Approval is set:

SELECT *

FROM   contentmanagement

WHERE  approvalflag = 1

 

33) Data Source Usage:

SELECT DISTINCT

"Data Source"."SourceName",

   COUNT(DISTINCT("Report"."PublishUUID")) as "Active Reports",

   COUNT(DISTINCT("Events"."EventId")) as "Report Hits"

FROM (

   select * from Event

UNION ALL

Select * from EventArchive

 

) AS "Events"

LEFT OUTER JOIN "ReportHeader" AS "Report"

ON (

"Events"."ContentId" = "Report"."ReportId"

)

AND (

"Report"."RoleCode" != N'SUBQUERY'

)

LEFT OUTER JOIN "ReportView" AS "View"

ON (

"Report"."ViewId" = "View"."ViewId"

)

LEFT OUTER JOIN "ReportViewSource" AS "Data Source"

ON (

"View"."SourceId" = "Data Source"."SourceId"

)

WHERE (

"Events"."EventCode" IN (N'DASHRUN', N'RPTRUN')

   AND "Report"."ReportStatusCode" = N'OPEN'

   AND "Data Source".SourceName in ('AR System')

)

GROUP BY

"Data Source"."SourceName"

 

 

34) View Usage:

SELECT

   "View"."ViewDescription",

   COUNT(DISTINCT("Report"."PublishUUID")) as "Active Reports",

   COUNT(DISTINCT("Events"."EventId")) as "Report Hits"

FROM (

   select * from Event

UNION ALL

Select * from EventArchive

 

) AS "Events"

LEFT OUTER JOIN "ReportHeader" AS "Report"

ON (

   "Events"."ContentId" = "Report"."ReportId"

)

AND (

   "Report"."RoleCode" != N'SUBQUERY'

)

LEFT OUTER JOIN "ReportView" AS "View"

ON (

   "Report"."ViewId" = "View"."ViewId"

)

WHERE (

   "View"."ViewStatusCode" NOT IN (N'DELETED', N'REPLACED')

   AND "Events"."EventCode" IN (N'DASHRUN', N'RPTRUN')

   AND "Report"."ReportStatusCode" = N'OPEN'

)

GROUP BY

   "View"."ViewDescription"

 

 

 

Disclaimer - It is not recommended to perform any kind of DML and DDL operations on Repository DB, It may break referential integrity of content Metadata.

 

I hope you found this post helpful in understanding Smart Reporting DB Repository. Also take an opportunity to visit our Smart Reporting User Group Get Started with Smart Reporting

 

You can find additional blogs like this at  The specified item was not found.

Share:|

Smart Reporting provides powerful options which allow you to format report records depending on the presence of certain conditions. For example, you can highlight a record if a due date is past.

 

In this blog I will show how you can create a simple traffic light or red, amber, green (RAG) report, which will highlight incidents that are past SLA in red, those that are close to their SLA resolution date in orange and those with plenty of time left in green.

 

First create your report, in this example I have created a simple incident report.

incident-report-sla-resolution-date.png

I want to use a traffic light system to highlight incidents that are close to or past their Required Resolution Date.

Change the background cell colour based on resolution due date

Click on Conditional Formatting:

conditional-formatting.png

Select Required Resolution Date then set up three alerts, making sure that Dynamic Date is selected.

traffic-light-smart-report.jpg

The rules are:

- If Required Resolution Date is Less than or equal to Current date - cell colour Red

- If Required Resolution Date is Between Current date + 1 and Current date + 4 - cell colour Orange

- If Required Resolution Date is Greater than or equal to Current date + 5 days - cell colour Green

 

Below are some screenshots of the results (Current date was 12 February 2019).

rag-results-red-amber.png

rag-results-green.png

A common format in RAG reports is to use an icon. You can also achieve this through conditional formatting.

 

Create a traffic light icon field based on resolution due date

Add another column to your report. I have added another Required Resolution Date column/field and I have renamed it 'RAG'.

 

Under Conditional Formatting select Icon in the Style drop down menu.

 

The Icon Set menu will appear. Select Traffic Lights. Set up your alerts in a similar way as the screenshot below:

icon-traffic-light-formatting.png

Here is the resulting report.

rag-icon-report.jpg

Filter Blog

By date:
By tag: