Search
ServicePilot provides access to the data it collects as graphs, tables and summaries as well as the raw data as stored in its databases. All of this is filtered and categorized to produce useful statistics.
When deploying resources, the package templates come with pre-defined dashboards to display information related to these resources. These dashboards might already show the information you require but it is possible to refine these searches or create completely new queries.
Data types
There are two main data types for information stored by ServicePilot in its databases. String fields store text. Integer fields store numerical data.
Indicator thresholds
When testing thresholds, String indicators can only use the = and <> operators while Integer indicators can also be checked using < and > to see if the value is greater or less than a limit.
As well as fixed thresholds, Integer indicators can also have thresholds set against analytical analysis of the data series. If machine learning spikes or cluster alerts are detected, these can also set indicator status. More information about Data Anomalies can be found under What is a threshold?
Database Query operators
When extracting data from the ServicePilot database, a number of series of data are collected to be presented. Each series is collected within the same time period specified. Either a Field is specified and a Field Statistic operator or a Formula is applied to the data.
Field statistics
Field Statistics are limited by the type of data being queried.
| Data Type | Field Statistic | Use |
|---|---|---|
| String & Integer | Count | Count the number of records in the database that contain this field within the time period specified. Not all records in the database contain all of the fields. If a count of all records is needed, count with the id index field that is present in all records. |
| First value | Obtain the first value of the field selected within the time period specified. | |
| Last value | Obtain the last value of the field selected within the time period specified. | |
| Missing data | Count the number of records in the database that do not contain this field within the time period specified. | |
| Unique | Count the number of different values that the field selected takes within the time period specified. | |
| Integer only | Sum | Sum all of the values for the field specified within the time period specified. |
| Mean | Find the average for all of the values of the field specified within the time period specified. | |
| Min | Find the smallest value from all of the values of the field specified within the time period specified. | |
| Max | Find the largest value from all of the values of the field specified within the time period specified. | |
| Sum2 | The squared sum all of the values for the field specified within the time period specified. | |
| Percentile | An estimated value for the specified percentile from all of the values for the field specified within the time period specified. For example: The 20th percentile is the value below which 20% of the observations may be found. | |
| Spike | Count the number of values that start falling outside a dynamically calculated range around the plot of values from the field specified. | |
| Level | Count the number of times that the values start causing the dynamically calculated range to change significantly from the field specified. | |
| Trend | A sliding trend expressed in as a percentage for the field specified. | |
| When | A time forecast as to when this field will cross the critical threshold associated with this indicator. |
The more complicated statistical formula are best represented graphically using an example graph. This graph plots one indicator with associated range, spikes outside the range and range level changes.
Formulas
Rather than obtaining a statistic directly from the data, a Formula can be applied to create a new series from the existing series. For example: A series is defined obtaining the sum of inbound network traffic and a second series is defined, obtaining the sum of outbound traffic. A third series is then defined as the sum of the first two series so that total traffic can be viewed.
| Formula | Use |
|---|---|
| sum(x,y,...) | Add the series values together. Example: sum(series0,series1) |
| sub(x,y) | Subtract the second series from the first. Example: sub(series0,series1) |
| product(x,y,...) | Multiply the series values together. Example: product(series0,series1) |
| div(x,y) | Divide the first series by the second. Example: div(series0,series1) |
| termfreq(series#,term) | Count the number of times term is found in the series. Example: termfreq(series0,'ok') |
| viewpath(series#) | Obtain the view hierarchy for the series. Example: viewpath(series0) |
| status(series#) | Obtain the status of the series. Example: status(series0) |
Custom database queries
As well as widgets querying the ServicePilot database in dashboards and PDF reports ad-hoc queries can be run from the Search menu. These queries can be refined to create new widgets if needed. The data returned will always be filtered automatically based what a particular user is allowed to monitor.
There are two ways in which queries can be made.
- Query - Simple SQL syntax
- Widget - Lucene syntax
Simple SQL queries extract a single series of data based on a database with a selection criteria, a grouping operator and a filter. Data is presented as a tree of items or an area map of entries based on the relative size of the values returned. When selecting an item from the tree or map the corresponding table of data is displayed.
Lucene queries use the Apache Lucene database query syntax. Data can then be presented in many forms. The query and the presentation form together are represented as widget definitions.
Perform an SQL query
To perform a simple SQL query in one of the ServicePilot databases go to the Query page.
| 1. Start by opening the Widget page | |
| 2. Change the time span to the smallest time period to reduce query times while developing a new custom query | |
| 3. Go to the Query page | |
| 4. Set the FROM, SELECT and GROUP BY fields as well as an optional Query - WHERE | |
| 5. Add a query filter and click on Apply | |
Custom searches can be saved with the Save button. These can then be retrieved and deleted in the Saved queries section of the example queries.
When a group of data is selected in the presented response, it is possible to Copy the query for use as a custom widget in a dashboard or PDF report template. It is also possible to use the Create Monitor button to add a new resource to the configuration that will continually query the database and generate a new statistics.
SQL query data presentation
Queries present data in one of two ways:
| Tree | Map |
|---|---|
| Results presented as a tree of folders each represending a group as defined by the group by filter. When a group is selected, the associated table of data is shown. | Results presented as an area map with the size of each area relative to the value associated with the group as defined by the group by filter. When a group is selected, the associated table of data is shown. |
 |
 |
The result will be displayed after clicking on an item in Tree or Map mode:
Perform a Lucene query
To perform Apache Lucene queries, use the Widget page to define both your query and data presentation in the form of a widget. See the Create custom widgets section below.
Create custom widgets
If the existing dashboards do not present the data in the form you want, new custom widget can be created and stored for use in custom dashboards and PDF reports. See the Dashboards page for more details.
Select widget source data
| 1. Start by opening the Widget page | |
| 2. Change the time span to the smallest time period to reduce query times while developing a new custom query | |
| 3. Select a Search Template as a starting point, based on the data to be queried. Templates are organised by the database in which the data is stored. Existing custom searches are presented at the bottom of the list under custom. | |
| 4. Select the Charts menu |  |
| 5. Click on the Event view button to see the raw data as it is found in the database |  |
| 6. Expand a record by clicking on the > icon and use the mouse wheel to scroll up and down the list of fields available in the record | |
| 7. To change the way in which the events are presented, click on the Properties button and select which data to show and highlight in the table as well as toggling the graph visibility |  |
It is possible to present this data directly as a table of records, however it is almost always more useful to filter, summarize, sort and graph the data.
Define widget data series
Series can be added, modified and deleted from the Series list at the top left of the screen.
A number of different series might be defined and displayed in the same widget. Each series has three critical parameters; Field or formula, Field Statistic and Title.
Other series parameters depend on these initial settings. Note the Table and Heat map tabs in the Series Settings Dialog contain further settings for how this series data will be presented.
Widget data presentation
Once the data is selected and filtered, it can be presented in many different ways. Use the Widget presentation buttons to select how the data is to be visualized.
| View Type | Use |
|---|---|
 - Chart |
Display a chart of series data. Each series will produce a different line or value on the chart depending on the chart type selected. |
 - Table |
View each of the series as a column in a table. The table rows are defined by the Group by filter. |
 - AvailPerf |
Display the availability and performance of views or objects over time. The table rows are defined by the Group by filter. |
 - Chart Top |
Display a chart of the most important groups. The data is first grouped by the Group by field and then a series is selected as the Top series. One line is presented on the graph for each of the top 10 groups form the top series. Each series will produce a different line or value on the chart depending on the chart type selected. For example: A Top Disk Space usage graph might be generated by setting the Top series to an Average Disk Space Usage indicator series and the Group by field to object to separate the data by disk. |
 - Mapping |
Display a map relating different elements. Values are presented for each series in a relationship between two elements. Each series will produce a different graph depending on the chart type selected. |
 - Scatter |
A scatter plot comparing one series against a second series. Each point on the plot is defined by the Group by filter. |
The major categories of widget presentation can then be visualized in a number of different forms.
| View Type | Use |
|---|---|
 - Bar chart |
Display a bar chart over time. The series are stacked on top of each other. |
 - Area chart |
Similar to the Bar chart the Area chart display stacked series over time except as area charts. |
 - Line chart |
Display one line per series and overlay them in a graph over time. |
 - Trend |
Display trend lines for each series over time. |
 - Capacity |
Display a series' trend changes in a table with an indication of when an element would be expected to cross the critical threshold if set. |
 - Heat map |
Display a series' values over time as a number of colored squares with the shade of the square representing the value. |
 - Pie chart |
Display a pie chart with each slice representing a series' value. Each slice size will be a relative percentage of all of the series values. |
 - Donut chart |
Similar to the Pie chart, the Donut chart displays ring sections for each series. |
 - Counters |
Display a single value for each series from all of the data of the time span specified. |
 - Bar chart |
Display a single bar per series or group from all of the data of the time span specified. |
| - Events |
Display the raw list of event records in the database, one per line. A bar chart of data can also be included. |
 - Geo Map |
Display a geographic map with a geo-located pins grouping records from each IP address. |
 - Geo Country |
Display a geographic map grouping records per country based on IP addresses. |
Mapping menu display
| View Type | Use |
|---|---|
 - Topo graph |
Display a graph of relationships between equipement with series values on each line between elements. |
 - Topo top-down |
Display a number of trees with source roots at the top and destinations beneath. Series values are placed on the lines between the elements. |
 - Topo left-right |
Display a number of trees with source roots to the left and destinations to the right. Series values are placed on the lines between the elements. |
Manage widget buttons
Widgets templates may be copied, cloned, edited, saved and deleted using buttons at the top right of the Widget page. Once saved, custom widgets can then be used to build dashboards and PDF report templates.
| View Type | Use |
|---|---|
| Copy | Copy the current widget definition to your clipboard as a JSON widget definition. This can be pasted into a dashboard or PDF report template of into the JSON widget editor accessible via the JSON button. |
| JSON | Open the JSON widget definition editor. The widget may be edited as text in this dialog. When OK is pressed, the Widget web page will update to reflect changes made to the widget definition. |
| Delete | If a custom widget template was selected, this template may be deleted from the list with this button. |
| Save | Once a new widget has been developed and tested, it can be given a name to save it to the custom widget list. Selecting an existing name will overrite a custom widget template or providing a new name will create a new template. Custom widget names can be selected when editing dashboards and PDF report templates. Note that when a widget is imported into a dashboard or PDF report template, a copy of the definition is taken. This means there is no link between the custom widget definition and the dashboard or PDF report template into which it was included. |
