|
|
The "Logging and Reporting Using Trend Reporter" chapter discussed the new TrafficDirector Trend Reporter application and explained how to work with the GUI to configure aging and logging parameters and generate the basic Cisco Systems-supplied reports. However, you may want to customize Trend Reporter to create reports that are more meaningful to your organization.
In this chapter, you will find more about the technical details, some tips to help you customize the application to fit your needs, and information about using SQL queries to create ad hoc reports.
Trend Reporter is based on a relational database, which means that you can make ad hoc queries to information contained in any of the database tables, set up automatic report generation, choose reports based on detail or summary data, and define how long detail or summary information stays in the database. For UNIX platforms, Trend Reporter includes a bundled Structured Language Query (SQL) server.
This manual is organized into two separate Trend Reporter chapters. The "Logging and Reporting Using Trend Reporter" chapter gave a brief overview of Trend Reporter, information about setting environment variables for UNIX platforms, and procedures for configuring aging and logging parameters. Also in that chapter are procedures for using the GUI to generate reports, save and load report configurations, and print reports shown on the display.
In this chapter, you will find an in-depth discussion of the Trend Reporter daemons, details about the different tables, information about the table schemata, information about setting up environment variables, administering the database, and using SQL queries to generate customized, ad hoc reports. The following list is a handy reference you can use to quickly find the topics you want to read about in the "Logging and Reporting Using Trend Reporter" chapter.
| What is Trend Reporter? | See the "How Trend Reporter Works" section in the "Logging and Reporting Using Trend Reporter" chapter. |
| How does V3.3 compare to V4.1? | See the "Comparing V4.1 and V3.3 Trend Reporting Features" section in the "Logging and Reporting Using Trend Reporter" chapter. |
| How is the database structured? | See the "Setting Up Environment Variables" section in the "Logging and Reporting Using Trend Reporter" chapter or the "Setting Up Environment Variables" section in this chapter. |
| What are Trend Reporter daemons? | See the "Understanding the Trend Reporter Daemons" section. |
| What is SQL query? | See the "Using SQL Queries" section. |
Trend Reporter lets you structure reports that are meaningful for your needs. The information in this chapter explains how Trend Reporter works and helps you quickly get up to speed on creating what you need. Depending on your needs and interests, you may need to read this entire chapter; you might need to read only certain sections that apply to the task at hand.
Trend Reporter is built upon a relational database, instead of flat files. Within the database are numerous tables containing different information. To access information in the database tables, you use SQL, whether interactively (entered on a command line) or submitted through the Trend Reporter GUI.
If you opt to use the Trend Reporter ad hoc query capability, you will need to be familiar with SQL. SQL queries are easy to use and understand; an example SQL query to an employee database table might look something like the following:
SELECT LastName, FirstName, Extension FROM employee_table WHERE FirstName = 'David' ORDER by LastName
On UNIX platforms, Trend Reporter includes a bundled SQL server. The SQL server must run locally on the host that is also running TrafficDirector.
The database that is located in the SQL server is a collection of tables that you access using SQL queries. Report tables are available to you and display information that you request through the GUI or ad hoc queries.
As mentioned in the "Logging and Reporting Using Trend Reporter" chapter, the Trend Reporter tables contain statistics for all agent/domain pairs. This means that SQL queries you submit can span multiple agents and domains. Trend Reporter uses the following seven types of report tables:
Each report table type can be classified as one or more of the following categories:
| Protocol | Contains media-independent domain (protocol) statistics common to all LAN and WAN types. Tables include packets and bytes only. |
| Segment | Contains media-specific segment statistics for Ethernet and Fast Ethernet, Token Ring, FDDI, and WAN. If you are using RMON2 probes, this table is useful for the RMON domain. |
| Host | Contains basic host statistics. |
| Conversations | Contains basic conversations statistics. |
When you want to look at any of the seven table types, you can choose the specific type of content that you want. This means that you can look at any of the tables described above for the following types of content:
| Detail | Shows a high level (usually hourly) of detail for the table you request as a report. |
| Summary | Shows a less detailed daily level for the table you request as a report. |
| Snapshot | Contains temporary information, but report applications do not reference it; only daemons see and work with information in this table. |
Trend Reporter uses detail and summary tables to extract the information required to prepare various reports you might request.
Trend Reporter gives you the flexibility to choose predefined reports through the GUI or to make ad hoc queries to the tables in the database. Other benefits of Trend Reporter include:
| Detail and summary data formats | As mentioned earlier, you can choose to view any of the Trend Reporter seven table types as either detail or summary. This means for any of the seven table types, you can choose the type of content that reflects your current needs, whether it is highly detailed or summary-level detail information. |
| Automatic data aging | This feature lets you specify what type of data you want to save in the database for a defined period of time. For example, you might want hourly detail information to stay in the database for 7 days, but you would need daily summary information to stay in the database for a month. Once you specify the amount of time you want to save information, Trend Reporter automatically deletes the aged information, as defined. |
| Automatic report generation | Using the Trend Reporter GUI, you can specify that a certain report must be generated on any combination of daily, weekly, and monthly intervals. Once you do so, Trend Reporter schedules the report to be run at all the intervals you specified. Trend Reporter does this automatic report generation by using an "autoreporter daemon" that you will read more about later in this chapter. |
| Minimum threshold specification | HostThreshold. This feature lets you specify a minimum utilization percentage for either a host or a source-to-destination that a host segment must meet before it is included in the host details or summary tables (host utilization) or conversation details or summary tables (source-to-destination). This means that the extraction daemon compares a host segment's utilization percentage or source-to-destination utilization percentage to the minimum numbers you specify and creates a row in the host details table only if the utilization percentage meets or exceeds this value. This feature lets you specify what utilization is meaningful to your organization that you need included in reports. |
In Trend Reporter, you can generate reports two ways: interactively, through the GUI, or on an ad hoc basis, using SQL queries. Depending on your needs, you might need to run the predefined reports, create your own ad hoc reports, or choose to mix some predefined reports with ad hoc reports you customize.
Depending on the report you select, you can usually choose from the following report formats (except for Billing, which is available only in tabular format):
A single database named NSTREND_DB contains all TrafficDirector-related tables. There are many tables in this database that Trend Reporter accesses in response to choices you make on the GUI or to ad hoc SQL queries you enter. Other TrafficDirector applications may also access some of the tables in the database.
Within the database, you will find sets of tables and types of tables. There are seven types of tables (described in the "More About the Trend Reporter Database Tables" section in the "Logging and Reporting Using Trend Reporter" chapter); for each type of table, three storage tables exist. The three storage tables and how they work are explained as follows:
For quick reference, Table 14-1 shows a list of all the tables in the database and gives a brief description of each, as well as maximum row size.
| Table | Description | Maximum Row Size |
|---|---|---|
| protocol_snap | Media-independent protocol statistics snapshots | 125 |
| protocol_detail | Media-independent protocol statistics details | 165 |
| protocol_summary | Media-independent protocol statistics summary | 165 |
| seg_et_snap | Ethernet segment statistics snapshots | 260 |
| seg_et_detail | Ethernet segment statistics details | 280 |
| set_et_summary | Ethernet segment statistics summary | 280 |
| seg_wan_snap | WAN segment statistics snapshots | 260 |
| seg_wan_detail | WAN segment statistics details | 280 |
| seg_wan_summary | WAN segment statistics summary | 280 |
| seg_fddi_snap | FDDI segment statistics snapshots | 260 |
| seg_fddi_detail | FDDI segment statistics details | 280 |
| seg_fddi_summary | FDDI segment statistics summary | 280 |
| seg_tr_snap | Token-Ring segment statistics snapshot | 449 |
| seg_tr_detail | Token-Ring segment statistics details | 469 |
| seg_tr_summary | Token-Ring segment statistics summary | 469 |
| host_snap | Host statistics snapshots | 205 |
| host_detail | Host statistics details | 225 |
| host_summary | Host statistics summary | 225 |
| conv_snap | Conversation statistics snapshots | 181 |
| conv_detail | Conversation statistics details | 201 |
| conv_summary | Conversation statistics summary | 201 |
As you might know, a daemon is a UNIX process that runs in the background and is disconnected from a process group and terminal. As used in Trend Reporter, certain daemons are used to update functions. Daemons work with related configuration files that rule how and when they are called to perform their roles. Trend Reporter uses the following daemons:
| Snapshot daemon | Called dbsnapd, this daemon gets snapshots of raw statistics from agents and stores this information in a snapshot table. |
| Extraction daemon | Called dbextrad, this daemon gets information from the snapshot table, creates new rows in the details table, and deletes the oldest member in each pair of snapshot rows. |
| Rollup and Aging daemon | Called dbrolld, this daemon performs two separate but related actions; it gets information from the details table to create new rows in the summary table and deletes obsolete information from both the detail and summary tables. |
| Server daemon | Called msqld, this daemon is bundled with the SQL server and listens and processes SQL queries (direct or those from the GUI). |
The Snapshot daemon, dbsnapd, creates logs based on specifications in the $NSHOME/usr/dbsnap.cfg configuration file. The Snapshot daemon now creates rows in snapshot tables, rather than individual logfiles.
The ASCII configuration file, dbsnap.cfg, contains information that controls how the Snapshot daemon works. In this file are the agent/domain combinations you want to log, as well as snapshot intervals that the daemon uses to gather raw data.
The smallest selectable interval is 1 minute (although you can specify a different interval, depending on your needs). For example, you would probably want to use the small 1-minute interval when you want to log protocol and segment statistics.
The Snapshot daemon reads the dbsnap.cfg file at the start of every defined interval, so that any changes to the file are effective for the next logging interval. For example, if you specify 04:00 as a logging interval, Trend Reporter takes snapshots at 4 a.m., 8 a.m., 12 noon, and so on. You can specify the following logging intervals in the dbsnap.cfg file:
Each line in the dbsnap.cfg file contains an agent name, domain name, and interval to log for protocol, segment, host, and conversation information. The following example shows the contents of a dbsnap.cfg file:
#Agent Domain Protocol Segment Host Conversation wan105 RMON 00:05 00:05 00:30 24:00 wan105 IP 00:05 - 02:00 24:00 et132 RMON 00:05 01:00 02:00 08:00 et132 IP 00:05 - 02:00 04:00
Using the example above, for agent et132 and domain IP, Trend Reporter would log conversation statistics starting at midnight, and again at 4:00 a.m., 8:00 a.m., 12:00 noon, 4:00 p.m., and 8:00 p.m. This means that every four hours (04:00), Trend Reporter takes a snapshot of the conversation statistics for agent et132.
The Extraction daemon, dbextrad, follows the Snapshot daemon and creates entries in the detail tables derived from the snapshot pairs in snapshot tables. Whenever the Extraction daemon uses a pair of rows in the snapshot table, it also deletes the older row because that snapshot information is no longer needed.
The Extraction daemon ignores snapshots taken more than 24 hours apart. After the Extraction daemon goes through a snapshot table, it deletes snapshots older than 48 hours; this is especially useful to delete snapshots for agent/domain combinations that no longer have logging enabled.
The ASCII configuration file, dbupdate.cfg, contains information that controls how the Extraction and Rollup and Aging daemons work. In this file are parameters you can customize as necessary. You cannot delete any of the tables in the database (they are permanent), but you can specify that old rows of information are deleted at specific intervals.
In the dbupdate.cfg file, you will find one line of information and parameters (shown in number of days) for each type of table (protocol, segment, host, and conversation). Also in the file are minimum utilization thresholds for the host and conversation tables only. The following example shows the contents of a dbupdate.cfg file.
# Detail Aging Summary Aging Protocol: 31 366 Segment: 31 366 Host: 7 31 Conversation: 7 31 # #Minimum utilization percentage for inclusion in host #and conversation detail/summary tables HostThreshold: 0.010 ConversationThreshold 0.005
In this configuration file, there are only four parameters you can modify, as explained below:
| Detail Aging | This interval shows the number of days that information can age before the Rollup and Aging daemon deletes it from the details table. Using the example above, for a host details table, information is allowed to age for 7 days, and then it is deleted from that table. You must specify a value greater than 1. |
| Summary Aging | This interval shows the number of days that information can age before the Rollup and Aging daemon deletes it from the summary table. Using the example above, for a protocol summary table, information is allowed to age for 366 days and then it is deleted from that table. You must specify a value greater than 1. |
| HostThreshold | This number shows the minimum utilization percentage that a host inbound and outbound must meet before it is included in the host details or summary tables. This means that the Extraction daemon compares a host segment's utilization percentage to this number and creates a row in the host details table only if the utilization percentage meets or exceeds this value. This is a useful feature that lets you save disk space by saving only certain information that meets the percentage you select. |
| ConversationThreshold | This number shows the minimum source-to-destination utilization percentage that a conversation must meet before it is included in the conversation details or summary tables. This means that the Extraction daemon compares a conversation source-to-destination utilization percentage to this number and creates a row in the conversation details table only if the utilization percentage meets or exceeds this value. This is a useful feature that, as part of the Trend Reporter GUI, lets you save disk space by saving only certain information that meets the percentage you select. |
The Rollup and Aging daemon, dbrolld, wakes up daily at 1:45 a.m. (the default setting). Once it is awake, it uses information in the details tables to create entries in the corresponding summary tables for the previous day. For example, if you have defined hourly protocol logging for the agent named et132 for domain TCP, the Rollup and Aging daemon looks at all 24 rows in the protocol detail table, summarizes the information into one row, and puts this summary row in the protocol summary table. The daemon does not delete the 24 rows in the protocol detail table at this point.
Once the Rollup and Aging daemon has looked through all the details tables and extracted the information to put into the summary tables, it performs an "aging pass." This aging pass is when the daemon goes through both details and summary tables looking for old information that has aged past the limits set in the dbupdate.cfg file. When the Rollup and Aging daemon finds information that is aged past the limits, it deletes it.
The daemon server, msqld, is really a server process that acts like but is not technically a daemon (although for ease of use, this book uses that term). The Server daemon is bundled with TrafficDirector on Motif platforms. When you start TrafficDirector, this wakes up the Snapshot daemon, which then wakes up the Server daemon.
The server daemon's job is basically to listen for SQL or database administration queries by using either a TCP/IP or a UNIX socket (although currently, connections must be local). The other action-oriented daemons (Snapshot, Extraction, and Rollup and Aging) are now able to connect to the database through the socket that the Server daemon uses.
Keep in mind that, as mentioned earlier, the Server daemon is really a process, and there are no configuration or log files you need to monitor or use, as you might for other daemons.
All the Trend Reporter true daemons (Snapshot, Extraction, and Rollup and Aging) record their activities by putting this information into log files. Control files are special files that contain code for the "keep alive" mechanism that helps TrafficDirector ensure that exactly one copy of each daemon is running at a given time.
You should occasionally check the daemon logfiles to ensure that the daemons are functioning correctly. To do so, you can use the tail-f UNIX command. Table 14-2 lists the logfiles you will want to monitor.
| Daemon | Logfile Created | Control File |
|---|---|---|
| dbsnapd | dbsnap.log | dbsnap.ctl |
| dbextrad | dbextra.log | dbextra.ctl |
| dbrolld | dbroll.log | - |
The daemon server, msqld, and the tools that access this daemon all use specific environment variables. These environment variables are set by using the same .traffdir script file (file that the install script creates during installation) that is also used to set other environment variables, such as NSHOME, HHHOME, and others. Table 14-3 gives the variables that must be set for Trend Reporter to work correctly.
| Variable | Default | Description |
|---|---|---|
| MSQL_TCP_PORT | 3000 | TCP port number used for remote TCP connections. Even though remote connections are not yet supported in Trend Reporter, the msqld daemon must be able to open this port. If you have installed multiple copies of TrafficDirector on one host, each must have a different value specified for this variable. |
| MSQL_HOME | $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 | $NSHOME/msql/msql.sock | UNIX socket used for local connections between the msqld daemon and client processes. |
In most cases, you will rarely need to edit the .traffdir file or any of the environment variables contained within it. However, if you need to relocate the $NSHOME directory (for example, if you are moving the software to a different system), you will need to edit the .traffdir file to indicate the new location. You must do so to ensure that TrafficDirector operates correctly.
As a quick reference, this section includes all the table schemata used in Trend Reporter. Tables in this section include:
| Field | Type | Description |
|---|---|---|
| Agent | char(26) | Agent name. |
| Domain | char(15) | Domain name. |
| TargetSnapTime | int | Target snapshot time. The actual snapshot may be taken after the target time. |
| TargetSnapTimeText | char(15) | Target snapshot time (text format). |
| ActualSnapTime | int | Actual snapshot time. |
| InstallTime | int | RMON table installation time (or 0 if unavailable). Used to detect domain reinstallation. |
| IfSpeed | int | Agent interface speed in bits per second. |
| NetType | char(4) | Agent network type: ET, TR, FDDI, or WAN. |
| SysUpTime | int | Agent SysUpTime at time of snapshot; used to detect domain reinstallation. |
| DomainId | int | Domain index. Used to detect domain reinstallation. |
| Filler1 | int | Reserved for future use. |
| Octets | real | Value of octets counter at snapshot time. |
| Pkts | real | Value of packets counter at snapshot time. |
| Filler2 | real | Reserved for future use. |
| Filler3 | real | Reserved for future use. |
| Field | Type | Description |
|---|---|---|
| Agent | char(26) | Agent name. |
| Domain | char(15) | Domain name. |
| TargetSnapTime | int | Target snapshot time. The actual snapshot may be taken after the target time. |
| TargetSnapTimeText | char(15) | Target snapshot time (text format). |
| ActualSnapTime | int | Actual snapshot time. |
| InstallTime | int | RMON table installation time (or 0 if unavailable). Used to detect domain reinstallation. |
| IfSpeed | int | Agent interface speed in bits per second. |
| NetType | char(4) | Agent network type: ET, TR, FDDI, or WAN. |
| SysUpTime | int | Agent SysUpTime at time of snapshot; used to detect domain reinstallation. |
| DomainId | int | Domain index. Used to detect domain reinstallation. |
| Filler1 | int | Reserved for future use. |
| DropEvents | real | Drop events. This and remaining fields are absolute values of corresponding RMON counters at the time of the snapshot. |
| Octets | real | |
| Pkts | real | |
| BroadcastPkts | real | |
| MulticastPkts | real | |
| CRCErrors | real | |
| UndersizePkts | real | |
| OversizePkts | real | |
| Fragments | real | |
| Jabbers | real | |
| Collisions | real | |
| Pkts64 | real | |
| Pkts65to127 | real | |
| Pkts128to255 | real | |
| Pkts256to511 | real | |
| Pkts512to1023 | real | |
| Pkts1024to1518 | real | |
| Filler2 | real | Reserved for future use. |
| Filler3 | real | Reserved for future use. |
| Field | Type | Description |
|---|---|---|
| Agent | char(26) | Agent name. |
| Domain | char(15) | Domain name. |
| TargetSnapTime | int | Target snapshot time. The actual snapshot may be taken after the target time. |
| TargetSnapTimeText | char(15) | Target snapshot time (text format). |
| ActualSnapTime | int | Actual snapshot time. |
| InstallTime | int | RMON table installation time (or 0 if unavailable). Used to detect domain reinstallation. |
| IfSpeed | int | Agent interface speed in bits per second. |
| NetType | char(4) | Agent network type: ET, TR, FDDI, or WAN. |
| SysUpTime | int | Agent SysUpTime at time of snapshot; used to detect domain reinstallation. |
| DomainId | int | Domain index. Used to detect domain reinstallation. |
| Filler1 | int | Reserved for future use. |
| DropEvents | real | Drop events. This and remaining fields are absolute values of corresponding RMON counters at the time of the snapshot. |
| Octets | real | |
| Pkts | real | |
| BroadcastPkts | real | |
| MulticastPkts | real | |
| CRCErrors | real | |
| Discards | real | |
| Aborts | real | |
| Congestions | real | |
| PktsLessThan64 | real | |
| Pkts64 | real | |
| Pkts65to127 | real | |
| Pkts128to255 | real | |
| Pkts256to511 | real | |
| Pkts512to1023 | real | |
| Pkts1024to1518 | real | |
| PktsGreaterThan1518 | real | |
| Filler2 | real | Reserved for future use. |
| Filler3 | real | Reserved for future use. |
| Field | Type | Description |
|---|---|---|
| Agent | char(26) | Agent name. |
| Domain | char(15) | Domain name. |
| TargetSnapTime | int | Target snapshot time. The actual snapshot may be taken after the target time. |
| TargetSnapTimeText | char(15) | Target snapshot time (text format). |
| ActualSnapTime | int | Actual snapshot time. |
| InstallTime | int | RMON table installation time (or 0 if unavailable). Used to detect domain reinstallation. |
| IfSpeed | int | Agent interface speed in bits per second. |
| NetType | char(4) | Agent network type: ET, TR, FDDI, or WAN. |
| SysUpTime | int | Agent SysUpTime at time of snapshot; used to detect domain reinstallation. |
| DomainId | int | Domain index. Used to detect domain reinstallation. |
| Filler1 | int | Reserved for future use. |
| DropEvents | real | Drop events. This and remaining fields are absolute values of corresponding RMON counters at the time of the snapshot. |
| Octets | real | |
| Pkts | real | |
| BroadcastPkts | real | |
| MulticastPkts | real | |
| CRCAlignErrors | real | |
| PktsLessThan64 | real | |
| PktsGreaterThan1518 | real | |
| SmtFrames | real | |
| AverageTRT | real | |
| Tokens | real | |
| Pkts64 | real | |
| Pkts65to127 | real | |
| Pkts128to255 | real | |
| Pkts256to511 | real | |
| Pkts512to1023 | real | |
| Pkts1024to1518 | real | |
| Filler2 | real | Reserved for future use. |
| Filler3 | real | Reserved for future use. |
| Field | Type | Description |
|---|---|---|
| Agent | char(26) | Agent name. |
| Domain | char(15) | Domain name. |
| TargetSnapTime | int | Target snapshot time. The actual snapshot may be taken after the target time. |
| TargetSnapTimeText | char(15) | Target snapshot time (text format). |
| ActualSnapTime | int | Actual snapshot time. |
| InstallTime | int | RMON table installation time (or 0 if unavailable). Used to detect domain reinstallation. |
| IfSpeed | int | Agent interface speed in bits per second. |
| NetType | char(4) | Agent network type: ET, TR, FDDI, or WAN. |
| SysUpTime | int | Agent SysUpTime at time of snapshot; used to detect domain reinstallation. |
| DomainId | int | Domain index. Used to detect domain reinstallation. |
| Filler1 | int | Reserved for future use. |
| MacDropEvents | real | (MAC-layer statistics) |
| MacOctets | real | |
| MacPkts | real | |
| PurgeEvents | real | |
| PurgePkts | real | |
| BeaconEvents | real | |
| BeaconTime | real | |
| BeaconPkts | real | |
| ClaimTokenEvents | real | |
| ClaimTokenPkts | real | |
| NAUNChanges | real | |
| LineErrors | real | |
| InternalErrors | real | |
| BurstErrors | real | |
| ACErrors | real | |
| AbortErrors | real | |
| LostFrameErrors | real | |
| CongestionErrors | real | |
| FrameCopiedErrors | real | |
| FrequencyErrors | real | |
| TokenErrors | real | |
| SoftErrorReports | real | |
| RingPollEvents | real | |
| DropEvents | real | (promiscuous statistics) |
| Octets | real | |
| Pkts | real | |
| DataBroadcastPkts | real | |
| DataMulticastPkts | real | |
| Pkts18to63 | real | |
| Pkts64to127 | real | |
| Pkts128to255 | real | |
| Pkts256to511 | real | |
| Pkts512to1023 | real | |
| Pkts1024to2047 | real | |
| Pkts2048to4095 | real | |
| Pkts4096to8191 | real | |
| Pkts8192to1800 | real | |
| PktsGreaterThan18K | real | |
| Filler2 | real | |
| Filler3 | real |
| Field | Type | Description |
|---|---|---|
| Agent | char(26) | Agent name. |
| Domain | char(15) | Domain name. |
| TargetSnapTime | int | Target snapshot time. The actual snapshot may be taken after the target time. |
| TargetSnapTimeText | char(15) | Target snapshot time (text format). |
| ActualSnapTime | int | Actual snapshot time. |
| InstallTime | int | RMON table installation time (or 0 if unavailable). Used to detect domain reinstallation. |
| IfSpeed | int | Agent interface speed in bits per second. |
| NetType | char(4) | Agent network type: ET, TR, FDDI, or WAN. |
| SysUpTime | int | Agent SysUpTime at time of snapshot; used to detect domain reinstallation. |
| DomainId | int | Domain index. Used to detect domain reinstallation. |
| Filler1 | int | Reserved for future use. |
| AddressType | char(4) | Host address type. |
| Address | char(20) | Host address, hexideximal-ASCII format. |
| InPkts | real | Packets in. This and remaining fields are absolute values of corresponding RMON counters at the time of the snapshot. |
| OutPkts | real | Packets out. |
| InOctets | real | Octets in. |
| OutOctets | real | Octets out. |
| OutErrors | real | Errors out. |
| OutBroadcastPkts | real | Broadcast packets out. |
| OutMulticastPkts | real | Multicast packets out. |
| OutNonUcastPkts | real | Nonunicast packets out. |
| Filler2 | real | Reserved for future use. |
| Filler3 | real | Reserved for future use. |
| Field | Type | Description |
|---|---|---|
| Agent | char(26) | Agent name. |
| Domain | char(15) | Domain name. |
| TargetSnapTime | int | Target snapshot time. The actual snapshot may be taken after the target time. |
| TargetSnapTimeText | char(15) | Target snapshot time (text format). |
| ActualSnapTime | int | Actual snapshot time. |
| InstallTime | int | RMON table installation time (or 0 if unavailable). Used to detect domain reinstallation. |
| IfSpeed | int | Agent interface speed in bits per second. |
| NetType | char(4) | Agent network type: ET, TR, FDDI, or WAN. |
| SysUpTime | int | Agent SysUpTime at time of snapshot; used to detect domain reinstallation. |
| DomainId | int | Domain index. Used to detect domain reinstallation. |
| Filler1 | int | Reserved for future use. |
| AddressType | char(4) | Host address type. |
| SrcAddress | char(20) | Source host address, hexidecimal-ASCII format. |
| DstAddress | char(20) | Destination host address, hexidecimal-ASCII format. |
| Pkts | real | Packets source host to destination host. This and remaining fields are absolute values of corresponding RMON counters at the time of the snapshot. |
| Octets | real | Octets source host to destination host. |
| Errors | real | Errors source host to destination host. |
| Filler2 | real | Reserved for future use. |
| Filler3 | real | Reserved for future use. |
| Field | Type | Description |
|---|---|---|
| Agent | char(26) | Agent name. |
| Domain | char(15) | Domain name. |
| StartTime | int | Start time. |
| EndTime | int | End time. |
| StartTimeText | char(15) | Start time (text format). |
| EndTimeText | char(15) | End time (text format). |
| Duration | int | Actual duration of interval in seconds. |
| IfSpeed | int | Agent interface speed in bits per second. |
| NetType | char(4) | Agent network type: ET, TR, FDDI, or WAN. |
| Reinstalled | int | Reinstalled flag, nonzero if reinstalled. |
| Filler1 | int | Reserved for future use. |
| Utilization | real | Average segment utilization for the time interval. |
| Octets | real | |
| Pkts | real | |
| Filler2 | real | Reserved for future use. |
| Filler3 | real | Reserved for future use. |
| Field | Type | Description |
|---|---|---|
| Agent | char(26) | Agent name. |
| Domain | char(15) | Domain name. |
| StartTime | int | Start time. |
| EndTime | int | End time. |
| StartTimeText | char(15) | Start time (text format). |
| EndTimeText | char(15) | End time (text format). |
| Duration | int | Actual duration of interval in seconds. |
| IfSpeed | int | Agent interface speed in bits per second. |
| NetType | char(4) | Agent network type: ET, TR, FDDI, or WAN. |
| Reinstalled | int | Reinstalled flag, nonzero if reinstalled. |
| Filler1 | int | Reserved for future use. |
| Utilization | real | Average segment utilization for the time interval. |
| DropEvents | real | This and remaining fields are the "delta" values for corresponding RMON counters during the interval. |
| Octets | real | |
| Pkts | real | |
| BroadcastPkts | real | |
| MulticastPkts | real | |
| CRCAlignErrors | real | |
| UndersizePkts | real | |
| OversizePkts | real | |
| Fragments | real | |
| Jabbers | real | |
| Collisions | real | |
| Pkts64 | real | |
| Pkts65to127 | real | |
| Pkts128to255 | real | |
| Pkts256to511 | real | |
| Pkts512to1023 | real | |
| Pkts1024to1518 | real | |
| Filler2 | real | Reserved for future use. |
| Filler3 | real | Reserved for future use. |
| Field | Type | Description |
|---|---|---|
| Agent | char(26) | Agent name. |
| Domain | char(15) | Domain name. |
| StartTime | int | Start time. |
| EndTime | int | End time. |
| StartTimeText | char(15) | Start time (text format). |
| EndTimeText | char(15) | End time (text format). |
| Duration | int | Actual duration of interval in seconds. |
| IfSpeed | int | Agent interface speed in bits per second. |
| NetType | char(4) | Agent network type: ET, TR, FDDI, or WAN. |
| Reinstalled | int | Reinstalled flag, nonzero if reinstalled. |
| Filler1 | int | Reserved for future use. |
| Utilization | real | Average segment utilization for the time interval. |
| DropEvents | real | This and remaining fields are the "delta" values for corresponding RMON counters during the interval. |
| Octets | real | |
| Pkts | real | |
| BroadcastPkts | real | |
| MulticastPkts | real | |
| CRCErrors | real | |
| Discards | real | |
| Aborts | real | |
| Congestions | real | |
| PktsLessThan64 | real | |
| Pkts64 | real | |
| Pkts65to127 | real | |
| Pkts128to255 | real | |
| Pkts256to511 | real | |
| Pkts512to1023 | real | |
| Pkts1024to1518 | real | |
| PktsGreaterThan1518 | real | |
| Filler2 | real | Reserved for future use. |
| Filler3 | real | Reserved for future use. |
| Field | Type | Description |
|---|---|---|
| Agent | char(26) | Agent name. |
| Domain | char(15) | Domain name. |
| StartTime | int | Start time. |
| EndTime | int | End time. |
| StartTimeText | char(15) | Start time (text format). |
| EndTimeText | char(15) | End time (text format). |
| Duration | int | Actual duration of interval in seconds. |
| IfSpeed | int | Agent interface speed in bits per second. |
| NetType | char(4) | Agent network type: ET, TR, FDDI, or WAN. |
| Reinstalled | int | Reinstalled flag, nonzero if reinstalled. |
| Filler1 | int | Reserved for future use. |
| Utilization | real | Average segment utilization for the time interval. |
| DropEvents | real | This and remaining fields are the "delta" values for corresponding RMON counters during the interval. |
| Octets | real | |
| Pkts | real | |
| BroadcastPkts | real | |
| MulticastPkts | real | |
| CRCErrors | real | |
| SMTPkts | real | |
| AverageTRT | real | |
| TokenPkts | real | |
| PktsLessThan64 | real | |
| Pkts64Octets | real | |
| Pkts65to127 | real | |
| Pkts128to255 | real | |
| Pkts256to511 | real | |
| Pkts512to1023 | real | |
| Pkts1024to1518Octets | real | |
| PktsGreaterThan1518 | real | |
| Filler2 | real | Reserved for future use. |
| Filler3 | real | Reserved for future use. |
| Field | Type | Description |
|---|---|---|
| Agent | char(26) | Agent name. |
| Domain | char(15) | Domain name. |
| StartTime | int | Start time. |
| EndTime | int | End time. |
| StartTimeText | char(15) | Start time (text format). |
| EndTimeText | char(15) | End time (text format). |
| Duration | int | Actual duration of interval in seconds. |
| IfSpeed | int | Agent interface speed in bits per second. |
| NetType | char(4) | Agent network type: ET, TR, FDDI, or WAN. |
| Reinstalled | int | Reinstalled flag, nonzero if reinstalled. |
| Duration | int | Actual duration of interval in seconds. |
| Filler1 | int | Reserved for future use. |
| Utilization | real | Average segment utilization for the time interval. |
| MacDropEvents | real | (MAC-layer statistics) |
| MacOctets | real | |
| MacPkts | real | |
| PurgeEvents | real | |
| PurgePkts | real | |
| BeaconEvents | real | |
| BeaconTime | real | |
| BeaconPkts | real | |
| ClaimTokenEvents | real | |
| ClaimTokenPkts | real | |
| NAUNChanges | real | |
| LineErrors | real | |
| InternalErrors | real | |
| BurstErrors | real | |
| ACErrors | real | |
| AbortErrors | real | |
| LostFrameErrors | real | |
| CongestionErrors | real | |
| FrameCopiedErrors | real | |
| FrequencyErrors | real | |
| TokenErrors | real | |
| SoftErrorReports | real | |
| RingPollEvents | real | |
| DropEvents | real | (promiscuous statistics) |
| Octets | real | |
| Pkts | real | |
| DataBroadcastPkts | real | |
| DataMulticastPkts | real | |
| Pkts18to63 | real | |
| Pkts64to127 | real | |
| Pkts128to255 | real | |
| Pkts256to511 | real | |
| Pkts512to1023 | real | |
| Pkts1024to2047 | real | |
| Pkts2048to4095 | real | |
| Pkts4096to8191 | real | |
| Pkts8192to1800 | real | |
| PktsGreaterThan18K | real | |
| Filler2 | real | |
| Filler3 | real |
| Field | Type | Description |
|---|---|---|
| Agent | char(26) | Agent name. |
| Domain | char(15) | Domain name. |
| StartTime | int | Start time. |
| EndTime | int | End time. |
| StartTimeText | char(15) | Start time (text format). |
| EndTimeText | char(15) | End time (text format). |
| IfSpeed | int | Agent interface speed in bits per second. |
| NetType | char(4) | Agent network type: ET, TR, FDDI, or WAN. |
| Reinstalled | int | Reinstalled flag, nonzero if reinstalled. |
| Duration | int | Actual duration of interval in seconds. |
| Filler1 | int | Reserved for future use. |
| AddressType | char(4) | Host address type. |
| Address | char(20) | Host address, hexidecimal-ASCII format. |
| InUtilization | real | Average inbound utilization for time interval. |
| OutUtilization | real | Average outbound utilization for time interval. |
| InPkts | real | Packets in. |
| OutPkts | real | Packets out. |
| InOctets | real | Octets in. |
| OutOctets | real | Octets out. |
| OutErrors | real | Errors out. |
| OutBroadcastPkts | real | Broadcast packets out. |
| OutMulticastPkts | real | Multicast packets out. |
| Filler2 | real | Reserved for future use. |
| Filler3 | real | Reserved for future use. |
| Field | Type | Description |
|---|---|---|
| Agent | char(26) | Agent name. |
| Domain | char(15) | Domain name. |
| StartTime | int | Start time. |
| EndTime | int | End time. |
| StartTimeText | char(15) | Start time (text format). |
| EndTimeText | char(15) | End time (text format). |
| IfSpeed | int | Agent interface speed in bits per second. |
| NetType | char(4) | Agent network type: ET, TR, FDDI, or WAN. |
| Reinstalled | int | Reinstalled flag, nonzero if reinstalled. |
| Duration | int | Actual duration of interval in seconds. |
| Filler1 | int | Reserved for future use. |
| AddressType | char(4) | Host address type. |
| SrcAddress | char(20) | Source host address, hexidecimal-ASCII format. |
| DstAddress | char(20) | Destination host address, hexidecimal-ASCII format. |
| Utilization | real | Average utilization for time interval, source host to destination host. |
| Pkts | real | Packets source host to destination host. |
| Octets | real | Octets source host to destination host. |
| Errors | real | Errors source host to destination host. |
| Filler2 | real | Reserved for future use. |
| Filler3 | real | Reserved for future use. |
Even if you are planning to use ad hoc queries to generate your own custom reports, you still need to configure certain parameters. Aging parameters are those that control how long Trend Reporter saves information in various database tables. Poller parameters, on the other hand, are those that control the information and associated time intervals the Snapshot daemon is collecting.
To specify how long Trend Reporter waits before aging out (deleting) data from various tables, you need to configure aging parameters. You also use aging parameters to specify the minimum utilization percentage required before information is included in the database. To configure the aging parameters, see the "Configuring Aging Parameters" section in the "Logging and Reporting Using Trend Reporter" chapter.
To specify what agents, agent groups, or switches that you want to log information for, you need to configure logging parameters for Poller. Poller is the function that lets you specify what specific data the Snapshot daemon collects. When you want to configure Poller logging for one or more agents, see the "Configuring Logging Parameters" section in the "Logging and Reporting Using Trend Reporter" chapter.
You can generate ad hoc reports as necessary for your needs by using SQL queries. To generate ad hoc reports, you first must run the msql utility. Once you do so, you can use standard SQL queries to specify certain reports. To run the msql utility, go to the command line, and enter the following:
$NSHOME/bin/msql NSTREND_DB
The msql program contains commands you can use in conjunction with Trend Reporter. It is easy to identify commands in msql--they are always preceded by a backslash (\). As a quick reference, use 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 enter this command and press Enter without specifying a query, the one shown 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 that you specify if you prefer to use one other than vi. |
| \g | Use this command to submit a query to the server for processing. If you enter 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 the workstation monitor. |
| \q | Use this command whenever you want to exit from the msql program. |
When you need to perform maintenance on the database, you must first enter the following command:
$NSHOME/bin/msqladmin
Once you have started the msql program, you can use any of the following commands that apply to your situation:
create databasename
| Lets you specify a new database on the server. Keep in mind that when you enter this command, you should supply your own database name. For example, to create a new database called NYSTATS, you would enter the following:
|
drop databasename
| Lets you delete the database you specify. Again, if you use this command, you must supply the name of an existing database. |
shutdown
| Lets you order the server to shut itself down. You would probably 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 probably use this command if the server had lost power or been rebooted. |
version
| Lets you view various version information about the server. |
Before beginning to use SQL queries with Trend Reporter, you should be familiar with SQL syntax and usage. The following information is provided as a quick reference.
The following sample query is one that you could use to show intervals for the Ethernet probe "et105" where utilization exceeded 10 percent:
select Agent, Domain, StartTimeText, Utilization from seg_et_detail where Agent = 'et105' and Utilization > 10 order by Agent, Domain, StartTimeText
|
| This is the SQL command to find and extract one or more field names you specify. |
Agent
| This is a 26-character field containing an agent name in the segment detail table for Ethernet. |
Domain
| This is a 15-character field containing a domain name in the segment detail table for Ethernet. |
StartTimeText
| This is a 15-character field containing a start time in text format in the segment detail table for Ethernet. |
Utilization
| This is a real type field containing the average segment utilization for a specified time interval in the segment detail table for Ethernet. |
from
| This is the SQL syntax you use to point to the table where the fields you specified are contained. |
seg_et_detail
| This is the name of the segment detail table for Ethernet. |
where
| This is the SQL syntax you use to specify what values fields must match, if applicable. |
Agent =
| This is the name of the field whose value must exactly match the value you specify after the equal sign (=). |
'et105'
| This is the value you want the Agent field to match. |
and
| This is 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 >
| This is the name of the field whose value must be greater than the value you specify after the greater than sign (>). |
10
| This is the value that the Utilization field uses as a baseline; values included in the report must be greater than this baseline value. |
order by
| This is 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
| This is the primary sort variable for the report. |
Domain
| This is the next sort variable for the report. |
StartTimeText
| This is the last sort variable for the report. |
The following is a list of sample queries that run certain reports. These examples give you an idea of how you might structure and run your own ad hoc queries.
select distinct Agent, Domain from protocol_summary order by Agent, Domain
select Agent, Domain, StartTimeText, Utilization from seg_wan_detail where Agent like 'ny?' and Utilization > 60 order by Agent, Domain, StartTimeText
The following list provides other sample queries that you could use with Trend Reporter.
select Agent, Domain, SrcAddress, DstAddress, StartTimeText, EndTimeText, EndTime, Pkts, Octets, Utilization from conv_detail order by Agent, Domain, EndTime, SrcAddress, DstAddress select Agent, Domain, SrcAddress, DstAddress, StartTimeText, EndTimeText, EndTime, Pkts, Octets, Utilization from conv_summary order by Agent, Domain, EndTime, SrcAddress, DstAddress select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from seg_et_detail order by Agent, Domain, StartTime select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from seg_et_summary order by Agent, Domain, StartTime select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from seg_fddi_detail order by Agent, Domain, StartTime select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from seg_fddi_summary order by Agent, Domain, StartTime select Agent, Domain, Address, StartTimeText, EndTimeText, EndTime, InPkts, OutPkts from host_detail order by Agent, Domain, EndTime, Address select Agent, Domain, Address, TargetSnapTimeText, TargetSnapTime from host_snap order by Agent, Domain, TargetSnapTime select Agent, Domain, Address, StartTimeText, EndTimeText, EndTime, InPkts, OutPkts from host_summary order by Agent, Domain, EndTime, Address select Agent, Domain, Address, StartTimeText, EndTimeText, EndTime, InPkts, OutPkts from host_detail 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 select Address, StartTimeText, EndTimeText, EndTime, InPkts, OutPkts, InUtilization, OutUtilization from host_detail where Address = '7e-f1-cc-f0-8f-07' order by EndTime, Address select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from protocol_detail order by Agent, Domain, StartTime select DISTINCT Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, NetType, IfSpeed, StartTime from protocol_summary order by Agent, Domain, StartTime select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from seg_tr_detail order by Agent, Domain, StartTime select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from seg_tr_summary order by Agent, Domain, StartTime 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 select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from seg_wan_detail order by Agent, Domain, StartTime select Agent, Domain, StartTimeText, EndTimeText, Pkts, Octets, Utilization, StartTime from seg_wan_summary order by Agent, Domain, StartTime
If you want, you can store queries in a text file and direct the output to a text file, as the following example shows:
msql NSTREND_DB < query-high-utilization > high-utilization-list
Use the following guidelines to print any report generated on an ad hoc basis using SQL queries. If you want to print a report generated through the Trend Reporter GUI, see the "Printing GUI-Generated Reports" section. Depending on what you want to print and where you want to print it, use any of the examples below:
msql NSTREND_DB < textfilename
textfilename is the text file you have created that contains one or more SQL queries.
msql NSTREND_DB < textfilename > filename
textfilename is the text file you have created that contains one or more SQL queries, and filename is the new file that you specify.
msql NSTREND_DB < textfilename | lpr
textfilename is the text file you have created that contains one or more SQL queries. This command pipes the output to the print spooler (lpr), which then prints it to the default printer. If you want to specify a different printer, you need to specify -Pprintername after entering lpr.
msql NSTREND_DB < textfilename | emailaddress
|
|