admitting in the first stage: it's the first time I try to set up automated (scheduled) reports within UIM
I've got a task of setting up daily/monthly CSV reports on specific UIM QOS data metrics but can't seem to find a proper solution.
There are several reporting utilities and terms related to UIM making it - to be honest - quite confusing to me:
- Unified Reporter
- Reports Portlet
- Report Scheduler Portlet
- Performance Reports
- List Reports
- Performance Report Designer
- List Designer
The very basic needs:
- select one or multiple sources
- select one or multiple targets
- select one or multiple QoS
- select a relative timeframe like "last month"
- select an execuation interval like "at 06.00 on first day of each month"
- cool optional addon feature: specific sources and targets by wildcard
Within UMP Reporting utilities, I'm able to get on demand QoS data display and manual export, but no scheduling.
While I don't even see how to launch The Report Scheduler Portlet, it is documented to deliver PDF format only.
We've got a CABI (JasperReports) ingtegrated with UIM, where I managed to define a report (based on an adhoc view) showing the required data. It would be easy to schedule repeated execution, but I can't seem to find a way to get parameter input controls to specify relative time ranges, e.g. "previous month". Furthermore, the timestamps just show day/month/year but no hour/minute/second.
I'm sure it would be technically possible to create a script issueing SQL queries to the UIM database directly. However, a preferred solution would make use of existing product features.
Any hints would be greatly appriciated.
What info/fields do you need/want in your csv file?
For each of those selected QoS do you want each polled samplevalue or do you want an average?
What is the goal of the report?
Hi Luc Chrlu03,
great to "hear" from you.
one QoS metric example would be QOS_SQLSERVER_check_dbalive which was selected to report on SQL server availability. The ultimate goal is to provide a availability percentage figure per month per SQL instance in a simple list like the following to the customer:
The basis to calculate that obviously is a list of all metric values wihtin the timeframe including their respective timestamps. The percentage calculation could be done within an external reporting system when needed, hence I want to focus on getting the raw metric values including timestamps and source (SQL instance) in a machine readable format and automatically scheduled first. However, I'm not going to block a full blown solution at all.
thanks a lot and best regards,Raphael
I think that a customized CABI (Jaspersoft) report would be the easiest way to go. (+ it's a common reporting tool among multiple products)
- First step is to create a sql query that gives you the data/output you want, like:
- next step is to take this into Jaspersoft studio to create a report with the layout you want, like: (here an example done with the net_connect qos)
- than you can publish your report your report into CABI, where it can be:
- executed online from the Portal (via webcontent)
- scheduled and result mailed
But if you need that info in a CSV file you can use the sql query and write the output into a CSV file.
In attachment example SQLCMD command to write availability overview for your QoS to csv file
(in example over the last 10 days)
Removed previous example because the sql query contained an invalid samplevalue translation. (availability was always 100%)
Attached here a corrected version