Page History
...
| Table of Contents |
|---|
|
Retrieving Uptime Infrastructure Monitor Data in Excel 2010: Creating a New Database Connection Retrieving Uptime Infrastructure Monitor Data in Excel 2010: Querying an Established Database Connection Retrieving Uptime Infrastructure Monitor Data in Excel 2007: Creating a New Database Connection Retrieving Uptime Infrastructure Monitor Data in Excel 2007: Querying an Established Database Connection
...
|
...
While Uptime Infrastructure Monitor has a powerful reporting function, you may want to generate reports that use a specific set of data and/or put that data into a custom report format. Using Microsoft Excel, you can take data from the up.time UIM DataStore and use that data to create a report that specifically suits your requirements.
...
Before you begin
If you have not already done so, install the MySQL ODBC driver and create a data source to access the up.time UIM DataStore. This article assumes you have performed these steps. For more information, see the see Connecting to the up.time Uptime Infrastructure Monitor DataStore via ODBC Knowledge Base article.
...
...
Retrieving UIM data in Excel 2010: Creating a
...
new database connection
To Follow these steps to establish the Uptime Infrastructure Monitor DataSource as an external data source for use in Excel 2010:
- Start Microsoft Excel 2010.
- # Open the Data Connection Wizard : click on Data on by going to the menu bar, click on and then clicking Data > From Other Sources, then click > From Data Connection Wizard.
...
- The Data Connection
...
- wizard appears
...
- .
- Select ODBC DSN as the type of data source you want to use to make a connection, and then click click Next.
- In the next Wizard step, select the Uptime Infrastructure Monitor UIM ODBC data source, and then click click Next. The name of the data source in the list is the name you gave the
...
- UIM DataStore when
...
...
- control panel.
In the next step, select a database and specific table from the data source:.
The database name you select is the name you gave to the
...
- UIM DataStore when
...
...
- control panel. The database table you select depends on the type of
...
- UIM data you want to import into Excel (in this example, we are importing aggregate performance data).
- In the final Wizard step, remember the Friendly Name assigned to this connection profile, then click Finish to save the database connection profile.
Info
...
Now that you
...
created an Office database connection (.odc) file for the
...
UIM database and specified table, you
...
can 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
...
prompts you with the Import Data dialog box, which you can use to modify your database query.
...
When you create a connection to the
...
UIM database for the first time, you
...
are always
...
prompted with
...
the Import Data
...
dialog box; this dialog box is described in the next section.
...
...
Retrieving UIM data in Excel 2010: Querying an
...
established database connection
To Follow these steps to import data from an Uptime Infrastructure Monitor a UIM DataSource into Excel 2010:
- 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:
...
- Click Properties.
...
The Connection Properties dialog is displayed.- Click the Definition tab:.
- In 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)
...
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()
...
This sample query retrieves CPU- and memory-related data that was collected by Uptime Infrastructure Monitor over the last seven days.GROUP BY e.display_name ORDER BY s.sample_time;
- Click Click OK.
- # 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.
...
Retrieving
...
UIM data in Excel 2007: Creating a
...
new database connection
To establish the UIM Follow these steps to establish the Uptime Infrastructure Monitor DataSource as an external data source for use in Excel 2007:
- Start Microsoft Excel 2007.
- Open the Data Connection Wizard : on the Data by going to the Data tab, and in the the Get External Data group, click From Other Sources, then click > From Data Connection Wizard. The Data Connection Wizard appears
...
- .
- Select Select ODBC DSN as the type of data source you want to use to make a connection, and then click click Next.In the next Wizard step, select the Uptime Infrastructure Monitor
- Select the UIM ODBC data source, and then click click Next. The name of the data source in the list is the name you gave the
...
- UIM DataStore when you created its profile in the ODBC Data Source Administrator
...
- control panel.
- In the next step, select a database and specific table from the data source:.
The database name you select is the name you gave to the up.time UIM DataStore when you you created its profile in the ODBC Data Source Administrator Control Panelcontrol panel. The database table you select depends on the type of Uptime Infrastructure Monitor UIM data you want to import into Excel (in this example, we are importing aggregate performance data). - In the final Wizard step, remember the the FriendlyName assigned to this connection profile, and then click click Finish to save the database connection profile.
...
| Info |
|---|
Now that you |
...
created an Office database connection (.odc) file for the |
...
UIM database and specified table, you |
...
can 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 prompts you with the Import Data dialog box, which you can use to modify your database query. |
When you create a connection to the Uptime Infrastructure Monitor UIM database for the first time, you will always be are prompted with the Import Data
dialog box; this dialog box is described in the next section.
...
Retrieving UIM data in Excel 2007: Querying an
...
established database connection
To Follow these steps to import data from an Uptime Infrastructure Monitor a UIM DataSource into Excel 2007:
- 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.
...
...
- Click Properties.
...
The Connection Properties dialog is displayed.- Click the Definition tab:.
- 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)
...
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()
...
This sample query retrieves CPU- and memory-related data that was collected byGROUP BY e.display_name ORDER BY s.sample_time;
...
- UIM over the last seven days.
- Click Click OK.
- 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.Anchor _bookmark40 _bookmark40
Retrieving
...
UIM data in Excel 2003
...
To establish the
...
UIM DataSource as an external data source for use in Excel 2003
...
- Start Microsoft Office Excel 2003.
- On the Data menu, click Import External Data, and then click click New Database Query. The Choose Data Source dialog box is displayed:.
- On the Databases tab, select the
...
- UIM ODBC data source.
...
- The name of the data source in the list is the name you gave the
...
- UIM DataStore when you created its profile in the ODBC Data Source Administrator Control Panel (in this example, the database is simply named "uptime").
...
- Click OK.
...
- The Query Wizard appears:.
- Choose the columns (and tables) to include in your query, and then click click Next.
- For the Filter the Filter Data Wizard step, if desired, provide specific rows to include in your query, then click Next.
- For the Sort the Sort Order Wizard step, if desired, indicate how the data will be sorted, then click Next.
- 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:.
- Click Edit Query to open Microsoft Query and preview the retrieved data
...
- .
- 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:.
- 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.
- 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.