DX Infrastructure Manager

Expand all | Collapse all

UIM - scheduled QoS data CSV reporting

  • 1.  UIM - scheduled QoS data CSV reporting

    Posted 05-16-2019 11:01 AM

    Hi all,

     

    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

    - iReport

    - Reports Portlet

    - Report Scheduler Portlet

    - Performance Reports

    - List Reports

    - Performance Report Designer

    - List Designer

    - CABI

     

    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.

     

    regards,
    Raphael



  • 2.  Re: UIM - scheduled QoS data CSV reporting

    Posted 05-17-2019 06:56 AM

    Hi Raphael,

    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?

    Regards, Luc



  • 3.  Re: UIM - scheduled QoS data CSV reporting

    Posted 05-21-2019 04:28 AM

    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:

    server/instance;availability

    server1_in01;99,5

    server1_in02;99,9

    server05_03;100

    ...

    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



  • 4.  Re: UIM - scheduled QoS data CSV reporting

    Posted 05-21-2019 05:41 AM

    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



  • 5.  Re: UIM - scheduled QoS data CSV reporting

    Posted 05-21-2019 05:59 AM

    But if you need that info in a CSV file you can use the sql query and write the output into a CSV file.



  • 6.  Re: UIM - scheduled QoS data CSV reporting

    Posted 05-22-2019 03:48 AM

    In attachment example SQLCMD command to write availability overview for your QoS to csv file

    (in example over the last 10 days)



  • 7.  Re: UIM - scheduled QoS data CSV reporting

    Posted 05-23-2019 06:49 AM
      |   view attached

    Removed previous example because the sql query contained an invalid samplevalue translation. (availability was always 100%)

    Attached here a corrected version

    Attachment(s)