Page History

...

Retrieving UIM data in Excel 2007: Querying an established database connection

Follow these steps to To import data from an Uptime Infrastructure Monitor a UIM DataSource into Excel 2007:

1. Whenever you finish creating a connection profile to the up.time UIM DataStore, or open an existing connection profile, the Import Data dialog is displayed.
   

...

1. Image Modified

...

2. Click Properties.

...

1. Image Removed
    The Connection Properties dialog is displayed.
2. Click the Definition tab:.
   Image Added
3. In the the Command text box, enter the SQL statement that retrieves the data you wish to use in Excel. For example:
   

   Code Block
   language sql
   SELECT e.display_name, min(p.cpu_sys+p.cpu_usr+p.cpu_wio), max(p.cpu_sys+p.cpu_usr+p.cpu_wio), avg(p.cpu_sys+p.cpu_usr+p.cpu_wio), min(p.free_mem), max(p.free_mem), avg(p.free_mem)

...

1. FROM performance_aggregate p, performance_sample s, entity e WHERE p.sample_id = s.id

...

1. AND s.uptimehost_id = e.entity_id AND s.sample_time > DATE_SUB(now(), INTERVAL 7 DAY) AND s.sample_time < now()

...

1. GROUP BY e.display_name ORDER BY s.sample_time;

   This sample query retrieves CPU- and memory-related data that was collected by

...

1. UIM over the last seven days.
2. Click Click OK.
3. If you are prompted with the ODBC 3.51 Connector dialog box, ensure your user and password information is correct, then click click OK.
   Once your queried data is in an Excel sheet, you can work with it as required. The example below is an inserted table displaying CPU usage highs, lows and averages for all monitored systems over the last seven days.
   Image Modified

Retrieving UIM data in Excel 2003

...

To establish the

...

UIM DataSource as an external data source for use in Excel 2003

...

1. Start Microsoft Office Excel 2003.
2. On the Data menu, click Import External Data, and then click click New Database Query. The Choose Data Source dialog box is displayed:.Image Modified
3. On the Databases tab, select the

...

1. UIM ODBC data source.

...

1. The name of the data source in the list is the name you gave the

...

1. UIM DataStore when you created its profile in the ODBC Data Source Administrator Control Panel (in this example, the database is simply named "uptime").

...

1. Click OK.

...

1. The Query Wizard appears:.
   
2. Choose the columns (and tables) to include in your query, and then click click Next.
3. For the Filter the Filter Data Wizard step, if desired, provide specific rows to include in your query, then click Next.
4. For the Sort the Sort Order Wizard step, if desired, indicate how the data will be sorted, then click Next.
5. On the final Wizard step, opt to to View data or edit query in Microsoft Query, then click Finish. The Import Data dialog box is displayed:.
   Image Modified
6. Click Edit Query to open Microsoft Query and preview the retrieved data

...

1. .
   Image Modified
2. Click the the View menu, then click SQL to display the query. The columns you selected in the Query Wizard (step 4) will be displayed as the SQL statement:.
   Image Modified
3. Edit the SQL statment statement to refine which of the data from the selected columns will be used in the Excel sheet, then click OK to return to Microsoft Query.
4. Click Click File, then click Return Data to Microsoft Office Excel.

Once your queried data is in an Excel sheet, you can work with it as required.