Performance Management

Invoking Sybase DWB from the Command Line

Before running this application from the command line, alter your terminal environment variable. We recommend that you access the application through the 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. Enter this variable 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 NMS workstation, enter the following at the command line:

• For SNM or HP OpenView on a Sun, set term=xterm_c.sun
  

• For HP OpenView on HP-UX, set term=xterm_c.hp
  

• For NetView for AIX, set term=xterm
  

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.

Step 6 Press Return to enter the default password.

Step 7 For detailed instructions on how to use this application, continue with the section "Using Report Writing."

Step 8 When you have finished using DWB from the command line, reset your terminal default to its original state by entering the following and pressing 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.

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 tables named sample*.

A brief description of the sample reports follow:

• devices---Reports which 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 as described in the previous section, "Invoking Sybase DWB from the Menu."

Step 2 In the main window of DWB, select Reports>Existing. (See Figure 4-14.)

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

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

Step 4 Select Actions>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.

Step 6 Click on the DWB window menu icon and select Close>Apply.

A report similar to the one in Figure 4-16 displays 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 using the Device Polling and Polling Summary applications. 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 substitutes MIB object names for current column names such as var1 and var2. Use the genmibview script on polling tables prior to CiscoWorks 2.0. Tables created with CiscoWorks 2.0 contain MIB object names for current columns automatically.

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

To create a new report, complete the following steps:

Step 1 Define the report in the Polling Summary application.

For information on setting your polling table, refer to the section "Creating Polling Tables Using Device Polling." For information on performing polling, refer to the section "Using Polling Summary" in this chapter.

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:

% cd $NMSROOT/etc

% genmibview -U user -P password 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 Reports>New.

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 Use>Table.

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 for 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>Apply.

The contents of the table appear in the window.

Step 9 Select all of the data columns in the table.

Step 10 Select Add>Result Columns. (See Figure 4-18.)

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

Step 11 Select Add>Summary Values.

The Add Summary Values window appears.

Step 12 Select the items to be averaged, then select Function>Average.
(See Figure 4-19.)

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

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

The SQL Batch Editor displays.

Step 14 At the Name prompt, enter a name to identify your SQL batch statement.

Step 15 Click on the DWB window menu icon in the upper left corner, and select Close>Apply. (See Figure 4-17.)

The Report Workbench window appears.

Step 16 Select Report>Processing.

Ensure that the filename is entered at the Process prompt.

Step 17 Select the Compile menu. (See Figure 4-20.)

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

Step 18 Click on the DWB window menu icon in the upper left corner and select Close>Apply.

Step 19 Select Report>Run.

The Standard Report Parameters window appears.

Step 20 Accept the default settings to run the report to the display.

Step 21 Click on the DWB window menu icon in the upper left corner, and select Close>Apply.

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

Step 22 To save the report, select Report>Save. (See Figure 4-21.)

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

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

Step 24 Click on the DWB window menu icon in the upper left corner and select Close>Apply.

Step 25 Click on Exit Report Workbench and press Return.

Running a Sample Report with SQL

Several SQL report-writing examples are presented in this section. 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. In the database, all fields are 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

The sections that follow describe how to use columns from static tables.

Creating Reports

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 query in the SQL parameter window:

select device_id, device_type, device_name, serial_number from 

devices where device_type = 0

Running the Poll

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

Reading the File

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

Processing 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. If you want to experiment, a screen display is usually most convenient.

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

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.

After you successfully run a poll and specify the set of data you are interested in, exit the Display Window (click the X icon) and then exit the SQL editor.

Select Reports>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.

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

These examples show a simple version of the results 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. By naming 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 request to do this follows:

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. Because no two networks are alike, the type of diagnostic or accounting information will be specific to your environment.

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

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 following example, 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; and the name of the new table is device_usage.

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

/* Report %Utilization for each interface in 15 minute intervals */
/*
 * Get the 15 minute totals for each interface that is operational
 */
select device_id, convert(int, inst) inst, sum(ifInOctets) ifInOctets, sum(ifOutOctets) 
ifOutOctets, max(ifSpeed) ifSpeed, min(timestamp) interval, datepart(dd, timestamp), 
datepart(hh, timestamp) hourint, convert(int, datepart(mi, timestamp)/15) minint
into #t1
from sample_view
where rec_type = 0
group by device_id, convert(int, inst), datepart(dd, timestamp), datepart(hh, timestamp), 
convert(int, datepart(mi, timestamp)/15)
having max(ifOperStatus) = 1 and max(ifAdminStatus) = 1
go

/*
 * Compute utilization levels for each interface. The computation is split
 * into serial and non-serial interfaces (full duplex and half duplex) by
 * using interface type.
 */
select substring(device_name, 1, 14) Device, interval, substring(interface_name, 1, 14) 
Interface,100 * convert(float, ifOutOctets)/ (15.0 * 60.0) /convert(float, (ifSpeed/8)) 
"% Utilization"
from #t1, devices, interfaces
where #t1.device_id = devices.device_id
and #t1.inst = interfaces.interface_id
and #t1.device_id = interfaces.device_id
and interfaces.interface_type in (2,3,4,5,16,17,18,19,20,21,22,23)
union
select substring(device_name, 1, 14) Device, interval, substring(interface_name, 1, 14) 
Interface,100 * convert(float, (ifInOctets + ifOutOctets))/ (15.0 * 60.0) /convert(float, 
(ifSpeed/8)) "% Utilization"
from #t1, devices, interfaces
where #t1.device_id = devices.device_id
and #t1.inst = interfaces.interface_id
and #t1.device_id = interfaces.device_id
and not (interfaces.interface_type in (2,3,4,5,16,17,18,19,20,21,22,23))
order by substring(device_name, 1, 14), interval, substring(interface_name, 1, 14)
go

/*
 * Clean up temporary tables
 */
drop table #t1
go

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)

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.

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

The remaining columns are determined by your definition of the polling table and contain the values of the polled MIB objects.

If the polled MIB objects are indexed by ifIndex, the inst column contains the appropriate value (for example, ifIndex or locIfxxx). You can join the inst column with the interface_id column in the Interfaces table. To join the two columns, use the following SQL syntax:

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

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.

Calculating Traffic Values on Serial Lines

Because serial lines are full duplex, there is no single way to perform a traffic calculation. The following calculation provides the best view:

traffic = max(ifInOctets, ifOutOctets)
utilization = traffic/line bandwidth

In general, however, it is often less complex and more direct to look at traffic as the amount of data transmitted by a given device onto a line. This is consistent with the router calculation that obtains a load value. In this case, use the following calculation (where the utilization is a function of which end of the serial line that you are measuring):

traffic = ifOutOctets
utilization = traffic/line bandwidth

If you use ifInOctets+ifOutOctets as a traffic measure for serial lines, your utilization levels appear to be greater than 100 percent.

To determine whether you have a serial line, check the interface_type column in the Interfaces table. Table 4-11 lists the serial interface types and the protocols associated with each interface. For detailed information on interfaces and protocols, refer to RFC 1213, Management Information Base for Network Management of TCP/IP-based Internets: MIB-II..

Table 4-11 : Serial Interface and Protocol Type

Serial Interface Type	Protocol Type
2	1822
3	1822
4	ddnx25
5	RFC 877-x25
16	lapb
17	sdlc
18	ds1
19	e1
20	ISDN
22	serial
23	PPP

To select serial interfaces, use the following with the WHERE clause:

select ...
where ...
and interfaces.interface_type in (2,3,4,5,16,17,18,19,20,21,22,23)

Improving Report Format

To improve the appearance of the output from your queries you can make simple with the Format option after you run a poll from the Data Workbench. For more complex changes in the appearance of the output use the Report Writer. You also can 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. 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.

Using Report Writer to Present Your Data

After you write and save your poll, you can focus on the presentation of data on the printed page. From the Data Workbench, select Reports>Define>Setup. Then select Queries, and from 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, a layout of all the elements of the report displays. 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 display 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 prompted to save your report in the database. Enter the report name and confirm your exit from the report menu.

For complete information about the Report command, 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. It is located in the $SYBASE/bin directory.

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:

% runrw user name.devicerpt -Uyourname -Ppassword

For more information about the runrw command, enter the following command:

syman runrw

Copyright 1988-1996 © Cisco Systems Inc.