1 2 Previous Next 17 Replies Latest reply: Mar 17, 2009 3:31 PM by mazz RSS

Export data from reports...

kevin gann Newbie

Is it possible to export data from the generated reports to say CSV or another text format? If not from the application is there certain SQL queries that may be performed on the database to extract this information?

  • 1. Re: Export data from reports...
    mazz Master

    We don't have any report exporting facilities (yet) - that certainly has been discussed and you aren't the first to ask for such a thing.

    That said, there is nothing stopping folks from performing their own SQL queries against the DB schema to obtain your own reports. You'd need to understand the schema in order to do this.

  • 2. Re: Export data from reports...
    kevin gann Newbie

    Is there reasonable documentation for understanding the schema? I don't have a problem reverse engineering it if there are some pointers on what the tables are meant to hold. :)

  • 3. Re: Export data from reports...
    mazz Master

    We really need to fill in the descriptions here ... maybe this is the impetus I needed to do it myself :)

    http://support.rhq-project.org/display/RHQ/Design-AllDBTables

    There is this too:

    http://support.rhq-project.org/display/RHQ/Design-DBSchema

    So, it depends on what you want - if you give us an idea of the kinds of things you are looking to report on, we can point you in the right direction as to the tables you'll need to query.

  • 4. Re: Export data from reports...
    kevin gann Newbie

    Thanks! I actually started viewing the schema using dbVisualizer which looks a lot like the schema documentation.

    How difficult is it to retrieve the basics of a particular server like CPU or memory usage over a certain time period?

  • 5. Re: Export data from reports...
    mazz Master

    That's because I believe those are snapshots of db-visualizer :)

    To get what you need (and obviously, its possible because these are the queries we run when we build the graphs in the UI) you would need to do a bunch of join'ing and union'ing of tables, due to the way the raw data is round-robined in the database.

    If you really only care about more coarse-grained data (say, hourly aggregates or daily aggregates) it would be simpler since you only have to join with one of the RHQ_MEASUREMENT_DATA_1H/6H/1D (1h=1 hour, 6h=6 hour, 1d = 1day).

    You need to work your way through the inventory to get to the resource you are interested in (RHQ_RESOURCE has all the resources in it - if you know the surrogate/primary key, that's ideal, otherwise, you'll have to query based on its name and possibly other information such as its parentage).

    You then need to determine which "schedule ID" represents the metric. Each metric definition has a schedule definition ID ("CPU utilization metric" for example). But all CPU resources have this definition - you need to narrow down the search and find the specific schedule ID for your specific CPU resource that you are intereseted in - a specific schedule ID is associated with the schedule definition and the resource you want. Once you get the schedule ID, you then look it up in the variaous RHQ_MEASUREMENT_xxx tables to get the actual data values.

    I just glossed over this and jotted things down from memory - so this isn't comprehensive, but hopefully, gets you started. I think I touched on the major things to worry about.

  • 6. Re: Export data from reports...
    mazz Master

    FYI:

    http://support.rhq-project.org/display/RHQ/Measurement+Subsystem

    That might be helpful to understand how the measurements are stored, compressed and eventually purged from the system.

  • 7. Re: Export data from reports...
    Greg Hinkle Newbie

    I think this could be done with a hook into our table component system. You could probably build a general solution to exporting any table of data to csv format (from the PageList object... and even use its pagination control hooks to request all the data for export).

    That way we could put a little CSV download icon on any table in our UI. Anyone want to try this?

  • 8. Re: Export data from reports...
    kevin gann Newbie

    That's a great idea and almost exactly what I'm looking to do.

  • 9. Re: Export data from reports...
    kevin gann Newbie

    I finally got a chance to work some more on this. Querying the rhq_resource I saw the connected column which I assumed would be whether the connected agent (parent) is "connected". In the RHQ dashboard I'm able to see the connected agent, browse, etc... but the connected column in the database never appears to update to true. I've looked at a specific resource_id for (resource_type of "JBossAS Server") and also the parent (root of the tree). I've waited approximately an hour with nothing changed. If I look at the rhq_agent the status is 0 which I assume is no error?

  • 10. Re: Export data from reports...
    Heiko Rupp Master

    Javadoc says about 'connected':

    * When true, it can be assumed the plugin can successfully connect to and manage the actual resource.
    * This means the plugin configuration is successfully set and has the proper values that allow the plugin to
    * connect to the managed resource.

  • 11. Re: Export data from reports...
    Heiko Rupp Master

    Actually we have all the code in the server layer already to pull the data out of the db -- so one would need to write a servlet that takes appropriate input (resource id/group id, start time, end time) and present the result as csv.

    If you are interested in working on this, contact me and I'll guide you through it.

  • 12. Re: Export data from reports...
    kevin gann Newbie

    That sounds great. I'd be more than willing to pull from a layer higher up than the database itself.

  • 13. Re: Export data from reports...
    mazz Master

    IIRC the connected column means the resource's plugin configuration is valid and the agent's plugin CAN connect to the resource (it isn't necessarily connected at this very moment in time).

    Look at RHQ_RESOURCE_AVAIL (the current availability for resources) and RHQ_AVAILABILITY (a run-length encoded history of when a resource was up or down)

  • 14. Re: Export data from reports...
    mazz Master

    BTW: I will be much easier to integrate with the higher-level API layer once we get web services implemented (on our list, unsure when it will get done). So you could have an external client make remote calls into the server and ask for the same kinds of data you see in the UI.

1 2 Previous Next