Category Archives: Excel

How to import Enterprise Manager Grid Control Data into Excel and Create Attractive Looking Graphs and Charts

It is really not difficult to import performance data from your Enterprise Manager Grid Control setup and produce some very nice looking graphs from the data stored within.

Here’s one I made earlier.

Custom Oracle Enterprise Manager Graph / Report produced using Excel
A nice 3D graph of Enterprise Manager repository data demonstrating how much nicer Excel’s graphs are than Oracle OEM’s

 

Here’s how to do it:

  1. The first step is to install ODAC. You must do this because in my experience, on a Windows XP PC, Microsoft’s ODBC for Oracle driver doesn’t appear to handle Oracle’s sysdate at all well. You can download ODAC (11.2 Release 4 (11.2.0.3.0)  in my case) from Oracle’s rather swish download pages.
  2. The next thing to do is create an entry for your repository database. Make sure you edit the tnsnames.ora that is part of the ODAC installation or this won’t work.
  3. Create an ODBC data source using the tnsnames entry you created in step 2.
  4. Fire up Excel.
  5. From the menu bar select Data / Import External Data / New Database Query
  6. Select the ODBC data source you created in step 3.
  7. Enter your username and password. Username will be sysman, see your admin people if you don’t know the password.
  8. The MS Query query wizard shop pop up. Expand the the first table that appears in the Available Tables and Columns list and add the first row only to Columns in your query (for me the table is AQ$_MGMT_ADMINMSG_BUS_G and the first column is MSGID).
  9. Keep clicking next until you reach Query Wizard – Finish and you’re asked what you want to do next.
  10. Select the middle option – View Data or Edit data in Microsoft Query and click finish.
  11. If all goes well you should a Microsoft Query window containing another a visual representation of the query you just created
  12. Click on the AQ$_MGMT_ADMINMSG_BUS_G table ( or which ever table you used in step 8 ) and press delete. The table should disappear from the screen.
  13. Click the SQL button (at the very top of the window) and paste your query into the the SQL box that opens up then click OK. (You can use the query builder features of Microsoft Query but,  to be honest, that can take a while. What i usually do is take my base query (click here to see it or download it here: sample_oemrep_query.sql) and paste that in). NOTE: this query returns daily summaries up until the day before today – the query won’t return any data for today.
  14. Change your parameters to suit and Save the query if you wish (Irecommend this).
  15. Then click the Return Data button (its at the top of the window to the immediate left of the SQL button). You should find yourself back in Excel territory.
  16.  Choose where you want to put the data and click ok. You should have a spreadsheet full of Enterprise Repository data.
  17. Create charts and graphs and pivot tables and pivot charts to your heart’s content.

Beware! There is a caveat:

Your date must be formatted as YYYY-MON-DD or MS Query will have a fit. Your query, if you’re going to query on dates must contain something like:

MGMT_METRICS_1DAY.ROLLUP_TIMESTAMP>TO_DATE('2012-JAN-01','YYYY-MON-DD')

or for the last 90 days worth of metrics data..

MGMT_METRICS_1DAY.ROLLUP_TIMESTAMP >= SYSDATE - 90

 

Leave a comment if this is any use to you.

Video (and amendments coming soon!).
Seamus.

Generate your own custom graphs and reports from Oracle Enterprise Manager Grid Control 11g’s Repository Data

I like Enterprise Manager. Its lets me see what’s going on and what’s gone on and that’s great. BUT, i know that’s in upper case, the graphs/charts it produces aren’t much use. The look okay but they’re far too small and are limited in a number of ways – the main way being that you tend to only get one metric per graph (unless I’m doing something really here  and someone wants to tell me where I’m going wrong). The other day My boss asked me for some reports on database storage (ie how much space our databases take up) and such like and could he have some nice graphs to put into his PowerPoint presentation.. Okay said I grudgingly.  I merely saved one or two of the bitmap graphs Enterprise Manager produces and sent him those. “These are nice graphs but I want some better graphs. Something that would look good in a presentation,” said he. “Gulp,” said I.

After a bit of mooching around the EM repository database I found that it wasn’t that difficult to get the data I wanted.

I logged into the my the repository data as SYSMAN, had a nose around and found that i needed 3 tables to extract the data I needed to produce a graph for statistics gathered on a daily basis: MGMT_TARGETS, MGMT_METRICS and MGMT_METRICS_1DAY. I chose MGMT_METRICS_1DAY because it contained the daily summary/figures for the data I wanted. I figured these tables would give me daily figures for the data I needed. You can figure out how the tables are joined by looking at the query which should make an appearance quite soon…

I decided that I needed information only or Oracle Databases,  the total size of all datafiles used by my database[s] over a specific time span. I needed to set the following parameters:

  • MGMT_TARGETS.TARGET_NAME = ‘DLIVE’ – this is the name of my Oracle Instance as it is displayed in Enterprise Manager.
  • MGMT_METRICS.TARGET_TYPE = ‘oracle_database’ – this ensures only data for Oracle Databases is returned
  • MGMT_METRICS_1DAY.ROLLUP_TIMESTAMP > TO_DATE(‘2011-JAN-01’, ‘YYYY-MON-DD’) – I wanted my data from the 1st January 2011
  • MGMT_METRICS.METRIC_COLUMN = ‘ALLOCATED_GB’ – we’re only interested in storage allocated to the oracle database
  • MGMT_METRICS.METRIC_NAME = ‘DATABASE_SIZE’ – as i understood things we’re only interested in the size of the database (though the query works without this parameter!)

 

This SQL provided can provide the basis for all reports which make use of Enterprise Manager’s daily roll-up (ie summary) of a particular metric.

SELECT DISTINCT
                      MGMT_METRICS.TARGET_TYPE, MGMT_METRICS.TYPE_META_VER, MGMT_METRICS.METRIC_NAME, MGMT_METRICS.METRIC_COLUMN,
                      MGMT_METRICS.SHORT_NAME, MGMT_METRICS_1DAY.TARGET_GUID, MGMT_METRICS_1DAY.METRIC_GUID,
                      MGMT_METRICS_1DAY.KEY_VALUE, MGMT_METRICS_1DAY.ROLLUP_TIMESTAMP, MGMT_METRICS_1DAY.SAMPLE_COUNT,
                      MGMT_METRICS_1DAY.VALUE_AVERAGE, MGMT_METRICS_1DAY.VALUE_MINIMUM, MGMT_METRICS_1DAY.VALUE_MAXIMUM,
                      MGMT_TARGETS.TARGET_NAME
FROM                  MGMT_METRICS, MGMT_METRICS_1DAY, MGMT_TARGETS, MGMT_TARGET_TYPES
WHERE                MGMT_METRICS.METRIC_GUID = MGMT_METRICS_1DAY.METRIC_GUID AND
                      MGMT_METRICS.TARGET_TYPE = MGMT_TARGETS.TARGET_TYPE AND
                      MGMT_METRICS_1DAY.TARGET_GUID = MGMT_TARGETS.TARGET_GUID AND
                      MGMT_METRICS.TYPE_META_VER = MGMT_TARGETS.TYPE_META_VER AND
                      MGMT_METRICS.TARGET_TYPE = MGMT_TARGET_TYPES.TARGET_TYPE

Here’s the SQL with my parameters added:

SELECT DISTINCT
                      MGMT_METRICS.TARGET_TYPE, MGMT_METRICS.TYPE_META_VER, MGMT_METRICS.METRIC_NAME, MGMT_METRICS.METRIC_COLUMN,
                      MGMT_METRICS.SHORT_NAME, MGMT_METRICS_1DAY.TARGET_GUID, MGMT_METRICS_1DAY.METRIC_GUID,
                      MGMT_METRICS_1DAY.KEY_VALUE, MGMT_METRICS_1DAY.ROLLUP_TIMESTAMP, MGMT_METRICS_1DAY.SAMPLE_COUNT,
                      MGMT_METRICS_1DAY.VALUE_AVERAGE, MGMT_METRICS_1DAY.VALUE_MINIMUM, MGMT_METRICS_1DAY.VALUE_MAXIMUM,
                      MGMT_TARGETS.TARGET_NAME
FROM                  MGMT_METRICS, MGMT_METRICS_1DAY, MGMT_TARGETS, MGMT_TARGET_TYPES
WHERE                 MGMT_METRICS.METRIC_GUID = MGMT_METRICS_1DAY.METRIC_GUID AND
                      MGMT_METRICS.TARGET_TYPE = MGMT_TARGETS.TARGET_TYPE AND
                      MGMT_METRICS_1DAY.TARGET_GUID = MGMT_TARGETS.TARGET_GUID AND
                      MGMT_METRICS.TYPE_META_VER = MGMT_TARGETS.TYPE_META_VER AND
                      MGMT_METRICS.TARGET_TYPE = MGMT_TARGET_TYPES.TARGET_TYPE AND (MGMT_METRICS.TARGET_TYPE = 'oracle_database') AND
                      (MGMT_METRICS_1DAY.ROLLUP_TIMESTAMP > TO_DATE('2011-JAN-01', 'YYYY-MON-DD')) AND
                      (MGMT_METRICS.METRIC_COLUMN = 'ALLOCATED_GB') AND (MGMT_METRICS.METRIC_NAME = 'DATABASE_SIZE')
ORDER BY              MGMT_METRICS_1DAY.ROLLUP_TIMESTAMP, MGMT_METRICS_1DAY.SAMPLE_COUNT

The next thing i did was to import this data into Excel, created a pivot table with chart and filtered the data to suit my needs – my boss was after information on only once of our instances but I thought as I was doing this and it was a useful exercise I’d bring back data for all of my databases and knock out a graph fit for a King. You can see the graph here. NB: I filtered the data so that only DLIVE – the database my boss was interested in is displayed. I should warn you: don’t get too excited.. though I did add a trend line for extra added excitement so beware… 🙂

You can read how to import the data into Excel and produce some fetching graphs and charts here.

Example Enterprise Manager Report in Excel
Enterprise Manager Data Imported into Excel and Presented as a Custom Graph

 

 

Now, I thought that was a useful exercise because there’s a whole load of data (and information) held in the walls of Enterprise Manager but displaying that data how you want the data to be displayed is a different matter all together. I find the graphs in Enterprise Manager to be too small and of very little use when writing my monthly summaries for my Boss. Anyway, he knows how to create his own graphs now so a little bit of work has succeeded in getting him off my back. This was my first custom graph and there are loads more to come. I’ll post details if anyone is interested. or even document the full process and post that if there’s a demand for it.

 

Seamus

Solver for Mac Excel 2008

If you’ve used solver on the Windows version of Excel and wondered where it was in the Mac Editions then look no further. There’s a version for the latest versions of Mac Excel too

http://www.solver.com/mac/index2008.html