How To: Cireson Analytics

About Cireson Analytics

Cireson Analytics is a dramatically simple SCSM reporting and dashboard solution that is easy to set up and makes use of the existing Dashboard Designer for easy work item data visualization.

This feature introduces the Cireson Analytics database where the whole historical reporting will be based on. The said database will contain anywhere from one up to ten years of information, depending on what is set in the data retention setting, of raw work item data from the ServiceManagement database. Each work item type and related configuration items will have its own corresponding table in the Analytics database. All work item data will be sourced from the ServiceManagement database (installed with the Cireson Portal), while all configuration item relationship data will be source from the ServiceManager database.

There will be scheduled SQL agent jobs in the Analytics database that will transfer work item data from ServiceManagement tables on a specified schedule, set forth during the installation. By default, the job sync schedule is set to every 24 hours, however this can bemodified during installation to suit your organization's requirements and policies.

There are three types of jobs that will be running; one for adding new records, one for updating existing records, and one for deleting or grooming of old records. The resulting jobs will bring in the following data into to the Cireson Analytics database:

Work item details including status and date info
Affected and Assigned User
SLO Status
Related CIs

Attachments and Assets are not included in the initial release of Cireson Analytics, and will be added in a future release.

Installation

To install Cireson Analytics, users will need to select the Install Cireson Analytics option under Configuration -> Select Components to Install.

Once Install Cireson Analytics is selected, Cireson Analytics Settings will be added as a page during the installation process. Here, users can complete the following:

Service Account
- The database administrator account name.
Service Account Password
- The database administrator account password.
Analytics Database Server Name
- The location path for the analytic database server. This is typically the same server that contains the ServiceManagement database.
Analytics Database Name
- The name of the analytic database. By default, this is CiresonAnalytics, but you may give it a different, unique name.
Select a date for First Data Sync
- Determines when the SQL agent scripts should run. By default, this is set to the install date and time, but it can be changed to a different date and time that suits you and/or your organization best.
Run Data Sync every X minutes
- Determines how frequently the SQL agent scripts will run in minutes. By default, this is set to 1440 minutes.
Data Retention Settings
- Determines the duration, in days, how the users would like to retain the work item data in the analytic database. This can be set anywhere from 1 (one day) to 3650 (approx. 10 years), depending on how much data you wish for the database to retain.

Once installation is complete, there will be database and portal dashboard changes which will be discussed further in the next sections.

Database and SQL Agent Scripts

The installer will create the Cireson Analytic database (CiresonAnalytics, unless changed during the installation) and the work item table, as shown below:

These are the following SQL Agent Jobs added upon installation:

Cireson Delete Aged Work Items - deletes work items older than the age threshold (in days) set in the SM_Data_Retention table.
Cireson Insert Display Strings – adds display string items from service management to SM_DisplayString table.
Cireson Insert New Changes – adds new change request items from service management to SM_WorkItem_Change table.
Cireson Insert New Incidents - adds new incident request items from service management to SM_WorkItem_Incident table.
Cireson Insert New Manual Activity - adds new manual activity items from service management to SM_WorkItem_ManualActivity table.
Cireson Insert New Problems - adds new problem items from service management to SM_WorkItem_Problem table.
Cireson Insert New Relationships - adds new related configuration items from service manager to SM_WorkItem_Relationship table.
Cireson Insert New Releases - adds new release record items from service management to SM_WorkItem_Release table.
Cireson Insert New Review Activities - adds new review activity items from service management to SM_WorkItem_ReviewActivity table.
Cireson Insert New Service Requests - adds new service request items from service management to SM_WorkItem_ServiceRequest table.
Cireson Update Existing Changes – copies any changes of the change request record from service management to SM_WorkItem_Change table
Cireson Update Existing Display Strings - copies any changes of the display string record from service management to SM_WorkItem_DisplayString table
Cireson Update Existing Incidents - copies any changes of the incident request record from service management to SM_WorkItem_Incident table
Cireson Update Existing Manual Activities - copies any changes of the manual activity record from service management to SM_WorkItem_ManualActivity table
Cireson Update Existing Problems - copies any changes of the problem record from service management to SM_WorkItem_Problem table
Cireson Update Existing Relationships - copies any changes of the related configuration item record from service manager to SM_WorkItem_Relationship table
Cireson Update Existing Releases- copies any changes of the release record from service management to SM_WorkItem_Release table
Cireson Update Existing Review Activities- copies any changes of the review activity record from service management to SM_WorkItem_ReviewActivity table
Cireson Update Existing Service Requests - copies any changes of the service request record from service management to SM_WorkItem_ServiceRequest table

Historical Analytic Dashboards

With Cireson Analytics, there will be a new section named Historical, which contains the predefined Analytics pages and dashboards. Each of these dashboard and pages are discussed in detail below:

Assigned Work

The Assigned Work page will contain the four following dashboards by default:

Average Number of Incidents by Analyst – a count chart that depicts average number of incidents per service desk employee within measurement period.
Average Number of Service Request by Analyst – a count chart that depicts average number of service requests per service desk employee within measurement period.
Top Analysts with Most Incidents
Top Analysts with Most Service Request

Unassigned Work

The Unassigned Work page will contain four dashboards, namely:

% of Unowned Incidents - Percentage of open incident that does not have a primary owner
% of Unassigned Incidents - Percentage of open incident that are not assigned to a person or group.
% of Unowned Service Requests - Percentage of open service requests that does not have a primary owner
% of Unassigned Service Requests - Percentage of open service request that are not assigned to a person or group.

Overdue Work

The Overdue Work page will contain two dashboards, namely:

% of Overdue Incidents - Number of overdue incidents (not closed and not resolved within the established time frame) relative to the number of open (not closed) incidents.
% of Overdue Service Requests - Number of overdue service requests (not closed and not resolved within the established time frame) relative to the number of open (not closed) service requests.

Response Time

By default, the Response Time page is not visible to users. Your Cireson Portal administrator will need to enable visibility in the Navigation Admin page to be available for users. The page can otherwise be scoped for specific user or group access using the Assigned AD Groups functionality in the same page

The Response Time page will contain two dashboards, namely:

(Incident) Average Response Time - The average amount of time (e.g. in minutes) between the detection of an incident and the first action taken to repair the incident.
(Service Requests) Average Response Time - The average amount of time (e.g. in minutes) between the detection of a service request and the first action taken to address the service request.

Target Resolution

The Target Resolution page will contain two dashboards, namely:

(Incident) % of Work Items Resolved Before Breach - Number of incidents resolved within the allowed duration time-frame, relative to the number of all incidents closed in a given time period (day). A duration time-frame is applied to each incident when it is received, and sets a limit on the amount of time available to resolve the incident. The applied duration time-frame is derived from the SoonestSLOStatusBreach field.
(Service Request) % of Work Items Resolved Before Breach - Number of service requests resolved within the allowed duration time-frame, relative to the number of all service requests closed in a given time period (day). A duration time-frame is applied to each service requests when it is received, and sets a limit on the amount of time available to resolve the incident. The applied duration time-frame is derived from the SoonestSLOStatusBreach field.

Top Affected Users

The Top Affected Users page will contain two dashboards, namely:

Top Affected Users with Most Incidents – List of the top 10 affected users that has the most number for incidents associated to them
Top Affected Users with Most Service Requests – List of the top 10 affected users that has the most number for service requests associated to them

Top Categories

The Top Categories page will contain two dashboards, namely:

Top Categories with Most Incidents – List of the 10 most commonly used category in an incident
Top Categories with Most Service Requests – List of the 10 most commonly used category in a service request

Top Config Items

The Top Config page will contain three dashboards, namely:

Top Configuration Items with Most Incidents – List of the 10 most commonly associated configuration item in an incident
Top Configuration Items with Most Service Requests – List of the 10 most commonly associated configuration item in a service request
Top Configuration Items with Most Change Requests – List of the 10 most commonly associated configuration item in a change request

Analytic Dashboard Settings

In the Analytic Dashboard Settings, these are the following settings that users need to keep in mind:

License Setting
- A valid dashboard license is need for Analytics to work as desired

Enable/Disable Analytics
- Aside from a valid dashboard license, Cireson Analytics must also be enabled from the Setting Items Table. You can enable EnableCiresonAnalytics by going to Admin Settings->Setting Items, then setting the item to True. Setting it to false will hide all predefined historical dashboards and other analytic chart features.

Analytics Settings
- Users can modify the work item data retention value on Admin Settings -> Analytics Settings. To Modify, simply change the values on the numeric picker and click on save to save the changes.

Dashboard DataSource Settings
- Users can add or modify their custom data sources for dashboards and analytics on Admin Settings -> Dashboard DataSource Settings.

How to create Analytic dashboard page

Create a custom page through navigation page.
Once page is created, add an SQL Chart Widget
Click on the edit (gear icon) button to configure the chart widget by supplying title, description, datasource, the actual sql query, and chart type.

Sample Query Used:

select DS.DisplayString, m1.CategoryId, m1.Created from SM_WorkItem_Incident as m1 inner join (select TOP 25 CategoryId, Cnt from (select CategoryId, COUNT(CategoryId) as Cnt from SM_WorkItem_Incident group by CategoryId) m order by Cnt desc) m2 on m1.CategoryId = m2.CategoryId JOIN SM_DisplayString DS ON m1.CategoryId = DS.ElementID where DS.LocaleID = 'DEU' AND @createdFilter order by Cnt desc

4. Click on apply to save the changes