Page History

...

Follow these steps to establish the up.time DataSource as an external data source for use in Excel 2010:

1. Start Microsoft Excel 2010.
2. Open the Data Connection Wizard: click on Data on the menu bar, click on From Other Sources, then click From Data Connection Wizard.
Image Removed
3. Image Added
   The Data Connection Wizard appears:
   

...

1. Image Added
2. Select ODBC DSN as the type of data source you want to use to make a connection, then click Next.
3. In the next Wizard step, select the up.time ODBC data source, then click Next.  
   The name of the data source in the list is the name you gave the up.time DataStore when you created its profile in the ODBC Data Source Administrator Control Panel.
4. In the next step, select a database and specific table from the data source:
   Image RemovedImage Added
   The database name you select is the name you gave to the up.time DataStore when you created its profile in the ODBC Data Source Administrator Control Panel. The database table you select depends on the type of up.time data you want to import into Excel (in this example, we are importing aggregate performance data).
   
   
5. In the final Wizard step, remember the Friendly Name assigned to this connection profile, then click Finish to save the database connection profile.
   Note: Now that you have created an Office database connection (.odc) file for the up.time database and specified table, you will be able to use this existing connection to perform new database queries in future Excel sessions (on the Data tab, in the Get External Data group, click Existing Connections, then select the connection by clicking the Friendly Name). When you begin to work with data using an existing connection, Excel will prompt you with the Import Data dialog box, which you can use to modify your database query.
   When you create a connection to the up.time database for the first time, you will always be prompted with the Import Data dialog box; this dialog box is described in the next section.

Retrieving up.time Data in Excel 2010: Querying an Established Database Connection

Follow these steps to import data from an up.time DataSource into Excel 2010:

1. Whenever you finish creating a connection profile to the up.time DataStore, or open an existing connection profile, the Import Data dialog is displayed:
   
Image Removed
2. Image Added
   Click Properties.
3. The Connection Properties dialog is displayed. Click the Definition tab:
   
Image Removed
4. Image Added
   
   
5. In the Command text box, enter the SQL statement that retrieves the data you wish to use in Excel. For example:
    

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)
 FROM performance_aggregate p, performance_sample s, entity e 
WHERE p.sample_id = s.id 
AND s.uptimehost_id = e.entity_id 
AND s.sample_time > DATE_SUB(now(), INTERVAL 7 DAY) 
AND s.sample_time < now() 
GROUP BY e.display_name
 ORDER BY s.sample_time;

Code Block
language sql

1. This sample query retrieves CPU- and memory-related data that was collected by up.time over the last seven days.
2. 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 OK.
4. 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.

...

1. Image Added

...

Retrieving up.time Data in Excel 2007: Creating a New Database Connection

...