The End-User Performance Management Pack contains SQL stored procedures. These stored procedures help to start analysis of End-User Performance data in third party applications. All described stored procedures are free to use in any Business Intelligence application of choice. Regardless of the tool used for analyzing the data, make sure there is a SQL connection to the SQL instance containing the SCOM data warehouse.
Historical data is, by default, kept for 400 days in the SCOM data warehouse. For each target and counter, data is aggregated by day and hour. The stored procedure below reflects these aggregations and returns availability and performance data for each simulation and simulation step.
dbo.TheBackbone_SMP04_BI_StepDataGet
Note: All data in the SCOM date warehouse is stored in UTC time zone, verify your regional settings and time zone while specifying the StartDate and EndDate.
| Column | Description |
|---|---|
| Date | Date value |
| Time | Time value, this value is always the same when data is aggregated by day |
| DateTime | Date and time combined |
| DateTimeUTC | Datetime value converted to UTC datetime |
| Location | Location or watcher running the simulation |
| Simulation | Name of the simulation. Related to Targets table for SLA definitions |
| StepName | Name of the step |
| StepNumber | Number of the step |
| SampleCount | Number of simulation runs within the datetime range according to the aggregations |
| Avail Critical | Number of milliseconds that the availability status was critical |
| Avail Disabled | Number of milliseconds that the availability status was disabled |
| Avail Healthy | Number of milliseconds that the availability status was healthy |
| Avail Planned Maintenance | Number of milliseconds that the availability status was planned maintenance |
| Avail Unavailable | Number of milliseconds that the availability status was unavailable |
| Avail Unmonitored | Number of milliseconds that the availability status was unmonitored |
| Avail UnPlanned Maintenance | Number of milliseconds that the availability status was unplanned maintenance |
| Avail Warning | Number of milliseconds that the availability status was warning |
| Perf Critical | Number of milliseconds that the performance status was critical |
| Perf Disabled | Number of milliseconds that the performance status was disabled |
| Perf Healthy | Number of milliseconds that the performance status was healthy |
| Perf Planned Maintenance | Number of milliseconds that the performance status was planned maintenance |
| Perf Unvailable | Number of milliseconds that the performance status was unavailable |
| Perf Unmonitored | Number of milliseconds that the performance status was unmonitored |
| Perf Unplanned Maintenance | Number of milliseconds that the performance status was unplanned maintenance |
| Perf Warning | Number of milliseconds that the performance status was warning |
| Performance (ms) | The average performance in milliseconds of all completed runs |
| Performance (sec) | The average performance in seconds of all completed runs |
| MinValue | The shortest simulation run in seconds |
| MaxValue | The longest simulation run in seconds |
Raw data is stored in the SCOM data warehouse for 10 days. The stored procedure below returns the raw simulation data including the defined thresholds.
TheBackbone_SMP04_BI_InstanceAnalyze
| Column | Description |
|---|---|
| Location | Location or watcher running the simulation |
| Simulation | Name of the simulation |
| Date | Date value |
| Time | Time value, this value is always the same when data is aggregated by day |
| DateTime | Date and time combined |
| DateTimeUTC | Datetime value converted to UTC datetime |
| Performance (sec) | The average performance in seconds of all completed runs |
| Warning Threshold | Warning threshold set for this simulation |
| Critical Threshold | Critical threshold set for this simulation |
Raw data is stored in the SCOM data warehouse for 10 days. The stored procedure below returns the raw simulation step data including the defined thresholds.
TheBackbone_SMP04_BI_StepAnalyze
| Column | Description |
|---|---|
| Simulation | Name of the simulation |
| StepName | Name of the step |
| StepNumber | Number of the step |
| Date | Date value |
| Time | Time value, this value is always the same when data is aggregated by day |
| DateTime | Date and time combined |
| DateTimeUTC | Datetime value converted to UTC datetime |
| Performance (sec) | The average performance in seconds of all completed runs |
| Warning Threshold | Warning threshold set for this simulation |
| Critical Threshold | Critical threshold set for this simulation |
