Chart definitions

Cloud Identity Service supports bar, pie, ring, time, and XY charts. Any number of charts can be defined.

The most important properties of a chart definition are chart type, query, and data source. These properties are used to generate the chart dynamically at run time by using a query. Each chart type requires a specific query format to generate a valid data set for the chart. The chart queries can also include parameters to be selected by the user at run time.

The X-Axis Label and Y-Axis Label fields are not used with pie or ring charts. The Plot Orientation field is used to determine the orientation of bar and XY charts. If the Show Values option is checked, a table that contains the chart values is displayed below the chart.

Bar chart definitions

Bar charts represent and compare data that is grouped in bars. Bar charts require a value, series, category, and five clauses to work correctly. The following format is required for bar charts:

SELECT value, series, category
FROM
WHERE
GROUP BY
ORDER BY

The following query produces a bar chart of the number of Logins and failed Logins for the past 7 days. The value, series, and category in this example are count(*), ae.audit_event_definition_id, and aed.info_text.

SELECT count(*), ae.audit_event_definition_id, aed.info_text FROM audit_event ae, 
audit_event_definition aed WHERE (ae.audit_event_definition_id = 15 
or ae.audit_event_definition_id = 16) and (ae.audit_event_definition_id 
= aed.audit_event_definition_id) and (ae.time_stamp > 
current_timestamp - interval '7 day') and (ae.time_stamp < current_timestamp) 
GROUP BY ae.audit_event_definition_id,aed.info_text 
ORDER BY ae.audit_event_definition_id,aed.info_text;

The following query produces a bar chart of the number of Logins and failed Logins from a date that is passed into the report. To use a parameter with a chart query, the matching report parameter must be added to the report. The value, series, and category in this example are count(*), ae.audit_event_definition_id, and aed.info_text.

SELECT count(*), ae.audit_event_definition_id, aed.info_text FROM audit_event ae, 
audit_event_definition aed 
WHERE (ae.audit_event_definition_id = 15 or ae.audit_event_definition_id = 16) 
and (ae.audit_event_definition_id = aed.audit_event_definition_id) and 
(ae.time_stamp > CAST($P{date1} AS date) - interval '7 day') and 
(ae.time_stamp < current_timestamp) 
GROUP BY ae.audit_event_definition_id,aed.info_text 
ORDER BY ae.audit_event_definition_id,aed.info_text;

Pie and ring chart definitions

Pie and ring charts represent and compare data that is grouped into segments of a sphere or ring. Pie and ring charts require a value, key, and five clauses to work correctly. The following format is required for pie and ring charts:

SELECT value, key
FROM
WHERE
GROUP BY
ORDER BY

The following query produces a bar chart of the number of Logins and failed Logins for the past 24 hours. The value, and key in this example are count(*), and aed.info_text.

SELECT count(*), aed.info_text FROM audit_event ae, audit_event_definition aed 
WHERE (ae.audit_event_definition_id = 15 or ae.audit_event_definition_id = 16) and 
(ae.audit_event_definition_id = aed.audit_event_definition_id) and (ae.time_stamp > 
current_timestamp - interval '1 day') and (ae.time_stamp < current_timestamp) 
GROUP BY ae.audit_event_definition_id,aed.info_text 
ORDER BY ae.audit_event_definition_id,aed.info_text;

XY chart definitions

XY charts are used to display values for two variables for a set of data, with relative positions on the X axis and Y axis. The following format is required for XY charts:

SELECT series, value1, value2
FROM
WHERE
GROUP BY
ORDER BY

Time chart definitions

Time charts are used to display values over a specified time. The following format is required for time charts:

SELECT series, time, value
FROM
WHERE
GROUP BY
ORDER BY