Ad Hoc Queries

You can make direct queries to database tables in TrafficDirector to view the statistical information you want in an "ad hoc" report format. An ad hoc report is an impromptu report that is created for specific variables you select, letting you choose what is reported and how it is sorted.You can even specify wild characters in some fields when you construct a query.

Understanding the SQL Database Tables

TrafficDirector uses a single database, named NSTREND_DB, that contains all TrafficDirector-related tables. You can access the tables in this database by specifying a table in your SQL query.

The database in TrafficDirector contains different tables that provide a wide range of useful statistical information. Probes collect the following statistics for reporting in TrafficDirector:

• Ethernet segment statistics

• Token Ring segment statistics

• FDDI segment statistics

• WAN segment statistics

• Protocol statistics

• Host statistics

• Conversation statistics

• Round-trip delay statistics

After you have chosen the type of statistics you want to see, you must select the level of report granularity you want reflected as the result of your query--either detail or daily-level. Statistic are stored in the following three kinds of storages:

Snapshot.	These tables contain raw snapshot data and are used as a resource to produce usable statistics for the detail and daily tables. The TrafficDirector snapshot daemon creates each new row of data in the snapshot table. The database extraction daemon then uses each available pair of rows in the snapshot table to create a corresponding row in the detail table. Once the database extraction daemon uses a pair of snapshot table rows, the older row is discarded, and the second row (the newer one) becomes the older row once new data is received and logged.
Detail.	These tables contain highly detailed data ranging from one minute to 24 hours, depending on how you define the logging poller parameters. Query this database when you want to generate history reports or summary reports that display data for 24 hour intervals.
Daily.	These tables contain utilization data that has been averaged for the day; for all other statistics, they contain delta values that represent counters of units between a specific start and end time. Ultimately you get one data point that represents that one day out of N number of days on a graph. Query this database when you want to generate history reports that display data at intervals of 24 hours or greater, or summary reports for any period greater than one day.

Database Table Quick Reference

Table 45-1 lists and describes the specific statistics that are logged in each of the tables in the database.

Table  45-1: Database Tables

Table	Contents	Number of Bytes per Row
protocol_snap	Media-independent protocol statistics snapshots	125
protocol_detail	Media-independent protocol statistics details	165
protocol_summary	Media-independent protocol daily statistics	165
seg_et_snap	Ethernet segment statistics snapshots	260
seg_et_detail	Ethernet segment statistics details	280
set_et_summary	Ethernet segment daily statistics	280
seg_wan_snap	WAN segment statistics snapshots	260
seg_wan_detail	WAN segment statistics details	280
seg_wan_summary	WAN segment daily statistics	280
seg_fddi_snap	FDDI segment statistics snapshots	260
seg_fddi_detail	FDDI segment statistics details	280
seg_fddi_summary	FDDI segment daily statistics	280
seg_tr_snap	Token Ring segment statistics snapshots	449
seg_tr_detail	Token Ring segment statistics details	469
seg_tr_summary	Token Ring segment daily statistics	469
host_snap	Host statistics snapshots	205
host_detail	Host statistics details	225
host_summary	Host statistics daily statistics	225
conv_snap	Conversation statistics snapshots	181
conv_detail	Conversation statistics details	201
conv_summary	Conversation daily statistics	201
ip_ping_snap	Round-trip delay statistics snapshots	125
ipping_detail	Round-trip delay statistics details	145
ip_ping_summary	Round-trip delay daily statistics	145

Running ISQL\w on Windows Platforms

If your platform is Windows 95 or Windows NT 3.5 or higher, you will use the administrative tools provided with Microsoft SQL Server 6.0 client and server software to perform SQL ad hoc queries to the NSTREND_DB. Use the ISQL\w tool provided with the SQL client software to 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.

Generating Ad Hoc Reports

Windows users must use the Microsoft SQL Server 6.0 or higher administrative tools to generate ad hoc reports.

Understanding Mini-SQL Commands

As a quick reference, use the following list to process the SQL queries you construct, or to get help:

\e	Edit command	Opens the vi text editor and edits a query. If you type this command and press Enter without specifying a query, the query shown in vi is the last one you submitted. You can also set the VISUAL environment variable to open a different text editor that you specify.
\g	Go command	Enter this command at the end of your query text and press Return to submit the query to the database. If you enter this command and press Enter without submitting a query, \g resubmits the last query that you sent.
\h	Help command	Displays Help information.
\p	Print command	Prints the contents stored in the query buffer to the workstation monitor.
\q	Quit command	Exits from the msql program.

Using SQL Syntax

Before you use SQL queries with Trend Reporter, you should be familiar with SQL syntax and usage. The following sample query shows intervals for the Ethernet probe et105 where utilization exceeded 10 percent:

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

select
 The SQL command to find and extract one or more field names you specify.
Agent
 The 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
 The name of a database field that contains the start time in text format in the segment detail table for Ethernet. 
Utilization
 The name of the database field that contains the average segment utilization for a specified time interval in the segment detail table for Ethernet.
from
 The SQL syntax you use to point to the table that you want to query.
segetdt_yymmdd
 An 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
 The SQL syntax you use to specify the values that fields must match, if applicable.
Agent = 
 The 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. (See "Using Wildcards in MSQL Queries" for more details and sample queries that use wildcards.) 
'et105'
 The value you want the Agent field to match. Note that any value you specify, for example, an agent name or StartTime, must be enclosed in single quotes. 
and
 The syntax you use to connect one criterion to another. Using this syntax means that the SQL query you are specifying contains at least two criteria that must match.
Utilization >
 The name of the field which value must be greater than the value you specify after the greater than sign (>).
10
 The value that the Utilization field uses as a baseline; values included in the report must be greater than this baseline value.
order by
 The syntax you use to specify how you want the report ordered. The first field you specify after this is the primary sort variable.
Agent
 The primary sort variable for the report.
Domain
 The next sort variable for the report.
StartTimeText
 The last sort variable for the report.
\g
 The command syntax for Go, the command to send the query to the database.

 Using Wildcards in MSQL Queries
 You use can wildcards with msql when specifying agent or domain names in a query. You can use wildcards at the beginning, middle or end of a name. Keep in mind that the wildcards you use to construct ad hoc queries are different and perform different substitutions from the wildcards you use with either UNIX or Windows operating systems. In fact, if you use a UNIX or Windows supported character as a wildcard, msql returns an error message. The following wildcards are valid with msql: 
 _ 
 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, enter Paris_ . The msql program returns information from any agent whose name begins with Paris and ends with an additional character of any value. If the agent you want is named Paris, this wildcard will not return the values you want. 
 %
 The percentage character (%) matches zero or more characters of any value and is usually 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, enter Rome%. The msql program returns information from any agent whose name begins with Rome and also contains zero or more other characters. Because this wildcard matches zero or more characters of any value, if you have forgotten that the agent you want is named Rome, this wildcard will return values for the agent named Rome. 
 \ 
 The backslash character (\ ) escapes special characters. Special characters include any printable nonalphanumeric character available on your keyboard (for example @, !, and $). For example, if you name an agent Rome@3, enter Rome\@3 in the Agent Name/Value field.
 When you use wildcards, you must use the like command instead of the equals symbol (=). The following is an example of a query you can use to discover which agents are logging utilization at greater than 65 percent:
select Agent, Domain, StartTimeText, Utilization from segetdt_970410 where Agent like 'et%' and Utilization > 65 order by Utilization, Agent, Domain, StartTimeText\g


 Sample Queries
 This section lists sample queries that run certain reports to give you an idea of how you might structure and run your own ad hoc queries.

To show all agents and domains with entries in the protocol summary table, enter the following:


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


To show intervals for WAN probes in the New York area (given that all probe names begin with 'ny'; the % symbol is used as a wildcard) where utilization exceeded 60%, enter the following:


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


Detail tables are as follows:



protocol_detail

seg_et_detail

seg_wan_detail

seg_fddi_detail

seg_fddi_detail

seg_fddi_detail

host_detail

conv_detail

ipping_detail



 For more information on details and other database tables, see Appendix B, "Report Resource Information." 
 The following are some sample Windows detail table queries:

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


 The following are some sample queries for UNIX and Windows:

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, TargetSnapTimeText, TargetSnapTime from host_snap order by Agent, Domain, TargetSnapTime\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 Queries in Text Files
 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 Ad Hoc Reports
 This section shows you how to print any report generated on an ad hoc basis using SQL queries. 

 Printing to a Management Console Display 
 To print a report to display on your management console screen, use the following syntax:
msql NSTREND_DB textfilename

 textfilename is the text file you created that contains one or more SQL queries. 

 Printing to a File 
 To print a report to a file use the following syntax:
msql NSTREND_DB textfilename filename

 textfilename is the text file you created that contains one or more SQL queries. 

 Printing Directly to a Printer 
 To print the report directly to a printer use the following syntax:
msql NSTREND_DB textfilename | lpr

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

 Sending the Report Using E-mail 
 To send the report as an e-mail to a user in your organization, use the following syntax:
msql NSTREND_DB textfilename | mail emailaddress

 textfilename is the text file you created that contains one or more SQL queries. This sends the output as an email to the emailaddress you specify.