Querying the Report Database

You can make direct queries to the data tables of the TrafficDirector application and view statistical information in an ad hoc report format. An ad hoc report is an impromptu report that you create for the specific variables you select. The ability to create your own ad hoc reports is a feature that complements the structured, instant reports available in the TrafficDirector application by letting you choose what is reported and how the information is sorted. You can use wildcard characters in some fields to construct a query. When you query the database, the result is an ad hoc report.

The following sections contain more information about querying the report database:

Understanding the SQL Database Tables

The TrafficDirector application uses a single database---NSTREND_DB. It contains all of the tables related to the TrafficDirector application. This database contains different tables that provide a wide range of useful statistical information. For example, the many different statistics that SwitchProbe devices collect for reporting are:

Ethernet segment statistics
Token Ring segment statistics
FDDI segment statistics
WAN segment statistics
Protocol statistics
Host statistics
Conversation statistics
Round Trip Delay (IP Ping) Statistics
Application Response Time (ART) statistics
Application Response Time (ART) Server statistics
SNMP-Get statistics
Network Conversation Matrix Top N statistics
Application Conversation Matrix Top N statistics

After you decide which type of statistics you want to view, you can select the level of detail you want reflected as the result of your query---either detail or daily-level.

These two types of data tables for each of the statistic categories are described as follows:

Detail---Contains data at intervals ranging from one minute to 24 hours. You query this database when you want to generate history reports that display data for intervals less than 24 hours.
Daily---Contains utilization data that has been averaged for the day, and for all other statistics, delta values that have been accumulated for the day. Each entry in the daily tables represents a 24 hour interval. You query this database when you want to generate history reports that display data at intervals of 24 hours or summary reports for any period greater than one day. For best performance, run all Summary Reports on the Daily tables (data is already accumulated).

Running IS QL\w on Windows Platforms

On a Windows NT system using the Microsoft SQL Server, you will use the administrative tools provided with the Microsoft SQL Server client and server software to perform SQL ad hoc queries to the NSTREND_DB. Using the ISQL\w tool provided with the SQL client software, you can print ad hoc reports or save them to a file.

See the Microsoft SQL Server Administrator's Companion manual for more information about using ISQL\w.

Running Embedded SQL (msql) on Windows Platforms

On a Windows NT system using the embedded SQL server, you will use msql to run customized reports using SQL queries.

To generate ad hoc reports, you first must run the msql utility. After doing so, you can use standard SQL queries to specify certain reports.

To run msql, follow these steps:

Step 1 Go to the DOS command line.

Step 2 Enter the following command:

$NSHOME/bin/msql NSTREND_DB

If you are using the TrafficDirector application for logging on remote hosts, you can query the remote server daemon using the following command (where remote01 is the name of a remote host):

$NSHOME/bin/msql -h remote01 NSTREND_DB

See "Generating Ad Hoc Reports" for more information about performing queries and generating customized reports.

Working with Embedded SQL (msql) on UNIX Platforms

On UNIX platforms, you use the msql program to run customized reports using SQL queries. Before you begin using msql, it is important that you set up several UNIX environment variables.

For more information, see the following sections:

Setting up UNIX Environment V ariables

The server daemon---msqld---and the tools that access this daemon, use specific environment variables that are set using the same script file (the file that the installation script creates during installation). The script file is also used to set other environment variables, including NSHOME and HHHOME.

Table 32-1 describes the variables you must set for Trend Reporter/SQL queries to work correctly.

Table 32-1: Variables Required for Trend Reporter and SQL Queries
Use This Variable... To Perform This Function

MSQL_TCP_PORT
(default 3000)

TCP port number used for remote TCP connections. The msqld daemon must be able to open this port to support remote connections from other hosts.

MSQL_HOME
(default $NSHOME/msql)

Base directory for msql files. The $MSQL_HOME directory contains the msql.acl file and the subdirectory msqldb, which contains the NSTREND_DB database.

MSQL_UNIX_PORT
(default $NSHOME/msql/msql.sock)

UNIX socket used for local connections between the msqld daemon and client processes.

**Table 32-1: Variables Required for Trend Reporter and SQL Queries**
Use This Variable...	To Perform This Function
MSQL_TCP_PORT (default 3000)	TCP port number used for remote TCP connections. The msqld daemon must be able to open this port to support remote connections from other hosts.
MSQL_HOME (default $NSHOME/msql)	Base directory for msql files. The $MSQL_HOME directory contains the msql.acl file and the subdirectory msqldb, which contains the NSTREND_DB database.
MSQL_UNIX_PORT (default $NSHOME/msql/msql.sock)	UNIX socket used for local connections between the msqld daemon and client processes.

Running msql o n UNIX Platforms

To generate ad hoc reports on UNIX platforms, you first must run the msql utility. After doing so, you can use standard SQL queries to specify certain reports.

To run the msql utility, follow these steps:

Step 1 Go to the command line.

Step 2 Enter the following command:

$NSHOME/bin/msql NSTREND_DB

If you are using the TrafficDirector application for logging on remote hosts, you can query the remote server daemon using the following command (where remote01 is the name of a remote host):

$NSHOME/bin/msql -h remote01 NSTREND_DB

Administering the msql Datab ase

When you need to perform maintenance on the database, you must first enter the following command:

$NSHOME/bin/msqladmin

After you have started the msql program, you can use any of the following commands that apply to your situation:

shutdown---Lets you order the server to shut itself down. Use this command only if you need to move the server to a different location, or if you need to perform maintenance on the actual server hardware.
reload---Lets you order the server to reload its access control information. You would use this command after modifying the msqld.acl file.
version---Lets you view various version information about the server.

Caution
Cisco Systems recommends that you use only the shutdown, reload, and version commands. Information is provided here about the additional commands, but it is strongly recommended that you do not create a new database on the server containing the Trend Reporter database, or that you delete the Trend Reporter database.

drop databasename---Lets you delete the specified database.
create databasename---Lets you specify a new database on the server. For example, to create a new database called NSTREND_DB, you would use the following command:

create NSTREND_DB

Generating Ad Ho c Reports

Depending on your platform (UNIX or Microsoft Windows), the tools you use to perform queries, to print, and to save your ad hoc reports are different. These tools are:

UNIX users must use the bundled SQL server
Windows users must use the Microsoft SQL Server administrative tools

Understanding Embedded SQL Commands

Note Data retrieved directly from the SQL database does not get normalized into uniform intervals. To extract and import normalized SQL data, use the dvreport command with the -format CSV option.

On UNIX platforms, SQL commands are always preceded by a backslash (\). Use the commands in the following list to process the SQL queries you construct, or to get help:

\e---Use this command to edit a query. When you do so, the vi text editor opens and you can edit a specific query. If you type this command and press Enter without specifying a query, the one displayed in vi is the last one you submitted to be processed. You can also set the VISUAL environment variable to open a different text editor you specify, if you prefer to use one other than vi.
\g---Use this command at the end of your query text and press Enter to submit the query to the database. If you type this command and press Enter without submitting a query, \g resubmits the last query that you sent to be processed.
\h---Use this command to display help information.
\p---Use this command to print the contents stored in the query buffer to your monitor.
\q---Use this command to exit from the msql program.

Using SQL Syntax

Before you use SQL queries with Trend Reporter, you must familiarize yourself with SQL syntax and usage.

The following sample query is one you might use to display intervals for the Ethernet SwitchProbe device, et105 where utilization exceeds 10%:

select Agent, Domain, StartTimeText, Utilization from segetdt_970410 where Agent = `et105' and Utilization > 10 order by Agent, Domain, StartTimeText\g

Note The example SQL syntax uses fields and values shown in the Segment Details table.

In the sample query, the following is applicable:

select---SQL command to find and extract one or more field names you specify.
Agent---Name of the database field that contains an agent name in the segment detail table for Ethernet.
Domain---A 15-character field containing a domain name in the segment detail table for Ethernet.
StartTimeText---Name of a database field that contains the Start Time in text format in the Segment Detail table for Ethernet.
Utilization---Name of the database field that contains the average segment utilization for a specified time interval in the segment detail table for Ethernet.
from---SQL syntax you use to point to the table that you want to query.
segetdt_yymmdd---Example of an Ethernet segment detail table name, where segetdt is the name of the table type, and yymmdd the two-digit year, month, and day that you specify.
where---SQL syntax you use to specify what values fields must match, if applicable.
Agent = ---Name of the field whose value must exactly match the value you specify after the equal sign (=). When using wildcards in a query, specify the command like instead of the equals sign.

Note

Using Wildcard Characters in msql Queries

`et105'---Value you want the Agent field to match. Note that any value that you specify, like an agent name, or StartTime, must be enclosed in single quotes.
and---Syntax you use to connect one criterion to another. Using this syntax means that the SQL query you're specifying contains at least two criteria that must match.
Utilization > ---Name of the field whose value must be greater than the value you specify after the greater than sign (>).
10---Value you specify that the Utilization field uses as a baseline; values included in the report must be greater than this baseline value.
order by---Syntax you use to specify how you want the report ordered. The first field you specify after this is the primary sort variable.
Agent---Primary sort variable for the report.
Domain---Next sort variable for the report.
StartTimeText---Last sort variable for the report.
\g---Command syntax for Go, the command that sends the query to the database.

Specifying Database Table Names

The different names for the Detail tables on UNIX and Microsoft Windows platforms are described in Table 32-2.

Table 32-2: Detail Tables---UNIX and Microsoft Windows Names
UNIX Table Name Microsoft Windows Table Name

protocoldt_yymmdd

protocol_detail

segetdt_yymmdd

seg_et_detail

segwandt_yymmdd

seg_wan_detail

segfddidt_yymmdd

seg_fddi_detail

segtrdt_yymmdd

seg_tr_detail

hostdt_yymmdd

host_detail

convdt_yymmdd

conv_detail

ippingdt_yymmdd

ipping_detail

artdt_yymmdd

art_detail

artsummdt_yymmdd

artsumm_detail

snmpgetdt_yymmdd

snmp_get_detail

nltopncdt_yymmdd

nltopnc_detail

altopncdt_yymmdd

altopnc_detail

**Table 32-2: Detail Tables---UNIX and Microsoft Windows Names**
UNIX Table Name	Microsoft Windows Table Name
protocoldt_`yymmdd`	protocol_detail
segetdt_`yymmdd`	seg_et_detail
segwandt_`yymmdd`	seg_wan_detail
segfddidt_`yymmdd`	seg_fddi_detail
segtrdt_`yymmdd`	seg_tr_detail
hostdt_`yymmdd`	host_detail
convdt_`yymmdd`	conv_detail
ippingdt_`yymmdd`	ipping_detail
artdt_`yymmdd`	art_detail
artsummdt_`yymmdd`	artsumm_detail
snmpgetdt_`yymmdd`	snmp_get_detail
nltopncdt_`yymmdd`	nltopnc_detail
altopncdt_`yymmdd`	altopnc_detail

In the UNIX table names, yymmdd is the two-digit year, month, and day that the table was generated. On UNIX platforms, new detail tables are created for each 24-hour interval.

Using Wildcard Characters in msql Queries

The msql program lets you use wildcards to specify agent or domain names in a query. You can use wildcards at the beginning, middle, or end of a name.

Note The wildcards you use to construct ad hoc queries are different and perform substitutions from wildcards you may be accustomed to using with either UNIX or Windows operating systems. If you use a UNIX- or Windows-supported character as a wildcard, msql will return an error message.

The valid wildcards you can use are:

Underscore---The underscore ( _ ) character matches any single character of any value. For example, if you want to query a specific agent but cannot remember if it is named Paris1 or ParisA, type Paris_ . msql returns information from any agent whose name begins with Paris and ends with an additional character of any value. If the agent you wanted was named Paris (and nothing more), using this wildcard would not return the values you wanted.
Percent---The percent (%) character matches 0 or more characters of any value, and, in most cases, is the best wildcard to use in queries. For example, if you want to query a specific agent but cannot remember if it is named RomeEng or RomeDev, type Rome%. msql returns information from any agent whose name begins with Rome and also contains 0 or more other characters. Because this wildcard matches 0 or more characters of any value, and the agent you want is named Rome (and nothing more), the use of this wildcard would return values for the agent named Rome.
Backslash---The backslash ( \ ) character escapes special characters. Special characters includes any printable, non-alphanumeric character available on your keyboard (such as @, !, $, and others). For example, if you have named an agent Rome@3, you would enter Rome\@3 in the Agent Name/Value field.

When you use wildcards, remember to use the command like instead of the equals sign (=).

The following example shows a query you might use to discover which agents are logging utilization at greater than 65%:

select Agent, Domain, StartTimeText, Utilization from segetdt_970410 where Agent like `et%' and 
Utilization > 65 order by Utilization, Agent, Domain, StartTimeText\g

Sample Queries

The following list shows sample queries that run certain reports. These examples let you know how you might structure and run your own ad hoc queries:

To show all agents and domains with entries in the protocol summary table, use this command:

select distinct Agent, Domain from protocol_summary order by Agent, Domain\g

To show intervals for WAN SwitchProbe devices in the New York area (if these devices are named with a convention where they all begin with `ny', the `%' symbol is used here as a wildcard) where utilization exceeded 60%, type the following command:

select Agent, Domain, StartTimeText, Utilization from segwandt_yymmdd where Agent like `ny%' and Utilization > 60 order by Agent, Domain, StartTimeText\g

Sample UNIX Detail Table Queries

Examples of detailed table queries (for UNIX platforms) are as follows:

select Agent, Domain, SrcAddress, DstAddress, StartTimeText, EndTimeText, EndTime, Pkts, Octets, Utilization from convdt_yymmdd order by Agent, Domain, EndTime, SrcAddress, DstAddress\g

select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from segfddidt_yymmdd order by Agent, Domain, StartTime\g

select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from segetdt_yymmdd order by Agent, Domain, StartTime\g

select Agent, Domain, Address, StartTimeText, EndTimeText, EndTime, InPkts, OutPkts from hostdt_yymmdd order by Agent, Domain, EndTime, Address\g
select Agent, Domain, Address, StartTimeText, EndTimeText, EndTime, InPkts, OutPkts from hostdt_yymmdd where Agent = 'et105' and 
Domain = 'RMON' and Address = '00-80-8c-01-04-52' and StartTimeText like 'Mar 9%' order by Agent, Domain, EndTime, Address\g

select Address, StartTimeText, EndTimeText, EndTime, InPkts, OutPkts, InUtilization, OutUtilization
from hostdt_yymmdd where Address = '7e-f1-cc-f0-8f-07' order by EndTime, Address\g

select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from protocoldt_yymmdd order by Agent, Domain, StartTime\g

select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from segtrdt_yymmdd order by Agent, Domain, StartTime\g

Sample Windows Detail Table Queries

Examples of detailed table queries (for Windows platforms) are as follows:

select Agent, Domain, SrcAddress, DstAddress, StartTimeText, EndTimeText, EndTime, Pkts, Octets, Utilization from conv_detail
where SrcAddress = '7e-f1-cc-f0-8f-07' or DstAddress = '7e-f1-cc-f0-8f-07' order by Agent, Domain, EndTime, SrcAddress, DstAddress\g

select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from seg_wan_detail order by Agent, Domain, StartTime\g

select Address, StartTimeText, EndTimeText, EndTime, InPkts, OutPkts, InUtilization, OutUtilization
from host_detail where Address = '7e-f1-cc-f0-8f-07' order by EndTime, Address\g
select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from protocol_detail order by Agent, Domain, StartTime\g

select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from seg_tr_detail order by Agent, Domain, StartTime\g

Other Sample Queries (UNIX and Windows)

Additional examples of detailed table queries (for both UNIX and Windows platforms) are as follows:

select Agent, Domain, SrcAddress, DstAddress, StartTimeText, EndTimeText, EndTime, Pkts, Octets, Utilization from conv_summary order by Agent, Domain, EndTime, SrcAddress, DstAddress\g
select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from seg_et_summary order by Agent, Domain, StartTime\g
select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from seg_fddi_summary order by Agent, Domain, StartTime\g
 
select Agent, Domain, Address, StartTimeText, EndTimeText, EndTime, InPkts, OutPkts from host_summary order by Agent, Domain, EndTime, Address\g

select DISTINCT Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, NetType, IfSpeed, StartTime 
from protocol_summary order by Agent, Domain, StartTime\g

select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from seg_tr_summary order by Agent, Domain, StartTime\g

select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from seg_wan_summary order by Agent, Domain, StartTime\g

Storing Queri es in Text Files for Later Report Generation

You can store queries in a text file and direct the output to a text file, as the following example shows (where query-high-util is the name of the query file, and high-util-list is the name of the resulting output text file):

msql NSTREND_DB < query-high-util > high-util-list

Note You can parse and process output from msql, if you provide your own tools.

Printing SQL Query Results

Use the following guidelines to print any report generated on an ad hoc basis using SQL queries. Depending on what you want to print, and where you want to print it, you can use the following examples:

Printing to a Management Console Display

To print a report on your management console screen, use the following syntax:

msql NSTREND_DB < textfilename

where textfilename is the text file you have created that contains one or more SQL queries. Doing so prints query output on the management console screen.

Printing to a File

To print the report to a file, use the following syntax:

msql NSTREND_DB < textfilename > filename

where textfilename is the text file you have created that contains one or more SQL queries. Doing so prints the output to the filename you specify.

Printing Directly to a Printer

To print the report directly to a printer, use the following syntax:

msql NSTREND_DB < textfilename | lpr

where textfilename is the text file you have created that contains one or more SQL queries. Doing so pipes the output ( | ) to the print spooler (lpr), which prints it to the default printer. To specify a different printer, you would also need to specify -Pprintername after the lpr command.

Sending the Report as an E-mail Message

To send the report as an e-mail message, use the following syntax:

msql NSTREND_DB < textfilename | mail emailaddress

where textfilename is the text file you have created that contains one or more SQL queries. Doing so sends the output, as an email, to the emailaddress you specify.

Table of Contents

Querying the Report Database