Performance Management

Invoking Sybase DWB from the Command Line

Before running this application from the command line, you need to alter your terminal environment variable. We recommend that you access the application through the SNM Tools menu because it alters the variable automatically. If you need to use the command line, follow these steps:

Step 1: To display the current terminal environment, enter the following at the command line:

% echo $TERM

Make a note of your terminal default. Type this variable in when you finish using Sybase DWB to return your terminal to use other CiscoWorks applications.

Step 2: To set your terminal variable to run using your Sun workstation, enter the following at the command line:

% set term=xterm_c.sun

Step 3: At the command line, enter the following:

% cd $NMSROOT/sybase/bin

Step 4: Then enter the Sybase DWB command:

% dwb

The DWB Login window appears. See Figure 4-12.

Step 5: Use the username user to log in. Press Return to enter the default password.

Step 6: Continue with "Using Report Writing" later in this chapter for detailed instructions on how to use this application.

Step 7: When you have finished using DWB from the command line, reset your terminal default to its original state. Enter the following and press Return:

% set term=original term setting

Using Report Writing

The Reports menu on the Sybase Data Workbench window enables you to run and print a report on any table created with the Device Polling application.

The following are two examples of how to use the report-writing feature:

• Visual query language (VQL)
  

VQL is a visually oriented front-end to SQL. VQL relies on menus and a point-and-select method to create the equivalent SQL.

• Structured query language (SQL)
  

An industry-standard database query language, SQL is commonly used with most well-known databases.

Running a Sample Report with VQL

This section describes how to run sample reports that are available in the CiscoWorks database.

There are three sample reports in the CiscoWorks database: devices, totals, and utilization. These reports correlate to the Sybase polling table named sample.

A brief description of the sample reports appear below:

• devices--Reports what network devices are in the database.
  

• totals--Calculates totals for numbers of packets, errors, broadcasts, maximum traffic, and uptime for each device.
  

• utilization--Calculates link utilization for each device interface.
  

To run the sample reports, perform the following steps:

Step 1: Access Sybase DWB from the Tools menu as described in the section, "Invoking Sybase DWB from the Tools Menu," earlier in this chapter.

Step 2: In the main window of DWB, select the Reports menu and pull down to the Existing command. See Figure 4-14.

Figure 4-14 : Sybase Data Workbench---Reports Menu

Step 3: Three reports appear: devices, totals, and utilization. Select one of the reports.

Step 4: Select the Actions menu and pull down to Run.

The Standard Report Parameters window appears. See Figure 4-15.

Figure 4-15 : Sybase Data Workbench---Standard Report Parameters Window

Step 5: Accept the default settings to run the report to the display. Click on the DWB window menu icon and select the Close option and pull over to Apply.

A report similar to the one in Figure 4-16 appears on your screen.

Figure 4-16 : Sybase Data Workbench---Standard Report

Creating a New Report with VQL

This section describes how to create a new report. To create a new report, define the report you want to run and then format it. You perform this process within the Device Polling and Polling Summary applications. You then need to run a script file that substitutes MIB object names for current column names such as var1 and var2.

The following examples use the default VQL layout. Refer to the Sybase Data Workbench User's Guide for details on how to customize the format of your reports.

To create a new report, complete the following steps:

Step 1: Define the report in the Polling Summary application.

Refer to "Creating Polling Tables Using Device Polling" for information on setting your polling tables and "Using Polling Summary" for information on performing polling.

Step 2: To check that your SYBASE environment variable is set correctly, enter the following at the command line:

hostname% printenv SYBASE

The system should respond with the location of SYBASE on your system, typically /usr/sybase.

Step 3: To run the genmibview script, enter the following at the command line:

hostname% cd $NMSROOT/etc
hostname% genmibview -Uuser -P tablename

The genmibview script creates separate tables that contain the MIB variable names instead of variable names such as var1, var2, and so on. The new table name will append _view to the original table name. For example, if the table name is traffic, the new table is called traffic_view.

Step 4: Select the Reports menu and pull down to the New command. See Figure 4-14.

The SQL Batch Editor window appears. See Figure 4-17.

Figure 4-17 : SQL Batch Editor Window

Step 5: Select the VQL! menu.

Step 6: Select the Use menu and pull down to the Table command.

The Use Table window appears over the center of the VQL window.

Step 7: At the Name prompt, enter the filename of the table that contains the variables on which you would like to run a report.

Step 8: Click on the DWB window menu icon in the upper left corner, select the Close option, and pull over to Apply.

The contents of the table appear in the window.

Step 9: Select all of the data columns in the table. Select the Add menu and pull down to the Result Columns command. See Figure 4-18.

Figure 4-18 : Sybase Data Workbench---Results Columns Command

Step 10: Select the Summary Values command.

The Add Summary Values window appears.

Step 11: Select the items to be averaged, then select the Function menu and pull down to Average. See Figure 4-19.

Figure 4-19 : Sybase Data Workbench---Add Summary Values Window

Step 12: Click on the DWB window menu icon in the upper left corner and select the Close option to exit again.

The SQL Batch Editor displays.

Step 13: At the Name prompt, enter a name to identify your SQL batch statement. Click on the DWB window menu icon in the upper left corner and select the Apply command under the Close option. See Figure 4-20.

The Report Workbench window appears.

Figure 4-20 : Sybase Data Workbench---SQL Batch Editor

Step 14: Select the Report menu and pull down to Processing.

Ensure the filename is entered at the Process prompt.

Step 15: Select the Compile menu. Click on the DWB window menu icon in the upper left corner and select the Apply command under the Close option. See
Figure 4-21.

Figure 4-21 : Sybase Data Workbench---Compile Menu

Step 16: Select the Report menu and pull down to Run.

The Standard Report Parameters window appears.

Step 17: Accept the default settings to run the report to the display.

Step 18: Click on the DWB window menu icon in the upper left corner and select the Apply command under the Close option.

A report similar to the one in Figure 4-22 appears on your screen.

Figure 4-22 : Sybase Data Workbench---Standard Report

Step 19: To save the report, select the Report menu and pull down to Save.
See Figure 4-23.

Figure 4-23 : Sybase Data Workbench---Saving the Report

Step 20: At the Save edited report as: prompt, enter the name of the report.

Step 21: Click on the DWB window menu icon in the upper left corner and select the Apply command under the Close option. Then click on Exit Report Workbench and press Return.

Running a Sample Report with SQL

Presented in this section are several SQL report-writing examples. Using these examples, you can construct your custom reports, print them on a printer or view them on the screen.

A data field contains a single item of information such as device name, device type, or postal code. Related columns of data fields are collected into tables. These collections of related information are arranged in records that relate to entities such as addresses, names, device names, and so on. These horizontal collections of records are also called rows. If you were to look at a database you would see all fields in columns and all records in rows.

For instance, the following columns are in the locations table:

• address_id
  

• location
  

• street
  

• street_two
  

• city
  

• state
  

• country
  

• zip_code
  

Static Table Queries

You can create reports using columns of data in the static database. For example, to create a quick report on devices of a certain type including type, name, and serial number, enter the following poll in the SQL parameter window:

select device_id, device_type, device_name, serial_number from 

devices where device_type = 0

Run the poll by selecting Sybase DWB. Select the SQL option from the Queries menu. When the SQL window appears, you can type the poll directly into the window, or read it in from a file.

To read a file into the window, select Recall Command and then From Host File. You will be prompted for the name of the file in which you have stored the poll.

Regardless of how you enter the poll, process it by selecting Run Command. You can display the results on screen, send them to a printer, or store them in a file. While you are experimenting, a screen display is usually most convenient.

Once the results come back, you can use the Format option. To format each column of your report. Refer to the Sybase Data Workbench User's Guide for detailed information. While in the display, you can scroll back and forth or up and down among the results.

If you want to produce hardcopy reports with pagination, headers, and footers rather than just columns of text, use Report Workbench to format the report. The Report Workbench will take your running poll and allow you to lay out the results for output to a printer using a WYSIWYG editor. If you have successfully run a poll and specified the set of data you are interested in, exit the Display Window (click the X icon) and then exit the SQL editor.

Choose the Reports menu item. It will display a submenu with two items: New and Existing. Choose New. This opens the SQL Batch Editor of the Report Workbench. Name the file in which you stored the poll and bring up the default report.

Refer to the Sybase Data Workbench User's Guide for a description of how to format your results for output to the printer.

These examples show a simple version of the results assume in an online poll in DWB:

device_id  device_type  device_name  serial_number
5   0  dross  NULL

Often, data you want is distributed across multiple tables. If you want to know more about the device type than just its number, you can include a description in your report by combining data from the devices and device_types tables. This means you name both tables in your FROM clause. You expand the WHERE clause to find only the relevant type description by linking (the technical term is "joining") the two tables on their shared columns.

The poll to do this would be:

select device_id, devices.device_type, device_name, device_types.

type_desc from devices, device_types where device_id = 5 and 

devices.device_type = device_types.device_type

Notice that a full name in tablename.columnname format is required whenever a column name is not unique in a poll. The results would look something like this, depending on how you formatted them:

device_id  device_type  device_name  type_descr
5   0  dross  default

You can have as many tables as you want, a poll (and results) drawing data from three tables could look like this:

select device_name, device_types.type_desc, vendor_name

from devices, device_types, vendors

where device_type =0 

and devices.device_type = device_types.device_type

and devices.vendor_id = vendors.vendor_id
device_name  type_descr  vendor_name  
dross   default  default  

Running Custom Table Queries

You can also use columns from static tables in conjunction with columns from custom tables you create. These custom tables include real-time data for the MIB variables you select. The following example illustrates what you can do once you get started. No two networks are alike and the type of diagnostic or accounting information you might find interesting is specific to your environment.

These examples are included only as a starting point for your own experimentation. Assume you need information about device utilization levels and uptime percent. Using Device Polling, Polling Summary, and Sybase DWB, you could produce this report:

For the period From: Mar 27 1992  5:44PM   To: Mar 28 1992  11:59AM 
Device          Interface         Pct Util
 --------------- --------------- --------------------
 abc            Ethernet0                  83.607993
 abc            Ethernet1                  75.815578
 abc            Ethernet10                 36.269430
 abc            Ethernet11                 83.352942
 abc            Ethernet12                 36.269430
 abc            Ethernet13                 36.269430
 abc            Ethernet14                 72.564532
 abc            Ethernet15                 82.645540
 abc            Ethernet2                  74.855613
 abc            Ethernet3                  97.716789

To obtain these results, use polling tables to collect information from the network and store it in the database. Then use SQL queries to display data from the database.

In the example that follows, the device is 5, the interfaces are 5, 11, and 12, and the MIB variables are: ifInOctets, ifOutOctets, and ifSpeed. The polling interval is 10 minutes. The name of the new table is device_usage.

The text of the poll that creates this report is as follows:

/*** Report of % Utilization, % Uptime by Device and Interface*/
SELECT device_id, SUM(sysUpTime)/100 uptime into #t4

FROM sample_view
WHERE rec_type = 0
GROUP BY device_id
go

select distinct device_id, convert(int, inst) inst into #t1
from sample_view
where rec_type = 0
order by device_id, convert(int, inst)
go

select #t4.device_id, inst, uptime into #t2
from #t1, #t4
where #t1.device_id = #t4.device_id
order by #t1.device_id, inst
go

select device_id, convert(int, inst) inst, sum(ifInOctets) ifInOctets, 
sum(ifOut
Octets) ifOutOctets, max(ifSpeed) ifSpeed
into #t3
from sample_view
where rec_type = 0
group by device_id, convert(int, inst)
order by device_id, convert(int, inst)
go

select substring(device_name, 1, 15) Device, substring(interface_name, 
1, 15) Interface,100 * convert(float, (ifInOctets + ifOutOctets))/ con
vert(float, (uptime))/convert(float, (ifSpeed/8)) "Pct Util"
from #t3, #t2, devices, interfaces
where #t3.device_id = #t2.device_id
and #t3.device_id = devices.device_id
and #t3.inst = interfaces.interface_id
and #t3.device_id = interfaces.device_id
and #t3.inst = #t2.inst
order by substring(device_name, 1, 15), substring(interface_name, 1, 
15)
go

drop table #t4
go

drop table #t1
go

drop table #t2
go

drop table #t3
go                         

Improving Report Format

You can improve the appearance of the output from your queries. Simple changes can be made with the Format option after you run a poll from the Data Workbench. More complex changes in the appearance of the output can be achieved using the Report Writer. You can also save your queries and reports and run them later from UNIX without using Data Workbench.

When you run the poll and display the results on the Data Workbench, the Format menu item lets you change the appearance of the results. The following are some of the things you can change:

• Column width
  

• Column headings
  

• Data alignment
  

• Column display order
  

The Format option is explained in more detail in the Sybase Data Workbench User's Guide. For hardcopy reports, make any additional changes with Report Writer.

Writing SQL Reports for Custom Polling Tables

To write SQL-based reports for analyzing data collected in polling tables, you must be familiar with CiscoWorks polling applications, Simple Network Management Protocol (SNMP), and structured query language (SQL).

Table 4-8 shows the columns that are always present in a polling table. The remaining columns are user-defined and contain the values of the polled MIB objects.

Table 4-8 : Columns in a Polling Table

Field Name	Field Size	Field Type
timestamp	datetime	Timestamp for each row of data polled
device_id	int	Join to devices table
sysUpTime	int	System up time in 100ths of a second
inst	char (255)	Index into MIB object table
rec_type	tinyint	0 = normal record, 1 = start record, 2 = not used, 3 = restart record, 4= irregular records

If the polled MIB objects are indexed by ifIndex, the inst column contains the appropriate value (for example, ifIndex or locIfxxx). This inst column can be used to join with the interface_id column in the Interfaces table. To join the inst column with the interface_id column, add the following to the SQL WHERE clause:

select ...
where ...
and convert(int, inst) = interfaces.interface_id

When you start polling a device, CiscoWorks creates a record with rec_type = 1. All the values in this record are absolute. All other records have rec_type = 0 and contain a mix of delta and absolute values. When you stop polling a device, you do not get any additional rows. A new rec_type value of 4 has been added to account for any anomalies or irregularities, which includes absolute values instead of delta values.

You can determine whether a MIB object will contain an absolute or delta value by getting its data type from the $SNMHOME/agents/cisco.schema file and identifying the value for each type. For example, sysUpTime uses timeticks as its record type, so the data value will be displayed as a delta value (the difference in value between the current poll and the last poll) if rec_type = 0.

For information on identifying the value for a data type, refer to Table 4-9.

Table 4-9 : Values for Types of Records

Record Type	Value
timeticks	delta
counter	delta
gauge	absolute
int	absolute

Specifying MIB Object Values

When you create a polling table in Device Polling, you specify the MIB object values that you want to collect. These values are collected by the poller and added to the database as the values are received. When polled values arrive at the poller in different packets, the values are written to the database in different records. Therefore, you might find that the data for a given poll is spread over two or more records, with NULL appearing where data was not available.

The following example shows how polling data is received over time and added to the database:

Polling:
                time    sysUpTime       var1    var2    inst
                 x         100           10               1
                 x+1       101                   15       1
                 x+2       102           10               2
                 x+10      110           20               1
                 x+11      111           25               2
Database storage:
                rec_type sysUpTime      var1    var2    inst
  1  100  10    1
  1  101    15  1
  1  102  10    2
  0   8  10   1
  0   1  15    2

You cannot perform row operations without first grouping and aggregating your data. You might want to use the timestamp column to perform groupings and then use the AVG or MAX (for absolute values) or SUM (for delta values) to aggregate. You can use the following GROUP BY clause to group by device, inst, and 15-minute intervals:

select ...
group by device_id, convert(int, inst), datepart(dd, timestamp),
datepart(hh, timestamp), convert(int, datepart(mi, timestamp)/15)

Using genmibview

In a custom polling table, the columns containing the polled object values are labeled
var[1-n], where n is the number of polled MIB objects. The genmibview script in the $NMSROOT/etc directory enables you to create a view of the polling table that uses the MIB variable names. Use the following command to create a table with meaningful column names:

$NMSROOT/etc/genmibview -Uuser -Ppassword tablename

The command creates a view called tablename_view. For example, to generate a view for a polling table named traffic, a user named netman and a password cisco, use the following command:

$NMSROOT/etc/genmibview -Unetman -Pcisco traffic

This command creates a view of the traffic table called traffic_view, with columns named after the MIB object names.

Computing sysUpTime Over a Polling Interval

To calculate time-dependent statistics for a device, calculate the total device uptime during the polling period. Create a temporary table containing the uptime for each polled device by using the following SQL commands:

select device_id, sum(sysUpTime)/100 uptime into #t1 from <table>
where rec_type = 0 group by device_id

The SQL commands create a temporary table called #t1 that contains device IDs and the uptime values for each device. The value is divided by 100 so that uptime will be in seconds (sysUpTime is in 100ths of a second).

Using ifOperStatus and ifAdminStatus

If you are polling interface values, include ifOperStatus and ifAdminStatus in your polling values to determine whether a given interface is operational. If you are using a GROUP BY clause in your SQL, add a HAVING condition to the group to check whether the interface was operational. Add the following commands to the group:

select ...
group by ...
having max(ifOperStatus) = 1 and max(ifAdminStatus) = 1

The possible values for ifAdminStatus are listed in Table 4-10.

Table 4-10 : Values for ifAdminStatus

Value	Meaning
1	up
2	down
3	testing

These values are also described in RFC 1213, Management Information Base for Network Management of TCP/IP-based Internets: MIB-II.

Using Report Writer to Present Your Data

Once you have written and saved your poll, you can focus on the presentation of data on the printed page. Under the Reports option of the Data Workbench, choose Define and then select Setup. Under Setup, select Queries, and under Queries, select SQL. The poll you run appears in the window.

In the window, you can recall queries from files and save them to files. When you use the Run Report command, you see a layout of all the elements of the report. You can edit the page layout and the data display, add trim, and change control break processing of the data. Run Report allows you to see the default layout on screen.

The poll will collect the set of data to be reported and the report writer will format the current set of data in the way you describe. When you exit from the Reports window, you will be notified to save your report in the database. Give the report a name and confirm your exit from the report menu.

For complete information about Report, refer to the Sybase Data Workbench User's Guide.

Running Reports from UNIX

You can run reports created and saved by the Data Workbench from the UNIX command line with the runrw command. The output of the report is automatically sent to your default printer.For more information about runrw, see the appropriate UNIX command reference manual furnished with your system.

The following is the command syntax for the sample report called storm_report:

runrw storm_report -Uyourname -Ppassword

To run a report owned by another user, enter the following at the command prompt:

hostname% runrw user name.devicerpt -Uyourname -Ppassword

Copyright 1988-1996 © Cisco Systems Inc.