Controlling data collection, manipulation and storage on a network with service assurance capabilities

[0001] The present invention relates to the data collection control and more particularly to data collection, manipulation and storage on a network with service assurance capabilities.

[0002] A network system architecture enables multiple applications like voice, data and video to be mixed over the network that comprises switching nodes and communications links. Networks for these applications generally have been designed for a particular type of application, such as teleconferencing applications. Different types of applications, however, have diverse requirements.

[0003] “Quality of Service” or “QoS” parameters in part define these requirements. In the case of ATM networks, such parameters may include errored cell ratio (ECR), cell loss ratio, fixed delay and delay variation parameters. Applications may also indicate a set of parameters called a “traffic contract” that relates to the bandwidth required from the network. The Errored Cell Ratio (ECR) value is the ratio of the ATM cells in error to all the ATM cells sent during some interval. The remaining parameters are described later. Each application may have a maximum or minimum acceptable value for each parameter and a requested value for each parameter.

[0004] As previously indicated, networks are implemented with switching nodes and communications links. The communications links typically have been based upon conventional telephony transmission systems and comprised fiber optic, microwave or wireline links. Fiber optic links transfer typically an error rate of 10.sup.-9; microwave and wireline connections, 10.sup.-7. More recently, communications links have begun to comprise terrestrial and satellite mobile wireless communications links and cable television systems. Each of these communications links are prone to introducing errors at significantly greater rates.

[0005] Some present ATM networks attempt to overcome such errors by implementing a transformation process in the form of a correction, or data manipulation, technique at each switching node on a “per-communications link” basis. That is, the network is designed such that certain links operate with a transforming process that is not alterable on a dynamic basis. For example, it is fairly common that satellite systems have a single error correcting code that operates over the entire bit stream passing over the link, notwithstanding the specific application. Using error correcting codes, such as forward error correction (FEC) codes, requires significant amounts of redundant information to be sent with each block of bits wherein a “block” may comprise a partial ATM cell, a single ATM cell or a plurality of ATM cells. This redundant information adds “overhead” to the transfer. This, in turn, reduces the bandwidth available for transporting an ATM cell. To be effective, an error correcting code must also match the anticipated error burst characteristics of the transmission system; systems that are subject to longer bursts require larger codewords or interleaving or both. Interleaving adds significant amounts of fixed delay to that already present in the transmission system. Such fixed delays manifest themselves, for example, as awkward conversation and user discomfort in a teleconferencing application or even as motion sickness and disorientation in a virtual reality application.

[0006] Despite the foregoing efforts, network failures are inevitable, and there is a need of monitoring network performance for the purpose of maintaining a predetermined agreed upon QoS.

[0007] The present invention provides for controlling the collection, manipulation and storage of network performance data and network event data of a network with service assurance capabilities. Upon the receipt of an activation signal, a signal is transmitted to initiate the retrieving of network performance data and network event data generated from at least one network monitor. Such network monitor is adapted for monitoring a network system and the relocating of the data into a common directory. A signal is then transmitted to initiate the manipulation of the data and the loading of the manipulated data into a database.

[0008] In one embodiment, the signal to initiate the manipulating and storing of the data initiates a controller program that transmits a signal to initiate the manipulation of the data. Such controller program transmits a signal to initiate the calculation of statistics from the data, and further transmits a signal to initiate the assigning of key codes to the data.

[0009] In one aspect of the present invention, the controller program also transmits a signal to initiate the sorting of the data, and further transmits a signal to initiate the concatenating of the data. Still yet, the controller program transmits a signal to initiate the loading of the data into the database.

[0010] As an option, the present invention may further transmit a signal to initiate a clean archive program for deleting files from an archive directory, a signal to initiate a table extract program for extracting data from tables stored in the database, a signal to initiate a trigger reporting program for generating reports, a signal to initiate a purge record program for deleting records from the database, and a signal to initiate a database backup program for backing up data stored on the database.

[0045] A preferred embodiment of a system in accordance with the present invention is preferably practiced in the context of a personal computer such as an IBM compatible personal computer, Apple Macintosh computer or UNIX based workstation. A representative hardware environment is depicted in FIG. 1, which illustrates a typical hardware configuration of a workstation in accordance with a preferred embodiment having a central processing unit 110, such as a microprocessor, and a number of other units interconnected via a system bus 112. The workstation shown in FIG. 1 includes a Random Access Memory (RAM) 114, Read Only Memory (ROM) 116, an I/O adapter 118 for connecting peripheral devices such as disk storage units 120 to the bus 112, a user interface adapter 122 for connecting a keyboard 124, a mouse 126, a speaker 128, a microphone 132, and/or other user interface devices such as a touch screen (not shown) to the bus 112, communication adapter 134 for connecting the workstation to a communication network (e.g., a data processing network) and a display adapter 136 for connecting the bus 112 to a display device 138. The workstation typically has resident thereon an operating system such as the Microsoft Windows NT or Windows/95 Operating System (OS), the IBM OS/2 operating system, the MAC OS, or UNIX operating system. Those skilled in the art will appreciate that the present invention may also be implemented on platforms and operating systems other than those mentioned.

[0046] A preferred embodiment is written using JAVA, C, and the C++ language and utilizes object oriented programming methodology. Object oriented programming (OOP) has become increasingly used to develop complex applications. As OOP moves toward the mainstream of software design and development, various software solutions require adaptation to make use of the benefits of OOP. A need exists for these principles of OOP to be applied to a messaging interface of an electronic messaging system such that a set of OOP classes and objects for the messaging interface can be provided.

[0047] OOP is a process of developing computer software using objects, including the steps of analyzing the problem, designing the system, and constructing the program. An object is a software package that contains both data and a collection of related structures and procedures. Since it contains both data and a collection of structures and procedures, it can be visualized as a self-sufficient component that does not require other additional structures, procedures or data to perform its specific task. OOP, therefore, views a computer program as a collection of largely autonomous components, called objects, each of which is responsible for a specific task. This concept of packaging data, structures, and procedures together in one component or module is called encapsulation.

[0048] In general, OOP components are reusable software modules which present an interface that conforms to an object model and which are accessed at run-time through a component integration architecture. A component integration architecture is a set of architecture mechanisms which allow software modules in different process spaces to utilize each others capabilities or functions. This is generally done by assuming a common component object model on which to build the architecture. It is worthwhile to differentiate between an object and a class of objects at this point. An object is a single instance of the class of objects, which is often just called a class. A class of objects can be viewed as a blueprint, from which many objects can be formed.

[0049] OOP allows the programmer to create an object that is a part of another object. For example, the object representing a piston engine is said to have a composition-relationship with the object representing a piston. In reality, a piston engine comprises a piston, valves and many other components; the fact that a piston is an element of a piston engine can be logically and semantically represented in OOP by two objects.

[0050] OOP also allows creation of an object that “depends from” another object. If there are two objects, one representing a piston engine and the other representing a piston engine wherein the piston is made of ceramic, then the relationship between the two objects is not that of composition. A ceramic piston engine does not make up a piston engine. Rather it is merely one kind of piston engine that has one more limitation than the piston engine; its piston is made of ceramic. In this case, the object representing the ceramic piston engine is called a derived object, and it inherits all of the aspects of the object representing the piston engine and adds further limitation or detail to it. The object representing the ceramic piston engine “depends from” the object representing the piston engine. The relationship between these objects is called inheritance.

[0051] When the object or class representing the ceramic piston engine inherits all of the aspects of the objects representing the piston engine, it inherits the thermal characteristics of a standard piston defined in the piston engine class. However, the ceramic piston engine object overrides these ceramic specific thermal characteristics, which are typically different from those associated with a metal piston. It skips over the original and uses new functions related to ceramic pistons. Different kinds of piston engines have different characteristics, but may have the same underlying functions associated with it (e.g., how many pistons in the engine, ignition sequences, lubrication, etc.). To access each of these functions in any piston engine object, a programmer would call the same functions with the same names, but each type of piston engine may have different/overriding implementations of functions behind the same name. This ability to hide different implementations of a function behind the same name is called polymorphism and it greatly simplifies communication among objects.

[0052] With the concepts of composition-relationship, encapsulation, inheritance and polymorphism, an object can represent just about anything in the real world. In fact, one's logical perception of the reality is the only limit on determining the kinds of things that can become objects in object-oriented software. Some typical categories are as follows:

• • [0053] Objects can represent physical objects, such as automobiles in a traffic-flow simulation, electrical components in a circuit-design program, countries in an economics model, or aircraft in an air-traffic-control system.
  • [0054] Objects can represent elements of the computer-user environment such as windows, menus or graphics objects.
  • [0055] An object can represent an inventory, such as a personnel file or a table of the latitudes and longitudes of cities.
  • [0056] An object can represent user-defined data types such as time, angles, and complex numbers, or points on the plane.

[0057] With this enormous capability of an object to represent just about any logically separable matters, OOP allows the software developer to design and implement a computer program that is a model of some aspects of reality, whether that reality is a physical entity, a process, a system, or a composition of matter. Since the object can represent anything, the software developer can create an object which can be used as a component in a larger software project in the future.

[0058] If 90% of a new OOP software program consists of proven, existing components made from preexisting reusable objects, then only the remaining 10% of the new software project has to be written and tested from scratch. Since 90% already came from an inventory of extensively tested reusable objects, the potential domain from which an error could originate is 10% of the program. As a result, OOP enables software developers to build objects out of other, previously built objects.

[0059] This process closely resembles complex machinery being built out of assemblies and sub-assemblies. OOP technology, therefore, makes software engineering more like hardware engineering in that software is built from existing components, which are available to the developer as objects. All this adds up to an improved quality of the software as well as an increased speed of its development.

[0060] Programming languages are beginning to fully support the OOP principles, such as encapsulation, inheritance, polymorphism, and composition-relationship. With the advent of the C++ language, many commercial software developers have embraced OOP. C++ is an OOP language that offers a fast, machine-executable code. Furthermore, C++ is suitable for both commercial-application and systems-programming projects. For now, C++ appears to be the most popular choice among many OOP programmers, but there is a host of other OOP languages, such as Smalltalk, Common Lisp Object System (CLOS), and Eiffel. Additionally, OOP capabilities are being added to more traditional popular computer progranu\ring languages such as Pascal.

[0061] The benefits of object classes can be summarized, as follows:

• • [0062] Objects and their corresponding classes break down complex programming problems into many smaller, simpler problems.
  • [0063] Encapsulation enforces data abstraction through the organization of data into small, independent objects that can communicate with each other. Encapsulation protects the data in an object from accidental damage, but allows other objects to interact with that data by calling the object's member functions and structures.
  • [0064] Subclassing and inheritance make it possible to extend and modify objects through deriving new kinds of objects from the standard classes available in the system. Thus, new capabilities are created without having to start from scratch.
  • [0065] Polymorphism and multiple inheritance make it possible for different programmers to mix and match characteristics of many different classes and create specialized objects that can still work with related objects in predictable ways.
  • [0066] Class hierarchies and containment hierarchies provide a flexible mechanism for modeling real-world objects and the relationships among them.
  • [0067] Libraries of reusable classes are useful in many situations, but they also have some limitations. For example:
  • [0068] Complexity. In a complex system, the class hierarchies for related classes can become extremely confusing, with many dozens or even hundreds of classes.
  • [0069] Flow of control. A program written with the aid of class libraries is still responsible for the flow of control (i.e., it must control the interactions among all the objects created from a particular library). The programmer has to decide which functions to call at what times for which kinds of objects.
  • [0070] Duplication of effort. Although class libraries allow programmers to use and reuse many small pieces of code, each programmer puts those pieces together in a different way. Two different programmers can use the same set of class libraries to write two programs that do exactly the same thing but whose internal structure (i.e., design) may be quite different, depending on hundreds of small decisions each programmer makes along the way. Inevitably, similar pieces of code end up doing similar things in slightly different ways and do not work as well together as they should.

[0071] Class libraries are very flexible. As programs grow more complex, more programmers are forced to reinvent basic solutions to basic problems over and over again. A relatively new extension of the class library concept is to have a framework of class libraries. This framework is more complex and consists of significant collections of collaborating classes that capture both the small scale patterns and major mechanisms that implement the common requirements and design in a specific application domain. They were first developed to free application programmers from the chores involved in displaying menus, windows, dialog boxes, and other standard user interface elements for personal computers.

[0072] Frameworks also represent a change in the way programmers think about the interaction between the code they write and code written by others. In the early days of procedural programming, the programmer called libraries provided by the operating system to perform certain tasks, but basically the program executed down the page from start to finish, and the programmer was solely responsible for the flow of control. This was appropriate for printing out paychecks, calculating a mathematical table, or solving other problems with a program that executed in just one way.

[0073] The development of graphical user interfaces began to turn this procedural programming arrangement inside out. These interfaces allow the user, rather than program logic, to drive the program and decide when certain actions should be performed. Today, most personal computer software accomplishes this by means of an event loop which monitors the mouse, keyboard, and other sources of external events and calls the appropriate parts of the programmer's code according to actions that the user performs. The programmer no longer determines the order in which events occur. Instead, a program is divided into separate pieces that are called at unpredictable times and in an unpredictable order. By relinquishing control in this way to users, the developer creates a program that is much easier to use. Nevertheless, individual pieces of the program written by the developer still call libraries provided by the operating system to accomplish certain tasks, and the programmer must still determine the flow of control within each piece after it's called by the event loop. Application code still “sits on top of” the system.

[0074] Even event loop programs require programmers to write a lot of code that should not need to be written separately for every application. The concept of an application framework carries the event loop concept further. Instead of dealing with all the nuts and bolts of constructing basic menus, windows, and dialog boxes and then making these things all work together, programmers using application frameworks start with working application code and basic user interface elements in place. Subsequently, they build from there by replacing some of the generic capabilities of the framework with the specific capabilities of the intended application.

[0075] Application frameworks reduce the total amount of code that a programmer has to write from scratch. However, because the framework is really a generic application that displays windows, supports copy and paste, and so on, the programmer can also relinquish control to a greater degree than event loop programs permit. The framework code takes care of almost all event handling and flow of control, and the programmer's code is called only when the framework needs it (e.g., to create or manipulate a proprietary data structure).

[0076] A programmer writing a framework program not only relinquishes control to the user (as is also true for event loop programs), but also relinquishes the detailed flow of control within the program to the framework. This approach allows the creation of more complex systems that work together in interesting ways, as opposed to isolated programs, having custom code, being created over and over again for similar problems.

[0077] Thus, as is explained above, a framework basically is a collection of cooperating classes that make up a reusable design solution for a given problem domain. It typically includes objects that provide default behavior (e.g., for menus and windows), and programmers use it by inheriting some of that default behavior and overriding other behavior so that the framework calls application code at the appropriate times.

[0078] There are three main differences between frameworks and class libraries:

• • [0079] Behavior versus protocol. Class libraries are essentially collections of behaviors that you can call when you want those individual behaviors in your program. A framework, on the other hand, provides not only behavior but also the protocol or set of rules that govern the ways in which behaviors can be combined, including rules for what a programmer is supposed to provide versus what the framework provides.
  • [0080] Call versus override. With a class library, the code the programmer instantiates objects and calls their member functions. It's possible to instantiate and call objects in the same way with a framework (i.e., to treat the framework as a class library), but to take full advantage of a framework's reusable design, a programmer typically writes code that overrides and is called by the framework. The framework manages the flow of control among its objects. Writing a program involves dividing responsibilities among the various pieces of software that are called by the framework rather than specifying how the different pieces should work together.
  • [0081] Implementation versus design. With class libraries, programmers reuse only implementations, whereas with frameworks, they reuse design. A framework embodies the way a family of related programs or pieces of software work. It represents a generic design solution that can be adapted to a variety of specific problems in a given domain. For example, a single framework can embody the way a user interface works, even though two different user interfaces created with the same framework might solve quite different interface problems.

[0082] Thus, through the development of frameworks for solutions to various problems and programming tasks, significant reductions in the design and development effort for software can be achieved. A preferred embodiment of the invention utilizes HyperText Markup Language (HTML) to implement documents on the Internet together with a general-purpose secure communication protocol for a transport medium between the client and the Newco. HTTP or other protocols could be readily substituted for HTML without undue experimentation. Information on these products is available in T. Berners-Lee, D. Connoly, “RFC 1866: Hypertext Markup Language-2.0” (November 1995); and R. Fielding, H, Frystyk, T. Berners-Lee, J. Gettys and J. C. Mogul, “Hypertext Transfer Protocol—HTTP/1.1: HTTP Working Group Internet Draft” (May 2, 1996). HTML is a simple data format used to create hypertext documents that are portable from one platform to another. HTML documents are SGML documents with generic semantics that are appropriate for representing information from a wide range of domains. HTML has been in use by the World-Wide Web global information initiative since 1990. HTML is an application of ISO Standard 8879; 1986 Information Processing Text and Office Systems; Standard Generalized Markup Language (SGML).

[0083] To date, Web development tools have been limited in their ability to create dynamic Web applications which span from client to server and interoperate with existing computing resources. Until recently, HTML has been the dominant technology used in development of Web-based solutions. However, HTML has proven to be inadequate in the following areas:

• • [0084] Poor performance;
  • [0085] Restricted user interface capabilities;
  • [0086] Can only produce static Web pages;
  • [0087] Lack of interoperability with existing applications and data; and
  • [0088] Inability to scale.

[0089] Sun Microsystem's Java language solves many of the client-side problems by:

• • [0090] Improving performance on the client side;
  • [0091] Enabling the creation of dynamic, real-time Web applications; and
  • [0092] Providing the ability to create a wide variety of user interface components.

[0093] With Java, developers can create robust User Interface (UI) components. Custom “widgets” (e.g., real-time stock tickers, animated icons, etc.) can be created, and client-side performance is improved. Unlike HTML, Java supports the notion of client-side validation, offloading appropriate processing onto the client for improved performance. Dynamic, real-time Web pages can be created. Using the above-mentioned custom UI components, dynamic Web pages can also be created.

[0094] Sun's Java language has emerged as an industry-recognized language for “programming the Internet.” Sun defines Java as: “a simple, object-oriented, distributed, interpreted, robust, secure, architecture-neutral, portable, high-performance, multithreaded, dynamic, buzzword-compliant, general-purpose programming language. Java supports programming for the Internet in the form of platform-independent Java applets.” Java applets are small, specialized applications that comply with Sun's Java Application Programming Interface (API) allowing developers to add “interactive content” to Web documents (e.g., simple animations, page adornments, basic games, etc.). Applets execute within a Java-compatible browser (e.g., Netscape Navigator) by copying code from the server to client. From a language standpoint, Java's core feature set is based on C++. Sun's Java literature states that Java is basically, “C++ with extensions from Objective C for more dynamic method resolution.”

[0095] Another technology that provides similar function to JAVA is provided by Microsoft and ActiveX Technologies, to give developers and Web designers wherewithal to build dynamic content for the Internet and personal computers. ActiveX includes tools for developing animation, 3-D virtual reality, video and other multimedia content. The tools use Internet standards, work on multiple platforms, and are being supported by over 100 companies. The group's building blocks are called ActiveX Controls, small, fast components that enable developers to embed parts of software in hypertext markup language (HTML) pages. ActiveX Controls work with a variety of programming languages including Microsoft Visual C++, Borland Delphi, Microsoft Visual Basic programming system and, in the future, Microsoft's development tool for Java, code named “Jakarta.” ActiveX Technologies also includes ActiveX Server Framework, allowing developers to create server applications. One of ordinary skill in the art readily recognizes that ActiveX could be substituted for JAVA without undue experimentation to practice the invention.

Overall Architecture

[0096] One embodiment of the present invention is composed of multiple software programs which are linked together to create an architecture which is capable of monitoring a network for events and checking system functions and resources. Such events can include alarms, faults, alerts, etc. Other embodiments of the present invention may each include an individual software program.

[0097] Reports on system performance, errors, etc. can be generated and output. For example, the reports may depict to operators/administrators of the network what is happening inside the network in real-time. This allows the administrators to respond to problems before disruptions to service occur.

[0098] Accordingly, FIG. 2 illustrates an embodiment of the present invention which provides service assurance for a network. In operation 200, an alarm is generated to indicate a status of a network. Network event information of the network is dispatched in operation 202 upon generation of the alarm after which the network event information is mapped in operation 204. The data collected on the status of the network is manipulated and stored in a database. See operations 206 and 208. In operation 210, availability of the network is conveyed graphically.

Collector

[0099] A collector may be used to collect system information including events and performance data and route pertinent portions of the system information to a notifier program which informs a system administrator of the system information. One example of a collector is Netcool® manufactured by Micromuse Inc., whose headquarters are located at 139 Townsend St., San Francisco, Calif. 94107.

Notifier

[0100] In one aspect of the present invention, a notifier may form part of the architecture or may stand alone. The notifier is a configurable decision engine that determines how to dispatch system information to users. Notification actions can include initiating alphanumeric pages, trouble tickets, log messages, and external scripts. The notifier is integrated closely with the collector and is able to forward alerts to specific individuals based on the contents of the alert and the time/day it was. The notifier also allows an alert to trigger more than one type of notification. This enables functionality like creating a trouble ticket before paging so that the trouble ticket number can be sent along with an alphanumeric page.

[0101] The notifier creates integration between the Collector, and any of a number of other programs which may include an alphanumeric paging program and/or a trouble tickets generation program. Preferably, the notifier can be modified to integrate with existing systems. The notifier may be written in the PERL language.

[0102] Accordingly, FIG. 3 illustrates one embodiment of the present invention for dispatching network event information of a network with service assurance capabilities. In operation 300, a network is monitored for an event. Thereafter, in operation 302, at least one notification action is generated based upon the occurrence of the event. The notification action may include an alphanumeric page, an e-mail message, a resolution script, a remedy trouble ticket, and/or a log message. Further, the notification action may be transmitted in operation 304 to notify a recipient about the occurrence of the event.

[0103] In one aspect of the present invention, the step of generating the notification action further includes the steps of: determining the type of event that occurred, and generating at least one notification action associated with the determined type of event. Further, the order in which notification actions are generated upon the occurrence of the event may be based upon the determined type of event.

[0104] In another aspect of the present invention, the step of transmitting the notification action may further include the steps of: determining characteristics of the occurred event, selecting at least one recipient for receiving the notification action based upon the determined characteristics of the occurred event, and transmitting the notification action to the selected recipient.

[0105] In still yet another aspect of the present invention, the step of transmitting the notification action may further include the step of determining the time of occurrence of the occurred event. Such selection of the recipient for receiving the notification action may be further based upon the occurrence time of the occurred event. In addition, the occurred event may be compared to a list of scheduled events such that the generation of the notification action may be inhibited when the occurred event matches of the listed scheduled events.

Automation Programs

[0106] Automations are a configurable function in the collector. Automations enhance the collector's functionality by adding intelligence and/or automating tasks. Exemplary automations include:

[0107] Fault/Resolution Pairing—each resolution alert received (e.g. Link Up) will trigger an automation to try and find its corresponding fault notification (Link Down, which would have been generated earlier) by matching the node and the problem uniquely. When a F/R pair is found, the resolution alert is removed and the fault alert is cleared (no longer a “critical” or “warning” status). Additional information can be added to the alert including the duration of the fault.

[0108] Event and Notification Logging—when a notification is sent out by the notifier, an automation can update the alert for which a notification was generated to indicate what action was taken (page, trouble ticket number, logged, etc;).

[0109] Maintenance—all cleared alerts can be removed from the event list after a specified period, reducing distractions from the operators.

Data Acquisition

[0110] In another embodiment of the present invention, Data Acquisition scripts are programs which coordinate the collection and transfer of application logs to a central location. Data Acquisition can be used so that log files containing performance statistics gathered by a monitoring program can be transferred to a central server for processing by the Performance Data Manipulator (see below). The Data Acquisition scripts may be written in PERL.

Performance Data Manipulator (PDM)

[0111] In an embodiment of the present invention, a PDM is a script that processes log files that have been collected by Data Acquisition in order to load the data into a database. The PDM converts the log files from formats specific to a particular monitoring program into a common format. PDM then formats the file based on data warehousing techniques which include converting nodes and performance metrics to key codes which are stored in the database. The coded data file is then bulk loaded into the database. The PDM may be written in PERL.

Database Data Warehouse Design

[0112] An embodiment of the present invention includes a database schema for storing and reporting on network element performance data. The schema should be built for the efficient storage, processing, and aging of performance data.

Time Key Preloader

[0113] The Time Key Preloader is a script that supports the efficient loading of data in the database. In prior art database systems, due to the use of “key codes” in the design of databases a unique time key needed to be created for each entry stored in the database. The original solution to this was to create a time key every time an entry was received. This is inefficient. One embodiment of the present invention includes the Time Key Preloader which pre-generates time keys for every second, minute, five minutes, and/or hour, etc. of the day all at once using a direct interface to the database. Thus, the time key for the time an alert or metric is received becomes associated with the alert or performance metric. The Time Key Preloader may be written in PERL.

[0114] FIG. 4 illustrates an embodiment of the present invention for assigning time-based identifiers to data stored in a database. First, prior to receiving data, a plurality of time-based identifiers are generated in operation 400. Next, in operation 402, the time-based identifiers are saved. Upon receipt of data in operation 404, one of the appropriate time-based identifiers is assigned to the received data in operation 406. In operation 408, the received data is stored with the assigned time-based identifier in a database.

[0115] In one aspect of the present invention, the time-based identifiers are generated for every second, minute, and hour of every day at once. The time-based identifiers may be generated using an interface to the database. Further, the time-based identifiers may be stored in a database separate from that in which the data is stored.

[0116] In yet another aspect of the present invention, the received data relates to a parameter of a network. Further, the data may be stored for service assurance purposes on the network.

Event Correlator and Manager (ECM)

[0117] One embodiment of the present invention includes an event correlation application which uses behavior models to correlate network conditions, identify critical problems, filter out superfluous events and take appropriate actions. An example of an ECM is NerveCenter manufactured by Seagate Software, 920 Disc Drive, Scotts Valley, Calif. 95067.

Patroller

[0118] An embodiment of the present invention includes a patroller which discover the environment of a network, continuously surveys related systems, and/or initiates alarms based on preset parameters. An example of a Patroller is BMC Patrol manufactured by BMC Software, BMC Software, Inc., 2101 CityWest Blvd., Houston, Tex. 77042-2827.

Alarm Definitions

[0119] Alarm Definitions are customizations to an ECM which allow for event correlation and alarm suppression. Preferably, the Alarm Definitions are specific to particular networks in order to add automated intelligence for problem resolution and to reduce unneeded alarm traffic sent to operators.

Automation Scripts and Knowledge Modules

[0120] Automation scripts allow the automation of application and system management tasks. Automation scripts may monitor application health and perform corrective actions when the status of an application changes. Other functions may include sending SNMP traps when specific conditions are detected in an application or system.

Reporting Interface Development, Customer Service Integration Module

[0121] One embodiment of the present invention provides the ability to correlate network events to individual customers (or providers in a Managed Network Services world) and notify customer service representatives of known outages affecting customers through a web interface. This allows proactive notification to customers of problems that affect them as well as builds confidence in customers calling to report problems that the provider is aware of.

Data Collection Overview

[0122] Referring to FIG. 5, in one embodiment of the present invention, an activation signal is received in operation 500. Upon receipt of the activation signal, a signal is transmitted in operation 502 to initiate the retrieving of network performance data and network event data generated from at least one network monitor. Such network monitor is adapted for monitoring a network system and the relocating of the data into a common directory. Then, in operation 504, the signal is transmitted to initiate the manipulation of the data and the loading of the manipulated data into a database.

[0123] In one embodiment, the signal to initiate the manipulating and storing of the data initiates a controller program that transmits a signal to initiate the manipulation of the data. Such controller program transmits a signal to initiate the calculation of statistics from the data, and further transmits a signal to initiate the assigning of key codes to the data.

[0124] In another aspect of the present invention, the controller program also transmits a signal to initiate the sorting of the data, and further transmits a signal to initiate the concatenating of the data. Still yet, the controller program transmits a signal to initiate the loading of the data into the database.

[0125] As an option, the present invention may further transmit a signal to initiate a clean archive program for deleting files from an archive directory, a signal to initiate a table extract program for extracting data from tables stored in the database, a signal to initiate a trigger reporting program for generating reports, a signal to initiate a purge record program for deleting records from the database, and a signal to initiate a database backup program for backing up data stored on the database.

[0126] The following subsections describe an embodiment of the present invention that controls the collection, manipulation and storage of network performance data and network event data of a network with service assurance capabilities and provides an exemplary step-by-step overview of the flow of data from collection to when it's loaded into the database. FIG. 6 is a flowchart that provides an overview of a data collection process of one embodiment of the present invention.

[0127] In this exemplary embodiment, Seagate NerveCenter will be used as the Event Correlator and Manager 600 and BMC Patrol will be used as the Patroller 602.

Applications

[0128] The data collection is started by the network monitory applications creating their ASCII text data files. These files are generally stored locally on the machines they are running on. Specifics on where these files should be stored are located in the installation & configuration instructions for each application.

Cron

[0129] The cron 604 process daily activates the processing scripts. The .crontab file should be modified on the system which will do the actual processing of the files.

[0130] The cron process starts one script, sa_—master_—process_—control.pl 606, which then begins the rest of the processes

sa_—master_—process_—control.pl

[0131] The sa_—master_—process_—control.pl script 606 does not actually move or manipulate any of the data files, but instead starts other scripts which will move and manipulate the data to the appropriate locations and into the required formats. There are 8 sequential steps to this process, each beginning after the previous has finished. The steps are as follows:

• [0132] 1. clean.pl 608
• [0133] 2. get_—data.pl

[0134] The get_—data.pl script 610 is designed to move files from the systems where the data is being collect to the /sa directory structure where it will then be further processed. There are three options that this script can do. The three options are (a) to move files locally on a Unix system, (b) to move files from a remote Unix system, and (c) to move files from a remote NT system.

[0135] The script gets its settings from a configuration file named get_—data.cfg 612. The most important part is the configuration section for each application:

[0136] #******************** Host Identification Section *******************

[0137] #$NodeName˜$Location˜$SourcePath˜$SourceExt˜$TargetPath˜$TmpExt˜$Mov eMethod˜$Archive˜#$ArchivePath˜$ArchiveExt˜$TransferType˜$Platform˜$Targ etExt˜$DestHost˜$LogFile˜$UnixRemoteScript

[0138] #*********************Example****************************** ***

[0139] nsmmws16˜remote˜/opt/PATROLLER/Solaris25-sun4/remote/˜.dat˜/sa/dev/dat/pr ocess/˜.tmp˜move˜yes˜/opt/PATROLLER3.2/Solaris25-sun4/remote/˜.old˜ftp˜Un ix˜.pat˜twmmdb02˜/files0/home/noc/bin/UnixRemote.log˜/files0/home/noc/bin/Un ixRemote.pl

[0140] Use the definition above and the example as a guide on adding more applications and host systems.

[0141] For moving files remotely from a Unix system, the UnixRemote.pl script 614 is called using remsh. Once UnixRemote.pl finishes, the verify.cmp file is used to make sure that all the files were properly transferred.

UnixRemote.pl

[0142] UnixRemote.pl 614 is called using remsh on the system which the files are to be moved. It first deletes the *.old files, which are the data files from the previous day. Then the current data files are renamed to $node_—$date_—*.$extension. A verify.cmp file is created to later verify that all the files are transferred. The *.$extension files and the verify.cmp file are then transferred to the /sa directory structure, usually by ftp. Once that's done, the old files are archived by change $extension to $extension.old, which flags them for deletion the next day.

• [0143] 3. extract 616
• [0144] 4. data_—manip_—wrapper.pl

[0145] The data_—manip_—wrapper.pl script 618 controls the action manipulation of the data by calling a number of subscripts, each performing it's own function. Each script is called when the previous script has finished.

data_—manip_—stage1.pl

[0146] The data_—manip_—stage1.pl script 620 is the main script which massages the data. If new applications are added, this is the script which will need to be modified. This script takes the individual data files in the /sa directory structure, reads in the data_—manip_—stage1.cfg configuration files, manipulates the data so that it's in the format defined by data_—manip_—stage1.cfg, and then outputs files in the form of $element.element.

calculate_—stats.pl

[0147] The calculate_—stats.pl script 622 calculates the mean, max, min, sample, and standard deviation for each element file. The output of the script is $element.element.stage2 and $element.element.stat for each element file.

key_—codes.pl

[0148] The key_—codes.pl script 624 uses perf_—metric_—tb.ext and network_—element_—tb.ext to look up the codes associated with the node or element being monitored. It assigns the key code as the name of the file (i.e. keycode.element.stage2 and keycode.element.stat). It also produces perf_—metric_—time_—tb.dat.

sort_—elmnt_—files.pl

[0149] The sort_—elmnt_—files.pl 626 sorts all of the keycode.element.stage2 and keycode.element.stat files and renames them keycode.element.stage2.sorted and keycode.element.stat.sorted. union_—all.pl

[0150] The union_—all.pl script 628 takes the keycode.element.stage2. sorted files and concatenates them into perf_—fact_—tb.dat and the keycode.element.stat.sorted files and concatenates them into perf_—fact_—dly_—tb.dat.

move.pl

[0151] The move_—all.pl script 630 moves perf_—fact_—tb.dat, perf_—fact_—dly_—tb.dat and perf_—metric_—time_—tb.dat to /files6/ipsa/data_—loads/data_—files.

• [0152] 5. data_—2_—db.pl 632
• [0153] 6. purge_—records.pl 634
• [0154] 7. backup.pl 636
• [0155] 8. trigger_—reporting.pl 638
  Call Script

[0156] This script does system calls and utilizes FTP to run other scripts. It calls the scripts in a specific order and logs successes and failures.

Scripts on other machines

[0157] If scripts need to be run on other machines, FTP should be used. With FTP, a configuration file is needed to house user and password information. Also, an account may have to be set up on that machine to be able to run that script.

NT Account

From the NT host:

[0158] Open Microsoft Internet Information Service (IIS), from there open Internet Services Manager

[0159] Right-Click on SA Ftp Site. Click on home directory and make sure this is share, not local. Add path, Network Share path, and make sure that Write and Log Access is accessed.

[0160] In the Security Accounts tab, make sure that the radio button for “Not allow anonymous connections is clicked.

[0161] In the FTP Site tab, make sure that the connections are limited to 1.

[0162] In the Directory Security tab, make sure the radio button for Denied Access is clicked and add the domain for the server that is attempting the FTP.

UNIX

[0163] The user and password being used to FTP must be supported by the system being accessed.

FTP example

• [0164] #
• [0165] # Trigger daily reporting
• [0166] #Create $ReportingScript in /sa/dev/dat/tmp with string “This file is used to trigger the daily reporting process”
• [0167] open (FILE,>$SACommon::SATempDir${ReportingScript})||die “not able to open $ReportingScript ($!)”;
• [0168] print FILE “This file is used to trigger the daily reporting process”;
• [0169] #Transfer the file to $NTHost
• [0170] $ftp=Net::FTP->new(“$NTHost”);
• [0171] $ftp->login(“$User”, “$Password”);
• [0172] $ftp->cwd(“$NTPath”);
• [0173] $Return=$ftp->put(“$SACommon::SATempDir$ {ReportingScript}”, “$NTPath\$ReportingScript”);
• [0174] $ftp->quit;
• [0175] #Process the return value
• [0176] if(0!=Return) {
  • [0177] &SACommon::WriteLog(“FTP of $ReportingScript failed”);
• [0178] }elsif (0=$Return) {
  • [0179] &SACommon::WriteErrorLog(“FTP of $ReportingScript was successful”);
• [0180] }
• [0181] #Delete $ReportingScripts file from /sa/dev/dat/tmp
• [0182] close (FILE);
• [0183] unlink ($SACommon::SATempDir$ {ReportingScript});
• [0184] Scripts on Local machines

[0185] The local scripts are called with a system call then logged successful or failure.

[0186] System command example

• [0187] #
• [0188] #Call Cleanup Utility
• [0189] $Return=(system($SACommon::SABinDir/$CleanupScript));
• [0190] if (0!=$Return) {
  • [0191] &SACommon::WriteLog(“$CleanupScript was successful”);
• [0192] }elsif (0=$Return) {
  • [0193] &SACommon::WriteErrorLog(“$CleanupScript failed to run”); }
• [0194] Order of Scripts Called
• [0195] 1. Cleanup Utility 608
• [0196] 2. Data Acquisition Utility 610
• [0197] 3. Table Extract 616
• [0198] 4. Data Manipulation 618
• [0199] 5. Data Loader 632
• [0200] 6. IPSA_—ProcessTheBatchQueue.txt (Reporting SPSS) 638
• [0201] 7. Purge Records 634
• [0202] 8. Backup 636
  get data.pl script

[0203] The get_—data.pl script, along with the get_—data.cfg file, retreves data from both local and remote hosts and relocates it in a common directory. The get_—data.pl script also uses the UnixRemote.pl script, which resides on the remote UNIX machines from which data is being acquired. A similar script could also be designed for use with remote NT machines. The get_—data.cfg file is the configuration file used to define all program specific information and parameters. There are additional variables and settings defined in the SACommon.pm Perl module, which is located in /sa/usr/mod/. In general, SACommon.pm contains global variables and sub-routines.

Configuration File (get_—data.cfg):

[0204] The following is an example of what the configuration file entries look like. Each line is read by _—data.pl and then acted on according to the logic defined in the script.

[0205] #******************** Host Identification Section (continuous line) *******************

[0206] $NodeName˜$Location˜$SourcePath˜$SourceExt˜$TargetPath˜$TmpExt˜$Move Method˜$Archive˜

[0207] $ArchivePath˜$ArchiveExt˜$TransferType˜$Platform˜$TargetExt˜$DestHost˜$L ogFile˜$UnixRemoteScript

[0208] #***********************Example (continuous line)********************************

[0209] nsmmws16˜remote˜/opt/PATROLLER/Solaris25sun4/remote/test/˜.dat˜/sa/dev/dat /process/˜.tmp˜

[0210] move˜yes˜/opt/PATROLLER/Solaris25sun4/remote/test/˜.old˜ftp˜Unix˜.pat˜twm mdb02˜/files0/home/noc/bin/UnixRemote.log˜/files0/home/noc/bin/UnixRemote.pl

[0211] The information contained in each entry is unique to the data being retrieved. The “˜” character is used as field delimiter throughout the file. Following is a description of the fields that must be defined for each entry:

• [0212] $NodeName—DNS name or alias for the local/remote host from which files need to be collected.
• [0213] $Location—Options include “local” or “remote”.
• [0214] $SourcePath—Complete source path designating the directory from which data will be retrieved.
• [0215] $SourceExt—File extension used to designate which files need to be collected.
• [0216] $TargetPath—Complete target path designating the destination directory, on
• [0217] $DestHost, where files should be transferred to.
• [0218] $TmpExt
• [0219] $MoveMethod
• [0220] $Archive
• [0221] $ArchivePath
• [0222] $ArchiveExt
• [0223] $TransferType
• [0224] $Platform
• [0225] $TargetExt
• [0226] $DestHost—DNS name or alias for the host where files will to be transferred to.

[0227] It is not necessary for the destination host to be the system calling get_—data.pl.

• [0228] $LogFile
• [0229] $UnixRemoteScript

[0230] There is a single variable, $ScriptName, defined locally within get_—data.pl. It is recommended for use with the WriteErrorLog and WriteLog sub-routines provided in SACommon.pm. This is a static variable, and should not need to be changed unless the name of the script is changed.

Data Acquisition Local and Remote

[0231] FIG. 7 illustrates an embodiment of the present invention for retrieving and relocating event and performance data of a network with service assurance capabilities. First, in operation 700, a data file is obtained from a host. The data file includes event data collected on a network and/or performance data collected on the network. In operation 702, a verification control file is created that is associated with the data file. The data file is renamed in operation 704 and copied to a target directory in operation 706. Thereafter, the copying of the renamed data file is verified with the verification control file in operation 708.

[0232] In one aspect of the present invention, information is added to the renamed data file relating to host name, date, and application extension. Further, the type of host is determined from which the data file was obtained. The type of host is selected from a local host and/or a remote host.

[0233] In another aspect of the present invention, a platform of the determined host is determined when the host is determined to be a remote host. Further, the verification control file may be copied with the renamed data file to the target directory. In addition, the data file may be archived.

[0234] In yet another embodiment, previously archived data files may be deleted. Further, a move method may be determined for the data file. A temporary copy of the data file may be created based on the move method. Such move method includes moving the data file and/or copying the data file.

[0235] FIG. 8 illustrates operation of the main data acquisition script (get_—data.pl) 800 for each entry in the configuration file (get_—data.cfg) 802. As shown in FIG. 8, the main data acquisition script (get_—data.pl) reads the configuration file (get_—data.cfg) once, at the beginning of processing, and stores necessary information in memory. Specifically, user and password are stored as variables, and the host entries are stored as an array. Each host entry is then processed in order:

• [0236] 1. If the data files are local, the get_—data.pl script will execute the following steps.
• [0237] Remove old archived files. 804
• [0238] If the files are to be moved, they are renamed with .tmp. 806
• [0239] Parse the system data and reconfigure for file renaming. 808
• [0240] Rename the files, add machine name, date and application extension. 810
• [0241] Copy the files to the target directory. 812
• [0242] Archive the moved files, rename by appending old to the file name. 814
• [0243] 2. If the data files are remote and Unix, the remote shell command is used to run the UnixRemote.pl script, which resides on the remote machines. UnixRemote.pl will execute and perform the following steps remotely.
• [0244] Delete old archived files. 816
• [0245] If the files are to be moved, they are renamed with .tmp. 818
• [0246] Parse the system data and reconfigure for file renaming. 820
• [0247] Rename the files, add machine name, date and application extension. 822
• [0248] Ftp the files to the target directory and create a verification file. 824
• [0249] Archive the moved files, rename by appending old to the file name. 826
• [0250] It returns to get_—data.pl and get_—data.pl continues to execute the verification of the FTP. (To be able to execute FTP commands, the Net::FTP Perl module must be used). 828
• [0251] 3. If the data files are remote and the platform is NT, the remote shell command will be used to run a script that will reside on the remote machine. The script will execute the following steps remotely.
• [0252] Delete old archived files. 830
• [0253] If the files are to be moved, they are renamed with .tmp. 832
• [0254] Parse the system data and reconfigure for file renaming. 834
• [0255] Rename the files, add machine name, date and application extension. 836
• [0256] Ftp the files to the target directory and create a verification file. 838
• [0257] Archive the moved files, rename by appending .old to the file name. 840
• [0258] It returns to get_—data.pl and get_—data.pl continues to execute the verification of the FTP. (To be able to execute FTP commands, the Net::FTP Perl module must be used). 842
  Data Manipulation Scripts

[0259] The scripts contained in the general function of data manipulation are: data_—manip_—stage1.pl. calculate_—stats.pl, key_—codes.pl, sort_—elmnt files.pl, union_—all.pl and move_—all.pl. The combination of these scripts takes the data from /sa/dev/dat/process, manipulates it and performs mathematical computations on it then moves it to the /files6/ipsa/data_—loads/data_—files so that the SQL loader can load it into the database.

Detailed Description of Modules

data_—manip_—stage.pl

[0260] The data_—manip_—stage1.pl script is the main script which manipulates the data. If new applications are added, this will need to be modified. This script takes the individual data files in the configuration files, manipulates the data so that it is in the format defined by data_—manip_—stage1.cfg and then outputs files in the form of <element>.element. This manipulation of data has several steps:

• [0261] Filters out records that contain metrics we do not use for reporting,
• [0262] Filters out fields we do not use for reporting
• [0263] Reorders the fields we do want into the order <element>, <metric>, <instance>, <date-time stamp>, <value>
• [0264] Converts raw metric names to standard metric names (according to a table)
• [0265] Converts Date Time Stamps to Standard
  calculate_—stats.pl

[0266] The calculate_—stats.pl script calculates the mean, maximum, minimum, sample size, and standard deviation for each metric in each *.element file. The output of the script is <element>.element.stage2 and <element>.element.stat for each .element file. The format of the *.stat files is <element>, <metric>, <instance>, <min>, =<max>, <mean>, <standard dev>, <sample size>.

key_—codes.pl

[0267] The key_—codespl script uses perf_—metric_—tb.ext and network_—element_—tb.ext to look up the codes associated with the node or element being monitored. It assigns the key code to the name of the file in place of the element name (i.e. <\vcode>.element.stage2 and <keycode>.element.stat). It also produces perf_—metric_—time_—tb.dat, which is a list of all of the unique date-time stamps appearing in the raw data and their corresponding epoch times (the number of seconds since Jan. 1, 1970)

sort_—elmnt_—files.pl

[0268] The sort_—elmnt_—files.pl sorts all of the <keycode>.element.stage2 and <Keycode>.element.stat files and renames them <keycode>.element.stage2.sorted and <keycode>.element.stat.sorted.

union_—all.pl

[0269] The union_—all.pl script takes the <keycode>.element.stage2.sorted files and concatenates them into perf_—fact_—tb.dat and the <keycode>.element.stat.sorted files and concatenates them into perf_—fact_—dly_—tb.dat.

move_—all.pl

[0270] The move_—all.pl script moves perf_—fact_—tb.dat, perf_—fact_—dly_—tb.dat and perf_—metric_—time_—tb.dat to /files6/ipsa/data_—loads/data iles.

Data Manipulator Sample Raw Data

Patroller Data

File 1: nsmmpe29_—1999_—902194516.dat

[0271]

,,nsmmpe29,HISTORY—Propagator,HISTORY—Propagator,GetHistoryData, 1999-
08-03 19:35:04,0
,,nsmmpe29,NT—CPU,CPU—0,CPUprcrProcessorTimePercent, 1999-08-03
19:42:51,1.83103
,,nsmmpe29,NT—CPU,CPU—0,CPUprcrProcessorTimePercent, 1999-08-03
19:57:51,2.2381
,,nsmmpe29,NT—CPU,CPU—0,CPUprcrProcessorTimePercent, 1999-08-03
20:12:52,3.98922
,,nsmmpe29,NT—CPU,CPU—0,CPUprcrProcessorTimePercent, 1999-08-03
20:27:52,1.52942
,,nsmmpe29,NT—MEMORY,NT—MEMORY,MEMmemAvailableBytes, 1999-08-03
19:34:41,4.14453
,,nsmmpe29,NT—MEMORY,NT—MEMORY,MEMmemAvailableBytes, 1999-08-03
19:49:41,2.4375
,,nsmmpe29,NT—MEMORY,NT—MEMORY,MEMmemAvailableBytes, 1999-08-03
20:04:41,1.85156
,,nsmmpe29,NT—MEMORY,NT—MEMORY,MEMmemAvailableBytes, 1999-08-03
20:19:41,1.19531
,,nsmmpe29,NT—MEMORY,NT—MEMORY,MEMmemAvailableBytes, 1999-08-03
20:34:41,1.14844
,,nsmmpe29,NT—NETWORK,2,NETniPcktsPerSec, 1999-08-03 19:46:55,10.9169
,,nsmmpe29,NT—NETWORK,2,NETniPcktsPerSec, 1999-08-03 20:01:55,11.5108
,,nsmmpe29,NT—NETWORK,2,NETniPcktsPerSec, 1999-08-03 20:16:55,11.514
,,nsmmpe29,NT—NETWORK,2,NETniPcktsPerSec, 1999-08-03 20:31:55,12.0128

File 2: nsmmws09_—1999_—901881775.dat

• [0272] ,,nsmmws09,CPU,CPU,CPUCpuUtil,1999-07-31 04:46:42,4
• [0273] ,,nsmmws09,CPU,CPU,CPUCpuUtil,1999-07-31 05:01:47,2
• [0274] ,,nsmmws09,CPU,CPU,CPUCpuUtil,1999-07-31 05:16:52,3
• [0275] ,,nsmmws09,CPU,CPU,CPUCpuUtil,1999-07-31 05:31:57,2
• [0276] ,,nsmmws09,DISK,sd4,DSKPercentBusy,1999-07-31 04:42:14,0
• [0277] ,,nsmmws09,DISK,sd4,DSKPercentBusy,1999-07-31 04:57:14,4
• [0278] ,,nsmmws09,DISK,sd4,DSKPercentBusy,1999-07-31 05:12:15,0
• [0279] ,,nsmmws09,DISK,sd4,DSKPercentBusy,1999-07-31 05:27:15,0
• [0280] ,,nsmmws09,MEMORY,MEMORY,MEMFreeMem, 1999-07-31 04:46:42,37184
• [0281] ,,nsmmws09,MEMORY,MEMORY,MEMFreeMem, 1999-07-31 05:01:47,37172
• [0282] ,,nsmmws09,MEMORY,MEMORY,MEMFreeMem, 1999-07-31 05:16:52,37168
• [0283] ,,nsmmws09,MEMORY,MEMORY,MEMFreeMem,1999-07-31 05:31:57,37168
• [0284] ,,nsmmws09,NETWORK,1e0,NETPacketsln,1999-07-31 04:43:05,6660
• [0285] ,,nsmmws09,NETWORK,1e0,NETPacketsIn,1999-07-31 04:58:06,6423
• [0286] ,,nsmmws09,NETWORK,1e0,NETPacketsIn,1999-07-3 1 05:13:06,6822
• [0287] ,,nsmmws09,NETWORK,1e0,NETPacketsIn,1999-07-31 05:28:07,6285
• [0288] ,,nsmmws09,NETWORK,1e0,NETPacketsOut, 1999-07-31 04:43:05,168
• [0289] ,,nsmmws09,NETWORK,1e0,NETPacketsOut, 1999-07-31 04:58:06,118
• [0290] ,,nsmmws09,NETWORK,1e0,NETPacketsOut, 1999-07-31 05:13:06,199
• [0291] ,,nsmmws09,NETWORK,1e0,NETPacketsOut,1999-07-31 05:28:07,118
  File 3:twmmt02_—1999_—901997338.dat
• [0292] ,,twmmnt02,HISTORYPropagator,HISTORY_—Propagator,GetHistoryData, 1 999-08-01 12:48:56,0
• [0293] ,,twmmnt02,NT_—CPU,CPU_—0,CPUprcrProcessorTimePercent,1999-08-01 13:02:42,0.835579
• [0294] ,,twmmnt02,NT_—CPU,CPU_—0,CPUprcrProcessorTimePercent, 1999-08-01 13:17:42,0.776552
• [0295] ,,twmmnt02,NT_—CPU,CPU_—0,CPUprcrProcessorTimePercent, 1999-08-01 13:32:42,0.795792
• [0296] ,,twmmnt02,NT_—CPU,CPU_—0,CPUprcrProcessorTimePercent,1999-08-01 13:47:42,0.786594
• [0297] ,,twmmnt02,NT_—MEMORY,NT_—MEMORY,MEMmemAvailableBytes,1999-08-01 13:02:49,21.4688
• [0298] ,,twmmnt02,NT_—MEMORY,NT_—MEMORY,MEMmemAvailableBytes,1999-08-01 13:17:49,21.5703
• [0299] ,,twmmntO2,NT_—MEMORY,NT_—MEMORY,MEMmemAvailableBytes,1999-08-01 13:32:49,16.7617
• [0300] ,,twmmt02,NT_—MEMORY,NT_—MEMORY,MEMmemAvailableBytes,1999-08-01 13:47:49,16.8359
• [0301] ,,twmmnt02,NT_—NETWORK,2,NETniPcktsPerSec,1999-08-01 13:02:40,14.9904
• [0302] ,,twmmnt02,NT_—NETWORK,2,NETniPcktsPerSec,1999-08-01 13:17:40,15.0483
• [0303] ,,twmmnt02,NT_—NETWORK,2,NETniPcktsPerSec,1999-08-01 13:32:40,14.8512
• [0304] ,,twmmnt02,NT_—NETWORK,2,NETniPcktsPerSec,1999-08-01 13:47:40,15.0225
• [0305] ,,twmmnt02,NT_—PHYSICAL_—DISKS,DISK_—0,PDpdDiskTimePercent,1999-08-01 13:02:40,0
• [0306] ,,twmmnt02,NT_—PHYSICAL_—DISKS,DISK_—0,PDpdDiskTimePercent, 1999-08-01 13:17:40,0
• [0307] ,,twmmnt02,NT_—PHYSICAL_—DISKS,DISK_—0,PDpdDiskTimePercent, 1999-08-01 13:32:40,0
• [0308] ,,twmmnt02,NT_—PHYSICAL_—DISKS,DISK_—0,PDpdDiskTimePercent,1999-08-01 13:47:40,0
  Event Correlation and Manager Data
  File 1: ifdata.log
• [0309] Time=Aug. 11, 1999 09:39:27 Tue, LogId=22248, Severity-Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt04e2-1, Alarm=IfDataLogger, Ostate=Ground, Trigger=if Data, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=26, Object=if Entry, Attribute if Speed=100000000 Attribute ifInOctets=464859949 Attribute if OutOctets=312935185
• [0310] Time=Aug. 11, 1999 09:39:27 Tue, LogId=22249, Severity-Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt04e2-1, Alarm=IfDataLogger, Ostate=Ground, Trigger=if Data, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=27, Object=if Entry, Attribute ifspeed=100000000 Attribute ifInOctets=0 Attribute if OutOctets=0
• [0311] Time=Aug. 11, 1999 09:39:27 Tue, LogId=22250, Severity-Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt04e2-1, Alarm=IfDataLogger, Ostate=Ground, Trigger=if Data, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=28, Object=ifEntry, Attribute if Speed=100000000 Attribute ifInOctets=0 Attribute if OutOctets=0
• [0312] Time=Aug. 11, 1999 09:39:27 Tue, LogId=22251, Severity-Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt04e2-1, Alarm=IfataLogger, Ostate=Ground, Trigger\-if Data, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=29, Object=ifEntry, Attribute if Speed=3705032704 Attribute ifInOctets=0 Attribute ifoutOctets=0
• [0313] Time=Aug. 11, 1999 09:39:27 Tue, LogId=22252, Severity-Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt03, Alarm=IfDataLogger, Ostate=Ground, Trigger=ifDfata, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=1, Object=ifEntry, Attribute if Speed=10000000 Attribute ifInOctets=1522731264 Attribute if OutOctets=2554292181
• [0314] Time=Aug. 11, 1999 09:39:27 Tue, LogId=22253, Severity-Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt03, Alarm=IfDataLogger, Ostate=Ground, Trigger=ifData, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=2, Object=ifEntry, Attribute if Speed=10000000 Attribute ifInOctets=0 Attribute ifoutOctets=0
• [0315] Time=Aug. 11, 1999 09:39:27 Tue, LogId=22254, Severity-Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt03, Alarm=IfDataLogger, Ostate=Ground, Trigger=ifData, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=3, Object=ifEntry, Attribute if Speed=10000000 Attribute ifInOctets=0 Attribute if OutOctets=0
• [0316] Time=Aug. 11, 1999 09:39:27 Tue, LogId=22255, Severity=Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt03, Alarm=IfDataLogger, Ostate=Ground, Trigger=if Data, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=4, Object=ifEntry, Attribute if Speed=10000000 Attribute if InOctets=0 Attribute if OutOctets=0
• [0317] Time=Aug. 11, 1999 09:39:27 Tue, LogId=22256, Severity_—Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt03, Alarm=IfDataLogger, Ostate=Ground, Trigger=ifData, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=5, Object=if Entry, Attribute ifSpeed=10000000 Attribute ifInOctets=958056 Attribute if OutOctets=3610555
  File 2: lsystemdata.log
• [0318] Time=Aug. 11, 1999 09:18:26 Tue, LogId=22153, Severity=Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt04e2-1, Alarm=lsystemDataLogger, Ostate=Ground, Trigger=lsystemData, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=0, Object=lsystem, Attribute busyPer=4
• [0319] Time=Aug. 11, 1999 09:18:28 Tue, LogId=22154, Severity=Normal, PropertyGroup=SA\_—Router_—Collection_—Group, Node=nsmmrt03, Alarm =lsystemDataLogger, Ostate=Ground, Trigger=lsystemData, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=0, Object=lsystem, Attribute busyPer=4
• [0320] Time=Aug. 11, 1999 09:33:38 Tue, LogId=22219, Severity=Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt04e2-1, Alarm=lsystemDataLogger, Ostate=Ground, Trigger=lsystemData, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=0, Object=lsystem, Attribute busyPer=2
• [0321] Time=Aug. 11, 1999 09:33:40 Tue, LogId=22220, Severity=Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node”=nsmmrt03, Alarm=lsystemDataLogger, Ostate=Ground, Trigger=lsystemData, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=0, Object=lsystem, Attribute busyPer=7
• [0322] Time=Aug. 11, 1999 09:48:43 Tue, LogId=22283, Severity=Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt04e2-1, Alarm=lsystemDataLogger, Ostate=Ground, Trigger=lsystemData, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=0, Object=lsystem, Attribute busyPer=2
• [0323] Time=Aug. 11, 1999 09:48:45 Tue, LogId=22284, Severity=Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt03, Alarm=lsystemDataLogger, Ostate=Ground, Trigger=lsystemData, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=0, Object=lsystem, Attribute busyPer=10
• [0324] Time=Aug. 11, 1999 10:03:49 Tue, LogId=22347, Severity=Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt04e2-1, Alarm=lsystemDataLogger, Ostate=Ground, Trigger=lsystemData, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=0, Object=lsystem, Attribute busyPer=4
• [0325] Time=Aug. 11, 1999 10:03:51 Tue, LogId=22348, Severity=Normal, PropertyGroup =SA_—Router_—Collection_—Group, Node=nsmmrt03, Alarm=lsystemDataLogger, Ostate=Ground, Trigger=lsystemData, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=0, Object=lsystem, Attribute busyPer=6
• [0326] Time=Aug. 11, 1999 10:18:56 Tue, LogId=2241 7, Severity=Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt04e2-1, Alarm=lsystemDataLogger, Ostate=Ground, Trigger=lsystemData, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=0, Object=lsystem, Attribute busyPer=3
• [0327] Time=Aug. 11, 1999 10:18:58 Tue, LogId=22418, Severity=Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt03, Alarm=lsystemDataLogger, Ostate=Ground, Trigger=lsystemData, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=0, Object=lsystem, Attribute busyPer=3
  Desired Metrics and their Standard or Derive Metrics

[0328]

TABLE 1
		Standard or Derived
SNMP metrics	Patroller metrics	Metric
busyPer	n/a	CpuBusy—Percent
ifInOctects,	n/a	InterfaceUtilization—Percent
ifOutOctets,
ifSpeed
ifInOctets,	n/a	InterfaceUtilization—BPS
ifOutOctets,
ifSpeed
n/a	CPUCpuUtil	CpuUtilization—Percent
n/a	MEMFreeMem	MemoryFree—Bytes
n/a	NETPacketsIn,	NetworkTraffic—PPS
	NETPacketsOut
n/a	DSKPercentBusy	DiskBusy—Percent
n/a	CPUprcrProcessorTime	CpuProcessorTime—Percent
	Percent
n/a	MEMmemAvailable	MemoryFree—Bytes
	Bytes
n/a	NETniPcktsPerSec	NetworkTraffic—PPS
n/a	PDpdDiskTimePercent	DiskTime—Percent
n/a	ftpResponseTime	FtpResponseTime—Seconds
n/a	smtpResponseTime	SmtpResponseTime—Seconds
n/a	nntpResponseTime	NntpResponseTime—Seconds
n/a	httpResponseTime	HttpResponseTime—Seconds

Data Loader

[0329] The purpose of the data loader in the SA toolkit is to perform recommended operations on the gathered event data before it is loaded into the database. Primarily, the raw data files need to be concatenated, reformatted, translated to key codes, and sorted. In addition, the data loader will also perform statistical calculations on the data for daily “roll-up” reports to be loaded into the database.

Body Section

Patroller Data

[0330] Performance data from Patroller collectors is in the following text log file format:

• [0331] ,,<element name>,<object type>,<instance>,<metric name>,<date time>,<metric value>
• [0332] a sample file (found in nsmmws16:/opt/PATROLLER3.2/Solaris25-Sun4/remote/):
• [0333] ,,nsmmws09,CPU,CPU,CPUCpuUtil, 1999-07-30 14:58:13,25
• [0334] ,,nsmmws09,CPU,CPU,CPUCpuUtil,1999-07-30 15:13:18,52
• [0335] ,,nsmmws09,CPU,CPU,CPUCpuUtil,1999-07-30 15:28:24,32
• [0336] ,,nsmmws09,DISK,fd0,DSKPercentBusy,1999-07-30 15:03:15,0
• [0337] ,,nsmmws09,DISK,sd4,DSKPercentBusy,1999-07-30 15:03:15,0
• [0338] ,,nsmmws09,DISK,sd4,DSKPercentBusy,1999-07-30 15:18:15,4
• [0339] ,,nsmmws09,DISK,sd4,DSKPercentBusy,1999-07-30 15:33:15,0
• [0340] ,,nsmmws09,MEMORY,MEMORY,MEMFreeMem,1999-07-30 14:58:13,24008
• [0341] ,,nsmmws09,MEMORY,MEMORY,MEMFreeMem,1999-07-30 15:13:18,23548
• [0342] ,,nsmmws09,MEMORY,MEMORY,MEMFreeMem,1999-07-30 15:28:24,23544

[0343] After parsing and reordering, the format is:

• [0344] <element>, <metric name>, <instancename>, <date time>, <metric value>

[0345] Finally, the element name and metric name must be translated to their corresponding key codes and the date time must be converted to the format consistent with the database:

• [0346] 16838, 236998718,6293,54

[0347] Event Correlator and Manager (ECM) Data

[0348] Performance data from ECM collectors is in the following text log file format: (note: fields in italics are do not have values for poll data):

• [0349] Time=<datetime>, LogId=<LogID>, Severity=<Severity>, PropertyGroup=<PropertyGroup>, Node=<Node>, Alarm=<Alarm>, Ostate=<Ostate>, Trigger=<Trigger>, Nstate=<Nstate>, TrapTime=<TrapTime>, GenericTrapNumber=<GenericTrapNumber>, Enterprise=<Enterprise>, SpecificTrapNumber=<SpecificTrapNumber>, Instance=<, [instance value]>, Object=<[base_—object_—name]>, Attribue <[attributename]>=value . . . .
• [0350] a sample file (found in/opt/seasoft/userfiles/logs/ifdata.log)
• [0351] Time=Aug. 11, 1999 09:39:27 Tue, LogId=22248, Severity=Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt04e2-1, Alarm=IfDataLogger, Ostate=Ground, Trigger=idData, Nstate=Ground, TrapTime=GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=26, Object=ifEntry, Attribute if Speed=100000000 Attribute ifInOctets=464859949 Attribute if OutOctets=312935185
• [0352] Time=Aug. 11, 1999 09:39:27 Tue, LogId=22249, Severity-Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt04e2-1, Alarm=IfDataLogger, Ostate=Ground, Trigger=ifData, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=27, Object=ifEntry, Attribute if Speed=1000\900000 Attribute ifInOctets=0 Attribute if OutOctets=0
• [0353] Time=Aug. 11, 1999 09:39:27 Tue, LogId=22250, Severity_—Normal, PropertyGroup=SA_—Router_—Collection_—Group, Node=nsmmrt04e2-1, Alarm=IfDataLogger, Ostate=Ground, Trigger=if Data, Nstate=Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=Instance=28, Object=ifEntry, Attribute if Speed=100000000 Attribute if InOctets=0 Attribute if OutOctets=0
• [0354] Time=Aug. 11, 1999 09:39:27 Tue, LogId=22251, Severity=Normal, PropertyGroup=SA_—Router Collection_—Group, Node=nsmmrt04e2-1, Alarm—IfDataLogger, Ostate=Ground, Trigger=ifData, Nstate—Ground, TrapTime=, GenericTrapNumber=, Enterprise=, SpecificTrapNumber=,Instance=29, Object=ifEntry, Attribute if Speed=3705032704 Attribute ifInOctets=0 Attribute if OutOctets=0

[0355] After parsing and reordering, the format is:

• [0356] <element>, <metric name>, <instance name>, <datetime>, <metric value>

[0357] Finally, the element name and metric name must be translated to their corresponding key codes and the date time must be converted to the format consistent with the database:

• [0358] 16838, 236998718,6293,54
  High Level Architecture Description

[0359] FIG. 9 illustrates an exemplary configuration of an architecture 900 of the present invention for manipulating data using a Patroller and an ECM.

Load Config File 902

• [0360] Inputs: Configuration file.
• [0361] Description: This subroutine will open the configuration file, read in the required values to three arrays and close the configuration file.
• [0362] Outputs: Three arrays: @SectionNames, @SectionMarker, @SectionValues. @SectionNames will contain all of the section titles found in the Config file, (SECTION_—GENERAL, SECTION_—CONCATENATE_—FILES, SECTION_—REORDER_—FIELDS . . . ); @SectionValues will contain each line from the config file (that isn't a section header, a blank line, or a comment) as a single element; @SectionMarker will contain the index number of the last line of each section. This way, you can pull out just the elements of the @SectionValues array that you need for a given functional block.
  Concatenate All Files 904
• [0363] Inputs: All log files in the directory sa/prd/dat/process/ECM and sa/prd/dat/process/Patroller. ECM data will have a .nrc extension, Patroller data will have a .pat extension.
• [0364] Description: This subroutine will concatenate all files from a given source into one master file. For each file in the directory (/ECM and/Patroller), it will read in each line, and:
  • [0365] Concatenate to master (actually done last) 906
  • [0366] Filter to only include desired metrics (delete line if it isn't listed as a desired metric) 908
  • [0367] Reorder and drop fields (split on “,” and put in proper order: <element>, <metric>, <time>, value). 910
  • [0368] Format non-standard fields into standard comma separated format. For each element file with a nonstandard field format (such as ECM's dual value fields with a metric=value format), this subroutine will convert these nonstandard fields to standard fields. 912
  • [0369] Outputs: The files master.nrc and master.pat.
    Generate Element Files 914
• [0370] Inputs: Master.nrc and master.pat. (Proper order, comma separated, all text).
• [0371] Description: For each master file, this subroutine will read each line, create an element file for each unique element, and write the line to the appropriate file.
• [0372] Outputs: The files element1.elemnrc, element2.elemnrc, . . . (for all elements found in master.nrc) and the files element1.elempat, element3.elempat (for all elements found in master.pat).
  Convert Date-Time Stamps to Standard 916
• [0373] Inputs: Element files. (*.elemnrc, *.elempat, proper order, comma separated, all text)
• [0374] Description:. Convert the current date time stamps to a standard format (e.g. the 1999-07-30 15:42:13 format found in Patroller files would be converted to . . . )
• [0375] Outputs: Element files.
  Convert to Standard Metric Names 918
• [0376] Inputs: Element files. (*.elemnrc, *.elempat, proper order, comma separated, all text).
• [0377] Description: For each element file, this function will lookup each metric name in a hash or flatfile. If found, it will convert it to standard. Else, it's already standard.
• [0378] Outputs: (Expanded) Element files.
  Sort Element files by Metric Time 920
• [0379] Inputs: Element files (*.elemnrc, *.elempat, proper order, comma separated, all text).
• [0380] Description: This subroutine will perform and ordered sort on each element file, sorting first by metric, then by time stamp.
• [0381] Outputs: Element files.
  Calculate Derived Fields 922
• [0382] Inputs: Element files. (*.elemnrc, *.elempat, proper order, comma separated, all text, sorted by metric and timestamp).
• [0383] Description: For each element file, this subroutine will read each line and search for all metrics (at a given time) required to calculate derived metrics, calculate the given derived metric, create a new line for this derived metric, and append this line to the end of the file.
• [0384] Outputs: Element files.
  Calculate Statistical Data and create Rollup Files 924
• [0385] Inputs: Element files. (*.elemnrc, *.elempat, proper order, comma separated, all text, sorted by metric and timestamp, derived metrics at end of file).
• [0386] Description: For each element file, this subroutine will
• [0387] 1) read each line for a given metric,
• [0388] 2) store each value into a temp array,
• [0389] 3) calculate min, max, mean, standard dev., and sample size,
• [0390] 4) create a new line (<element>, <metric>, <time>, <min>, <max>, <mean>, <stddev>, <n>),
• [0391] 5) write this line to a newly created rollup file and
• [0392] 6) repeat for the next metric.
• [0393] Outputs: Original element files (unchanged), rollup files stats.elemdailypat, stats.elemdailynrc
  Convert all Text Fields to Key Codes 926
• [0394] Inputs: Element files (*.elemnrc, *.elempat, proper order, comma separated, all text, sorted by metric and timestamp, derived metrics at end of file), and rollup files.
• [0395] Description: For each file, this subroutine will read each line, convert each text field to a key code (according to a lookup table in a hash or file, except for the time field, which will be calculated), and write the line to a temp file. When all lines have been converted, the source file will be deleted, and the temp file will be moved to the source file.

[0396] For each file and for each unique time stamp, create a new line with the new calculated time key cd (<perf time_—key_—cd>, <dt>, <day>, <mon>, <yr>, <hour>, <min>, <sec>, <dow>) and write to a file perf_—metric_—time_—tb.dat file. (This file then should be collapsed to remove duplicate time stamps from different elements). 928

• [0397] Outputs: (Encoded) element files and rollup files.
  Union All Files of Same Element 930
• [0398] Inputs: Element files (*.elemnrc, *.elempat, proper order, comma separated, all key codes, sorted by metric and timestamp, derived metrics at end of file), and rollup files (all key codes)
• [0399] Description: This subroutine will concatenate all files of the same element, regardless of collector source (*.pat, *.nrc, . . . ) into a *.elemall files.
• [0400] Outputs: All-Element files: element1.elemall, element2.elemall,
  Sort Each Element File 932
• [0401] Inputs: All-Element files (see A. 10)
• [0402] Description: This subroutine will sort each file first by metric, then by time stamp.
• [0403] Outputs: All-Element files
  Union All Files 934
• [0404] Inputs: All-Element files, Rollup files (see A.10)
• [0405] Description: This subroutine will concatenate all *elemall files into perf_—fact_—tb.dat, and all rollup files into perf_—fact_—daily_—tb.dat.
• [0406] Outputs: Database load files: perf_—fact_—tb.dat, perf_—fact_—daily_—tb.dat
  Move Files 936
• [0407] Inputs: A files ready to be archived or moved to sql loader (perf_—fact_—tb.dat, perf_—fact_—daily_—tb.dat, perf_—metric_—time_—tb.dat)
• [0408] Description: This subroutine copies and/or moves files to any arbitrary location on a local drive.
• [0409] Outputs: Copies and/or moves files to /files6/ipsa/data_—loads/data_—files/ on twmmdb02.

[0410]

Metric Names
SNMP
metrics	Patroller metrics	Standard or Derived Metric
busyPer	n/a	CpuBusy—Percent
ifInOctects,	n/a	InterfaceUtilization—Percent
ifOutOctets,
ifSpeed
ifInOctets,	n/a	InterfaceUtilization—BPS
ifOutOctets,
ifSpeed
n/a	CPUCpuUtil	CpuUtilization—Percent
n/a	MEMFreeMem	MemoryFree—Bytes
n/a	NETPacketsIn,	NetworkTraffic—PPS
	NETPacketsOut
n/a	DSKPercentBusy	DiskBusy—Percent
n/a	CPUprcrProcessorTime	CpuProcessorTime—Percent
	Percent
n/a	MEMmemAvailableBytes	MemoryFree—Bytes
n/a	NETniPcktsPerSec	NetworkTraffic—PPS
n/a	PDpdDiskTimePercent	DiskTime—Percent
n/a	ftpResponseTime	FtpResponseTime—Seconds
n/a	smtpResponseTime	SmtpResponseTime—Seconds
n/a	nntpResponseTime	NntpResponseTime—Seconds
n/a	httpResponseTime	HttpResponseTime—Seconds

Configuration of Event Correlator and Manager

[0411] This section describes how to configure the Event Correlator and Manager (UNIX) to collect performance data. It covers the process of adding property groups, creating polls, and building alarms to collect basic performance statistics from CISCO routers. Although the examples are specific to CISCO routers, the concepts and processes introduced are transitive, and can be used to implement collection for other types of SNMP devices.

[0412] Accordingly, FIG. 10 depicts an embodiment of the present invention which generates an alarm to indicate a status of a network for service assurance purposes. Such purposes can include identifying errors and faults, monitoring system resources, anticipating problems, etc. Once a parameter of a network that is to be monitored is selected in operation 1000, a triggering level of the parameter is determined in operation 1002. In operation 1004, the parameter for an occurrence of the triggering level is monitored. If the triggering level is reached, an alarm is initiated in operation 1006.

[0413] In one aspect of the present invention, the alarm is named for identification purposes. Optionally, the parameter can be adapted to be monitored for a plurality of components of the network. In such an embodiment, a separate alarm notification may be initiated for each of the components upon the monitored occurrence of the triggering level thereon.

[0414] As an option, the alarm notification may be stored upon its initiation. Preferably, the alarm notification is stored until the occurrence of a user action.

Assumptions:

• • [0415] The ECM MIB definition for your network hardware has been added to the MIB file.
    Starting the Client
  • [0416] Start the ECM Client as ‘no’, if it is not already running.
  • [0417] Using the Server/Connect menu selection, connect to the network management station as (i.e. nsmmwsl6) as ‘noc’.
  • [0418] Using the Server/Resync menu selection, resynchronize the ECM database with the platform database containing managed nodes (i.e. HP NNM).
    Create a new ‘Property Group’

[0419] Property groups allow categorization of managed devices into groups. The groups can be based on device types, location, priority, supported MIBs, business function, or any other useful characteristic. Polls or alarms can be restricted to the members of one or more property groups.

• • [0420] Select the Admin/Property Group List menu option to open the Property Group List.
  • [0421] Select the MIB to Group button to open the MIB to Property Group window.
  • [0422] Select the MIB of interest from those listed in the MIB list. For our example, select MIB-II.
  • [0423] Type a unique name in the Property Group Name field, and then select OK. For this example, enter “SA_—Router_—Collection_—Group”.
    Adding a Property to a Property Group

[0424] Adding properties to an existing property group enables one to take advantage of the flexibility of property groups. Properties may need to be added if it is desired to:

• • [0425] Make a property group unique by adding a property that isn't in any other group. By assigning the unique property group to selected devices, you can manage those devices differently from other devices.
  • [0426] Make polls and/or alarms apply to devices belonging to that property group.
  • [0427] Monitor a base object on a group of devices when the base object isn't listed as a property in the devices' property group.

[0428] For the purposes of this example, the “lsystem” base object property will be added, defined in the cscoe.asnl MIB definition; as well as a custom property to make the property group unique.

• • [0429] From within the Property Group List window, select SA_—Router_—Collection_—Group from the list of groups
  • [0430] Click inside the New Property field, type the name of the new property, and select Add. For our example, type “SA_—Router_—Collection_—Property”.
  • [0431] Repeat the previous step to add the “lsystem” property.
  • [0432] Select Save
  • [0433] Close the Property Group List window
    Assigning Property Groups

[0434] In the following example, SA_—Router_—Collection_—Group, the property groups created in the previous section, will be assigned to a single router.

• • [0435] Open the Node Definition window for the device.
  • [0436] Select the property group you would like associated with this device from the pop-up list in the Group field.
  • [0437] Select Save, and close the Node Definition window.
    Create a new ‘Poll’
  • [0438] Open the ‘Polls’ list from the Patroller console using the Admin/Polls . . . menu selection.
  • [0439] Select ‘Add’ from the Polls window.
  • [0440] Configure the poll
    • [0441] Leave ‘Object Type’ as Poll
    • [0442] Change ‘Name’ to a new name
    • [0443] Set the desired poll rate.
    • [0444] Type a new name for the ‘Poll Trigger’. Use a name similar to the name you gave to this poll.
    • [0445] Leave False Trigger blank
    • [0446] Leave Property as ‘NO_—PROP’
    • [0447] Select ‘Set Poll Condition’
• [0448] (a) Select the ‘Base Object’ from which you want to collect data. This base object directly correlates to the base object of the SNMP MIB.
• [0449] (b) Select the desired ‘Attributes’ from the base object and drag them into the ‘Expression’ box using the middle mouse button. For each Attribute selected, change the ‘Rel Op’ field to ‘present’ and click the ‘Add’ button. When all attributes have been selected, click the ‘Apply’ button.

Poll lsystemDataPoll

• • [0450] Poll rate=15 min
  • [0451] True Trigger=lsystemDataTrigger
  • [0452] Property=SA_—Router_—Collection_—Property
  • [0453] Poll condition:
  • [0454] Base object=lsystem
  • [0455] lsystem.avgBusy1 present and
  • [0456] lsystem.avgBusy5 present and
  • [0457] lsystem.busyPer present
    Create a new “Alarm”
  • [0458] Open the ‘Alarm Definitions’ list from the Patroller console using ‘Admin->Alarm Definitions . . . ’ selection.
  • [0459] Select ‘Add’ from the Alarm window.
  • [0460] Configure the new Alarm.
  • [0461] Select a new name for the Logging alarm.
  • [0462] Select the property of the device group you want to collect data from.
  • [0463] Select the ‘State Diagram’ button.
  • [0464] Using the ‘New State’ button, create 2 states called ‘Ground’ and ‘Logging’. The ‘Severity’ of both states should be set to ‘normal’.
  • [0465] Using the ‘New Transition’ button, create a transition from ‘Ground’ to ‘Logging’. The trigger for this transition should be the name of the ‘Poll Trigger’ you defined in section 3.1.4. Select ‘Apply’. Under ‘Action’ select ‘Log’ and type the name of the log file where you want the data stored in the ‘Arguments’ field. Note: use the name of the trigger with a ‘.log’ extension for the log name. Do not enter a path name as all data is stored in the pre-defined ECM logging directory.
  • [0466] Create the same transition from ‘Logging’ to ‘Logging’.
  • [0467] In the ‘Alarm Configuration View’ select ‘On’ and ‘Apply’.
    Note/Caution:

[0468] When designing a logging alarm based on two states (i.e. ground and logging), be aware that a new 5K instance of the alarm may be instantiated and remain in memory for each object within the defined scope. For example, a subobject scoped alarm with two states, ground and logging, created to collect interfaced data from a router would be instantiated once for each interface on all routers in the target property group. Because this alarm contains two states, ground and logging, the alarm remains in memory until manually stopped as it never returns to the ground state. One method of avoiding this unnecessary overhead is to create the logging alarm with only one state, ground, and a single transition from ground to ground.

[0469] Design is Ground to Ground, with a single transition based on the ‘lsystemDataTrigger’ defined in the example poll above.

Alarm=lsystemDataLogger

• • [0470] Property=SA_—Router_—Collection_—Property
  • [0471] Scope=node
  • [0472] State List:
  • [0473] Ground—Normal
  • [0474] Transition List:
  • [0475] Ground→Ground Trigger=if DataTrigger
  • [0476] Action=Log to file:
  • [0477] Ifdata.log
  • [0478] Verbose enabled
    Update the Nerve Center Schema Files

[0479] The ECM schema file(s) (e.g. ecm_—schema.def) are used by the SA data management subsystem to define and upload data into SAS datasets.

• • [0480] Edit the file ‘/perf/CONFIGS/NERVEC_—schema.def’ Add to the end of this file a section for the new data you are collecting. The section should start with a single line to define the new ‘TABLE’ name, multiple lines for the attributes being collected, and a single line indicating the end of the table definition with the label ‘ENDTABLE’.
  • [0481] Add the ‘TABLE’ line. This line has 5 fields separated by commas.
    Field 1—‘TABLE’ label
    Field 2—Name of new SAS dataset. This field should be no longer than 6 characters.
    Field 3—Name of the ‘Poll Trigger’.
    Field 4—Name of the Alarm.
    Field 5—INDEXED or NONINDEXED. Type INDEXED if this is to be and INDEXED dataset. An INDEXED dataset contains data with multiple instances for a single device. For example, collecting router interface data for each interface on a router would be INDEXED, while collecting CPU data from a single CPU on a router would be NONIDEXED.

TABLE,NCSWRMON,RMONData,NC_—RMONDataLogger,INDEXED

• • [0482] Add a line for each attributed collected. These lines have 5 fields separated by commas.
    Field 1—Name of the SAS Dataset defined in the ‘TABLE’ line.
    Field 2—New name of the attribute. This name must be 7 characters or less.
    Field 3—Name of the ‘Poll Trigger’.
    Field 4—Name of the Attribute being collected by ECM.
    Field 5—Type of data. (STRING, C2RATE, GAUGE, INT)
    STRING: Alphanumeric string.
    C2RATE: If the data is a rising counter, the label will tell SAS to convert it to a rate for the interval in which is was collected.
    GAUGE: If the data is already a rate or a percentage. SAS will do no conversion.
    INT: If the data is an integer (Ex: Interface number)

NCSWRMON,INBITS,RMONData,etherStatsDataSource,STRING

• • [0483] Add a line to signify the end of the configuration section. This line has 3 fields separated by commas.
    Field 1—‘ENDTABLE’ label
    Field 2—Name of new SAS dataset.
    Field 3—Name of the ECM Alarm.

ENDTABLE,NCSWRMON,NC_—RMONDataLogger

[0484] One embodiment of the present invention, shown in FIG. 11, is used to develop a database. Data is extracted from a source system with an application program in operation 1100. The data is translated into a consistent model in operation 1102. The model contains information selected from one or more of performance metric information, time data, geographic location data, and event data. The modeled data is stored in a database in operation 1104. The data is analyzed in operation 1106 based on the information and the results of the analysis are output. It should be noted that the data may be analyzed before or after storing.

[0485] In one aspect of the present invention, the data is extracted using a plurality of application programs. Further, the information may include performance metric information, time data, geographic location data, and event data.

[0486] In yet another aspect of the present invention, the results may be outputted by way of reports and charts. Further, identifiers may be assigned to groups of the data. The data can also be stored in a plurality of databases including a first database for development purposes, a second database for testing purposes, and a third database for production purposes.

[0487] Refer to the following sections for more detailed information and additional embodiments.

Database Design

[0488] The database for the Service Assurance Toolkit can be designed as a data warehouse. This design offers greater performance and flexibility, which will enable the database to evolve in future releases of the Service Assurance Toolkit. The architecture for a successful data warehouse, by which we mean the end-to-end tools and processes, will vary from implementation to implementation. A typical data warehousing architecture should include, as a minimum:

• • [0489] Multiple extract programs from one or more operational systems, to retrieve the source data for the warehouse.
  • [0490] A data repository (“Operational Data Store”) containing the extracted data in an appropriate model.
  • [0491] A tool to analyze and display the data as reports and charts.

[0492] The design of the SA database should also be independent of any relational DBMS. By doing this the SA toolkit will be more portable across platforms and will allow us to better meet client needs. Also, the design of the database may contain all necessary information to meet the requirements laid out in the reporting requirement document. For the SA toolkit, the data model should contain information such as:

• • [0493] Network element information.
  • [0494] Performance Metric information.
  • [0495] Detailed time data
  • [0496] Network element geographic location data
  • [0497] Network element event data

[0498] The design of the database can utilize surrogate keys to uniquely identify records. This will be done to increase query performance as well as to add flexibility to the database design. However, this adds complexity to the data manipulation because look-ups on the surrogate keys will need to take place.

Physical Database

[0499] The SA toolkit may be built on the Oracle RDBMS platform. While this is a specific platform, the physical design of the database can be independent of Oracle and portable to the major players in the RDBMS world i.e. Sybase, Informix, DB2, Microsoft, etc.

Development Environment

[0500] The database environment will have one database for the entire development process. However, for larger networks, more databases can be installed. For example, three databases may be installed—one database each for the devel, test, and production environments.

[0501] The installation and file system structure for the database will follow an Optimal Flexible Architecture (OFA). OFA is a set of 13 guidelines or goals to follow in order to standardize the database environment.

Database Backup/Recovery

[0502] Backups of the SA database will meet the following requirements:

• • [0503] The backup script utility may be written in perl. Perl allows scripts to be more portable across hardware platforms.
  • [0504] The backup utility can be executed by the UNIX cron utility. It could also be executed by the Windows NT AT scheduler if running on the NT platform.
  • [0505] Cold backups will be run at least once a week. This requires database down time.
  • [0506] Exports of the database will take place every evening at the end of the nightly batch schedule.
    Database Objects

[0507] All database objects (DDL, scripts, code) will be placed under source control. This will be done to allow for versioning when databases for other environments are implemented. This also places these objects in a centralized location for easy access.

Data Management, Retention and Volume

Data Volume

[0508] The estimated volume of detailed data is 2,000,000 rows per day. The physical design of the database will be built to handle this volume while remaining flexible enough to handle a large increase in volume.

Data Manipulation

[0509] Due to the fact surrogate keys will be used to uniquely identify records, a look-up process needs to occur in the data manipulation process. A perl script will be written to extract surrogate key information from the database to flat files. The data manipulation process will use these files to generate flat files in a format that can be loaded into the database tables.

[0510] During the nightly batch window, all the detailed level data will be rolled up into daily summary data.

Data Loading

[0511] The detailed records will be loaded in via a perl script which utilizes an SQL*Loader utility. While all of the major RDBMS vendors have some sort of data loading utility, they are all different syntactically. Thus, this script may need to change depending on the type of database. For phase 2, the detailed data may be loaded once a day during the nightly batch run.

[0512] Daily rollup data will also be loaded via the load script at the same time the detail records are loaded. This is possible because all of the data manipulation is occurring outside of the RDBMS.

Data Retention

[0513] Phase 2 recommends detailed level data to be retained for 40 days and daily rollup data retained for 13 months. A perl script will be written to purge this data once is has expired. This process will run nightly, before the actual loading of the data occurs.

Database Developer

[0514] The purpose of this section is to outline the steps taken to install a Database Developer portion of the present invention on a server. The following examples will teach how to install the Database Developer on both a Windows NT Workstation and a HP 9000 Server. This section should also be applicable to install the Database Developer on different servers. “Dbse” throughout the description of the present invention denotes a database program.

Minimum System Requirements

[0515] The minimum system requirements should be determined before installation of the Database Developer. Exemplary minimum system requirements are:

Each Client Workstation

• • [0516] 90 MHz processor
  • [0517] 32 Mb RAM, 1 Gb hard disk
  • [0518] 60–680 Mb available disk space (depending on the components you choose to install
  • [0519] Windows NT 4.0/95
  • [0520] SQL*Net Client 2.3.4
  • [0521] TCP/IP Adapter
  • [0522] SQL*Plus 3.3.4
  • [0523] CD-ROM drive
    On the Server (for each Repository Created).
  • [0524] 125 Mb in the SYSTEM tablespace
  • [0525] 20–330 Mb in other tables for the Repository data
  • [0526] 16–28 Mb available RAM for the System Global Area
    Pre-Installation Steps
  • [0527] Verify the system requirements are met at each client workstation
  • [0528] Verify the Server is correctly installed and accessible on the network
  • [0529] Verify the database instance you wish to use has been created and functions correctly
    Client Installation
• [0530] 1. Log on to the client workstation as a user with administrative privileges but not as an administrator
• [0531] 2. Close all other applications
• [0532] 3. Insert the Database Developer media into a media reading device, (e.g., insert a CD into the CD-ROM drive)
• [0533] 4. In the Installation Settings dialog box:
  • [0534] a) Type the name of the license owner for this product
  • [0535] b) Select DEFAULT_—HOME for Home Name
  • [0536] c) Select English for Home Language
  • [0537] d) Click OK
• [0538] 5. Select Yes in all Update message boxes to update out-of-date products
• [0539] 6. Select No in all Up to Date message boxes to avoid reinstalling products
• [0540] 7. Select OK in all General Information and System Support Files message boxes to acknowledge message
• [0541] 8. Select OK to acknowledge the products that will be installed
• [0542] 9. Select OK to acknowledge that Installation is complete
• [0543] 10. Configure SQL*Net as follows:
  • [0544] a) Click Start→Programs→SQL for Windows NT→SQL Net Easy Configuration
  • [0545] b) Choose Add Database Alias and click OK
  • [0546] c) In the Choose Database Alias box, type the database alias you wish to use and click OK
  • [0547] d) Choose TCP/IP in the Choose Protocol dialog box and click OK
  • [0548] e) In the Choose Protocol/Server Name and Database Instance dialog box:
    • [0549] i. Type the name of the server where the database is located
    • [0550] ii. In the Database Instance field, type the name of the database
    • [0551] iii. Click OK, click Yes
  • [0552] f) Select Exit SQL*Net Easy Configuration, click OK
    • [0553] g) Click OK
      Server Installation (Example)
      Activities at the Database Server
• [0554] 1. Log on to the UNIX Server that holds the database
• [0555] 2. Start the database instance, if not already running
• [0556] 3. Verify that TNS Listener is running
• [0557] 4. Log onto SQL*Plus as system
• [0558] 5. In SQL*Plus, verify that the procedural option has been installed
• [0559] a) Execute the following SQL statement:
  SQL>create or replace procedure test as x varchar2(10)
• [0560] 2 begin
• [0561] 3 if x is null then null
• [0562] 4 end if
• [0563] 5 end
• [0564] 6/
  Warning: Procedure created with compilation errors.
  SQL>drop procedure test;
  Procedure dropped.
• [0565] b) If the SQL statement returns an error (as above), continue to step c. Otherwise go to step 5.
• [0566] c) Run the following SQL script to install the option where dbse_—home is the location where dbse is installed (C:\DBSNT):
  SQL>@dbse_—home/rdbms/73/admin/catproc.sql
• [0567] 6. Create two tablespaces, one for Repository Data and another for Repository Index, of size 100 Mb each
• [0568] 7. Create one tablespace for small rollback segments (recommended four rollback segments @(10 Mb each)
• [0569] 8. Create one large tablespace for the rollback statement used during installation. The size of this rollback segment should be as big as possible (recommended at least 100 Mb). In this tablespace, create one rollback segment and put it online (this rollback segment and tablespace can be removed after install)
• [0570] 9. Take all rollback segments offline except the SYSTEM rollback segment and the one created in step 8
• [0571] 10. Check the size of the SYSTEM tablespace. You will need at least 125 Mb of free space for the installation.
• [0572] 11. Create the Repository owner and grant connection and resource roles
• [0573] 12. Check the settings in the INITxxx.DBA (where xxx is the name of the database) file and change if necessary.
• [0574] 13. If any changes were made to INITxxx.DBA in the previous step, shut down and restart the database. Make sure to close anySQL*Plus windows that are open. Once the database is restarted successfully, exit Server Manager
  Activities at the Client
• [0575] 1. Log on to the Client Workstation as a user with administrative privileges but not as an administrator
• [0576] 2. Log on to the database using SQL*Plus
• [0577] 3. Create a role called OWNER
• [0578] a) Execute the following SQL statement where home is the directory where the Database Developer was installed:
  SQL>home\repadm20\utl\ckrorole.sql
• [0579] b) Issue all privileges that apply to the Repository owner
• [0580] 4. Grant the role created in step 3 to the Repository owner
• [0581] 5. Close any SQL*Plus windows that are open
• [0582] 6. Start the Repository Administration Utility
• [0583] 7. Connect as the Repository owner
• [0584] 8. Click the View Privileges button and verify the required system privileges and roles are granted for the Repository owner. If privileges and roles are correct, continue by clicking Cancel
• [0585] 9. Click the View Tablespace button to check whether the SYSTEM tablespace is fragmented. If the SYSTEM tablespace is not fragmented, continue by clicking Cancel
• [0586] 10. Click the View Parameters button to verify the parameters are set correctly. If they are correct, continue by clicking Cancel
• [0587] 11. Click the Install button on the Repository Administration Utility window
• [0588] 12. Choose Small, Medium, or Large in the Repository Size box according to the expected initial size of the Repository and click the Estimate button
• [0589] 13. Click the Pre-Check button to view a list of system privileges. Insure all privileges have a check mark under the “Has Privilege?” column. If so, continue by clicking OK
• [0590] 14. Click the Tablespace button to view all available tablespaces. Insure there is sufficient space in each tablespace. If so, continue by clicking Cancel
• [0591] 15. Assign tablespaces for indexes by using the dropdown lists under Index Tablespaces and for tables using the dropdown lists under Table Tablespaces using the tablespaces created in step 6
• [0592] 16. Start installation by clicking on the Start button. NOTE: expect this step to take 2–3 hours
• [0593] 17. When you see the message box saying Operation Complete, click OK, then Cancel
• [0594] 18. Examine the log files to ensure that there are no errors and warnings. These files can be deleted if they contain no errors or warnings and all post-installation is procedures run correctly
• [0595] 19. Delete temporary tablespace/rollback segment
• [0596] 20. Put all other rollback segments online
  Automatic Startup and Shutdown of Database

[0597] This section will outline the steps to setup automatic database startup and shutdown procedures on a HP 9000 server. This process is operating system dependent, but a similar procedure should be applicable for other servers. Consult the correct installation guide for the exact process for the particular server you are working with.

Required Files

[0598] The following files are required for automatic startup and shutdown. Some of the files will be created in the process, some are already created, while others are symbolic links.

/sbin/init.d/dbse

[0599] This file contains the main script for the database startup and shutdown. It can be created from /sbin/init.d/template. This script will start the database with the command su-dbse-c dbstart and shut it down with the command su-dbse-c dbstop.

/etc/rc.config.d/dbse

[0600] This file contains assignment statements that will enable automatic startup and shutdown procedures. It will be created during configuration.

/etc/rc1.d/K100dbse->/sbin/init.d/dbse

[0601] This file is a link to the /sbin/init.d/dbse script. Depending on the three-digit number after the “K”, the script will be executed in ascending order at shutdown. The value after “K” should be as small as possible so the database is one if the first processes to be shutdown.

/etc/rc2.d/S990 dbse->/sbin/init.d/dbse

[0602] This file is a link to the /sbin/init.d/dbse script. Depending on the three-digit number after the “S”, the script will be executed in ascending order at startup. The value after “S” should be as large as possible so the database is one if the first processes to be shutdown.

/etc/dbstab

[0603] This file defines which databases are started automatically. The dbse installer creates this file. It simply needs to be modified. If the database is created manually, this file needs to be created.

Create /sbin/init.d/dbse script

[0604] Make a copy (but do not remove)/sbin/init.d/template and name it /sbin/init.d/dbse.

Create/etc/rc.config.d/dbse file

[0605] Create a file named dbse in the /etc/rc.config.d/directory. In this file, include the following assignment statements:

DBSE_—START=1

Export DBSE_—START

Edit the /etc/dbstab File

[0606] This file will already be created if the installer program is used to create the database. This file includes all database that are to be started automatically at system boot. An entry for each database instance will be listed in this file in the format where Y indicates automatic startup and shutdown is activated and N indicates it is not activated:

DBSE_—SID:DBSE_—HOME:{Y\N}

[0607] For each database in which you want to invoke automatic startup and shutdown, make changes as necessary to activate automatic startup and shutdown.

Create a Link from /sbin/rc1.d/Kxxxdbse to /sbin/init.d/dbse

[0608] The scripts in the directory /sbin/rc1.d are executed when the system is shut down. Scripts are executed in ascending order determined by the three-digit number after the “K” (where the number is xxx of the filename Kxxxdbse). This number defines ordering between all scripts located in the /sbin/rc1.d directory. Number the script as low as possible in order to ensure that dbse is shut down before any other processes.

Create a Link From /sbin/rc2.d/Sxxxdbse to /sbin/initd/dbse

[0609] The scripts in the directory /sbin/rc2.d are executed when the system is started. Scripts are executed in ascending order determined by the three-digit number after the “S” (where the number is xxx of the filename Sxxxdbse). This number defines ordering between all scripts located in the /sbin/rc2.d directory. Number the script as high as possible in order to ensure that dbse is shut down before any other processes.

[0610]

/sbin/init.d/dbse
#!/sbin/sh
#
PATH=/sbin:/usr/sbin:/usr/bin
export PATH
rval=0
set—return () {
	x=$?
	if [ $x -ne 0 ]
	then
	echo “EXIT CODE: $x”
	rval=1
	fi
}
case $1 in
start—msg)
	echo “Start Dbse”
	;;
stop—msg)
	echo “Stop Dbse”
	;;
‘start)
	if [ -f/etc/rc.config.d/dbse ]
	then
	./etc/rc.config.d/dbse
	else
	echo “ERROR: /etc/rc.config.d/dbse MISSING”
	exit 2
	fi
	if [ ${DBSE—START} -eq 1 ]
	then
	echo “Starting Dbse”
	su - dbse -c dbstart
	set—return
	else
	rval=2
	fi
	;;
	‘stop’)
	if [ -f/etc/rc.config.d/dbse ]
	then
	./etc/rc.config.d/dbse
	else
	echo “ERROR: /etc/rc.config.d/dbse MISSING”
	exit 2
	fi
	if[ ${DBSE—START} -eq 1 ]
	then
	echo “Stopping Dbse”
	su - dbse -c dbshut
	set—return
	else
	rval=2
	fi
	;;
	*)
	echo “Usage: $0 {start|stop}”
	;;
	esac
	exit $rval

Manual Startup and Shutdown of Database

[0611] This section will outline the processes to start and stop a database on a HP 9000 server. It will also outline the process to start and stop a Listener Service. This process should be applicable for different versions of UNIX and databases on various other types of servers.

Database Startup Procedure

[0612] The following steps can be used when starting a database in UNIX version HP UX 10.2. To verify that the database has been started properly, see the section below entitled “Verifying the database is started/stopped.”

• [0613] 1. Log on to UNIX as system user
• [0614] 2. Enter system user's password
• [0615] 3. Check environment table to be sure that the DBSE_—SID is set to the database instance that you wish to start→env
• [0616] 4. Start Server Manager→svrmgrl
• [0617] 5. Connect to the system user→connect/as sysdba
• [0618] 6. Start the database→startup
• [0619] 7. Exit Server Manager→exit
  Database Shutdown Procedure

[0620] The following steps can be used when stopping a database in UNIX. To verify that the database has been stopped properly, see section five below.

• [0621] 1. Log on to UNIX as system user
• [0622] 2. Enter system user's password
• [0623] 3. Check environment table to be sure that the DBSE_—SID is set to the database instance that you wish to start→env
• [0624] 4. Start Server Manager→svrmgrl
• [0625] 5. Connect to the system user→connect/as sysdba
• [0626] 6. Start the database→shutdown
• [0627] 7. Exit Server Manager→exit
  Verifying the Database is Started/Stopped

[0628] It is possible to check if the database is started or stopped after completing the steps above. To do so, follow these steps.

• [0629] 1. At the command line type→ps-ef|grep system_—user (where system_—user is the system user's name that you are logged on as)
• [0630] 2. Look for an entry such as the following→ora_—pmon_—SD (where SID is the database instance name)

[0631] If you find an entry such as the one above, the database is running. If not, the database is shutdown. If you do not get the expected results, redo the database startup or shutdown procedure set forth above.

Listener

[0632] The Listener is a utility that handles database connection requests. It may have a service for all databases. If a database is started while the Listener is already running, it may be necessary to restart the Listener.

a. Check If Listener is Started for the Database Instance

[0633] At the UNIX prompt, enter the command lsnrctl status. Under the heading “Services Summary . . . ”, check that there is an entry reading “SID has 1 service handler(s).” (where SID is the desired database instance). If there is an entry for the desired SID, then the Listener is started. Otherwise the listener is not started.

b. Start the Listener

[0634] To start the listener, type lsnrctl start at the UNIX prompt. After this has completed, verify the listener is started by following the steps above in section 6.1.

c. Stop the Listener

[0635] It is recommended to stop the listener when the server is shutdown. To do this type lsnrctl stop at the UNIX prompt.

Database Configuration

[0636] FIG. 12 illustrates an exemplary database configuration chart. Table 3 provides a key to FIG. 12.

[0637]

TABLE 3
Key:	O/S	Operating system at current location. Values
		include HP-UX, NT.
	Tablespace:	Name of database tablespace
	Default	Indication of whether tablespace should be on-
	status:	line/off-line during normal environment
		operation.
	Physical	Indication of such things as requirements to have
	requirements:	data vs. index tablespaces on separate system
		disks.
	Related	List of other tablespaces and their relationships to
	tablespaces/	the current tablespace. For example, the related
	relationships:	tablespace might be an index counterpart to the
		current tablespace, or it might be a data
		partitioning counterpart, etc.
	Datafile:	Name of a database datafile which is part of the
		current tablespace.
	Datafile	Size, in MB of the current database datafile.
	size:

[0638]

	TABLE 4
	File Descriptors	Default Status	File name(s)	File size

[0639]

	TABLE 5
	Key:	File Descriptors	Description of the file or
			file groups which will
			reside outside of the
			database
		Default status	Condition of file at time
			of database startup-
			available, off-line
		File name(s)	Name(s) of files to be created
		File size	Size of files to be created

Environment: Development Location: Development database Server

[0640]

	TABLE 6
		Group Permitted
	Tablespace	Access	Type of Access
	IPSADAT01	SPSS—DEVEL	Unlimited
		ALL	Read-Only
	IPSAIDX	SPSS—DEVEL	Unlimited
		ALL	Read-Only
	USERS	ALL	Unlimited
	SYSTEM	DBA	Unlimited
	TEMP01	ALL	Unlimited
	@

[0641]

TABLE 7
Key:	Tablespace	Name of database tablespace
	Group Permitted	Name of security group (see security docu-
	Access	ment) permitted access to current tablespace.
	Type of Access	Type of access allowed to tablespace, such as
		unlimited, quota per user, etc.

[0642]

init.dbs Configuration
db—name = IPSA01
db—block—size = 8192
sort—area—size = 102400
db—file—multiblock—read—count =32	# LARGE
db—block—buffers = 10000	# LARGE
shared—pool—size = 70000000	# X LARGE
log—checkpoint—interval = 10000
processes = 100	# MEDIUM
nls—date—format = “DD-Mon-YYYY HH24:MI:SS”
dml—locks = 200	# MEDIUM
log—buffer = 65536	# des 2k
open—cursors = 400	# des 2k
sequence—cache—entries = 10	# SMALL
sequence—cache—hash—buckets = 10	# SMALL
max—dump—file—size 10240	# limit trace file size to 5 Meg each
# If using private rollback segments, place lines of the following
# form in each of your instance-specific init.ora files:
# rollback—segments = (name1, name2)
rollback—segments = (r01,r02,r03,r04)
# Global Naming -- enforce that a dblink has same name as the db it
connects to global—names = TRUE
# adversely affect performance. On some non-VMS ports the
db—block—cache—*
# debugging modes have a severe effect on performance.
—db—block—cache—protect = true	# memory protect buffers
compatible = 7.3.4
# By default, this is false.
lock—shared—memory = true
# reads entire SGA into memory on startup. Will take longer to startup
database
# define two control files by default
control—files = (/files0/ipsa/vendor/dbse/links/IPSA01/IPSA01ctrl01.ctl,
	/files0/ipsa/vendor/dbse/links/IPSA01/IPSA01ctrl02.ctl,
	/files0/ipsa/vendor/dbse/links/IPSA01/IPSA01ctrl03.ctl,
	/files0/ipsa/vendor/dbs/links/IPSA01/IPSA01ctrl04.ctl)
background—dump—dest = /files0/ipsa/vendor/dbse/admin/IPSA01/bdump
user—dump—dest = /files0/ipsa/vendor/dbse/admin/IPSA01/udump
core—dump—dest = /files0/ipsa/vendor/dbse/admin/IPSA01/cdump

Database Cold Backup

[0643] This section will describe the required files needed to run cold_—backups. It will outline the recommended steps to configure cold_—backups (written in Perl) to run on a database. Finally, this section will discuss how to execute the script. The location of the cold_—backups script can be anywhere in the database's directory system.

Required Files/Directories

[0644] The following list of files and directories need to be created before cold_—backups will run. These files are described bellow and common naming conventions are given. Also, an OFA directory structure location is specified.

System Identification (SID) File

[0645] This file should contain a list of one or more system ids, dbse home, and dbse base. Every system id will be shutdown, backed up, and restarted. The values of this file are also used to set the correct environment of the SID. The format of this file must be of the form: SID,DBSE_—HOME,DBSE_—BASE. There are no spaces between fields. Also, it is important that there are no blank lines in this file. A common naming convention for this file is cold_—sids. The file is best housed with other files related to the backup.

SQL*Plus Script File

[0646] This script will generate a complete list of files that need to be backed up. A common naming convention is unix_—script.sql. This file will be located in the directory specified for sql scripts.

Password Script

[0647] The contents of this file can be found in Appendix B or in O:\Performance\QoSM\Product Development\Database\Doc\getpass. This script allows the user's password to be used within the script without hard-coding the password into it. A common naming convention is getpass. This file should be housed in the unix directory. NOTE: the password is not encrypted so it is important that an unauthorized user does not execute this script at the command line. Also, if the password scripts is used within another script it is important the password is not echoed to the screen.

Backup Directory

[0648] The backup directory will store the backup files. The naming convention for this directory is {SID}_—cold where SID is the system id. The file is best housed in the current directory (described below in “Current Directory”).

Configuration of Variables

[0649] Directory paths and file names are hard coded in the cold_—backups script. Therefore it is recommended to configure these variables to suit the file structure of the particular database the backup is to be performed on. The following are variables that need to be specified for the system in which the script is run on. These variables are grouped together at the beginning of the script. Look for the commented section entitled “Variable List” to locate the variables within the script.

Current Directory ($Current Dir)

[0650] This variable is set to the directory location in which the backup directory (described above in “Backup Directory”), log file (described below in “Log”), system id file (described above in System Identification (SID) File), and temporary directory (created during execution of the backup and deleted after its completion) are kept. This directory can be placed anywhere.

SID File ($SID File)

[0651] This file, described above in System Identification (SID) File, is located in the current directory. This file contains a list of system ids and environment variables for all databases that need to be backed up. The files has a strict format that must be followed for the script to run in the cron.

Script File ($Script_—File)

[0652] This file is described above in “SQL*Plus Script File.”

Log($Log)

[0653] This file contains messages and errors generated from cold_—backup. A common naming convention is cold.log. The log file is created in the current directory (see section 4.2 above) by cold_—backups. The log file will be kept in an archived form, also in the current directory, for one previous backup and it will be named *.old where * represents the filename you designate as the log file.

User ($User)

[0654] This variable designates which user has export privileges.

Mail Program ($Mailer)

[0655] This variable is set to a command line mail program. The mail program is used to notify the Database Administrator of problems occurred during execution of the backup or to inform the Administrator of a successful backup.

File Mount Point ($Mount_—point)

[0656] This variable is set to the directory structure mount point.

DBA ($DBA)

[0657] This variable is set to the Database Administrator that is responsible for getting email messages.

How to Execute

Manually

[0658] The cold_—backups script can be run manually by having a user execute the script. Since the backup requires that the database be shutdown, inform all database users of the downtime. Expect up to fifteen minutes for the backup. In order to run the script manually go to the directory where the script is located. Check the permissions, and set if necessary. At the command line, type cold_—backups. The script will run without user interaction. The script is complete when the prompt returns.

Crontab

[0659] It is possible to schedule the backup using the UNIX crontab. To create a new crontab or to edit an existing one, type crontab-e at the UNIX command line. To list the entries in the crontab, type crontab-l at the command line. Entries in the crontab file are lines of six fields separated by spaces or tabs. An asterisk (*) in fields one through five indicates all legal values. Values can be separated by commas (for a list of values) or hyphens (for inclusive range). The fields are as follows:

[0660]

	TABLE 8
	Field	Acceptable Data
	1-minute	0–59
	2-hour	0–23
	3-month	1–31
	day
	4-month	1–12
	5-weekday	0–6 (Sunday is 0)
	6-command	Complete path of executable

[0661] By using the crontab, it is possible to schedule daily, weekly, or monthly backups during non-peak hours. Since the backup requires that the database be shutdown, inform all database users of the expected downtime. Expect up to fifteen minutes for the backup. Check the permissions of cold_—backups before the first backup and set if necessary.

[0662]

SQL*Plus Script File
set echo off
set heading off
set embedded on
set pagesize 1000
set termout off
spool & 1
select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;
spool off
set termout on
set heading on
set feedback on
set embedded off
exit
Password Script
#!/bin/sh
#------------------------------------------------------------#
# Script—name: getpass	#
# Description: This script will be used to retrieve the #
#    appropriate password from the password #
#    file. It can be used from the command #
#    line to retrieve a password or from a #
#    shell script to eliminate hard coding of #
#    passwords. The .password file is located #
#    in $DBSE—HOME, with the executable #
#    located in $DBSE—HOME/bin.    #
#	#
# Dependencies: getpass requires one file, password #
#	#
# SID—FILE: Contains a list passwords for #
#    system, sys, and dbse.    #
#	#
# Command syntax: getpass USERNAME	#
#----------------------------------------------------------------------#
# what shell do we use?
SHELL=/usr/bin/sh
# where is our home?
DBSE—HOME=/files2/ipsa/vendor/dbse/product/7.3.4; export
DBSE—HOME
# what path do we look for?
PATH=/usr/bin:/bin:${DBSE—HOME}/bin; export PATH
# who are we retrieving the password for
if[ $1 ]
then
	USER=$1
else
	echo “ ”
	echo “USAGE: getpass ‘username’”
	echo “ ”
	exit 1
fi
# make sure the .password file exist
if [ ! -s $DBSE—HOME/.password ]
then
	echo “ ”
	echo “ERROR: this machine does not appear to have a password file”
	echo “ ”
	exit 1
fi
# get the appropriate password
PASSWORD=‘cat $DBSE—HOME/.password|grep -i “${USER}
”|awk ‘{print $2}’‘
BAD—SIDS=‘cat $DBSE—HOME/.password|grep -i “${USER}
“|awk ‘{print $3}’‘
OLD—PASS=‘cat $DBSE—HOME/.password|grep -i “${USER}
“|awk ’{print $4}’‘
# if a password was found, print it to the screen
if [ $PASSWORD ]
then
	echo “$ {PASSWORD}”
	# print a message if passwords appear to not be synced
	between databases
	if [ $BAD—SIDS ]
	then
	BAD—SIDS=‘echo ${BAD—SIDS}|cut -c2-100‘
	echo “”
	echo “WARNING:\tThe password for ${USER} may not be
	synchronized ”
	echo “\t\tbetween all databases. The database(s) ${BAD—SIDS}
	appear(s) ”
	echo “\t\tto use the old password ‘${OLD—PASS}’”
	echo “”
	fi
else
	echo “ ”
	echo “ERROR: ${USER} is not a supported username”
	echo “ ”
	exit 1
fi

[0663] The following is an entry of a crontab. The cold_—backups script will be backed up at 2:30 am daily.

• [0664] /files2/db/dev/backup1/cold_—backups>crontab-l 30 2* * * /files2/db/vendor/dbse/admin/general/unix/cold_—backups/cold_—backups

[0665] If both fields are specified in an entry, they are cumulative. For example:

• [0666] 0 0 1,15 * 1/files2/db/vendor/dbse/admin/general/unix/cold_—backups/cold_—backups

[0667] This crontab entry runs at midnight on the first and fifteenth of each month, as well as every Monday. To specify days in only one field, set the other field to asterisk (*). For example:

• [0668] 0 0* * 1/files2/db/vendor/dbse/admin/general/unix/cold_—backups/cold_—backups

[0669] This crontab entry runs only on Mondays.

Database Export Backup

[0670] This section will describe the files needed to run export_—backups. It will outline the recommended steps to configure export_—backups (written in Perl) to run on a database. Finally, this section will discuss how to execute the script. The location of the export_—backups script should be in admin/general/unix (following OFA standards).

[0671] The export_—backups script performs a full database export on a database by using an export utility. This script is meant to run daily and the backup files are stored in a cycle that can be configured to suit the needs of the client. For an explanation of the backup cycle and naming convention, see the sections below entitled “Configuration of Backup Cycle” and “How to Execute.”

Required Files/Directories

[0672] The following list of files and directories need to be created before export_—backups will run. These files are described bellow and common naming conventions are given. Also an OFA directory structure suggestion is given.

System Identification (SID) File

[0673] This file should contain a list of one or more system ids and the corresponding values of dbse home and dbse base. Every system id listed in this file will be backed up using the export utility. The values in this file are also used to correctly set the environment variables. The format of this file will be: SID,DBSE_—HOME,DBSE_—BASE. There are no spaces, only commas, separating the fields. Also, it is important that there are no blank lines in this file. A common naming convention for this file is export_—sids. The file is best housed in the current directory (described below in “Current Directory”).

Day File

[0674] This file will contain the current day number of the backup cycle. The backup cycle is commonly four weeks in length but it can be set at any interval (see section five below on configuration of cycle variables). The day file will contain an integer between one and the maximum day number of the cycle (29 for a four-week cycle). It is important that there are no blank lines in this file. The user will need to create this file before the initial execution of the backup and after that the file will update itself. It is a good idea to have the day number correspond to the current weekday. For example, a one in the day file will correspond with Sunday and a four will correspond with Wednesday. A common naming convention is day_—file.dat. This file is best housed in the current directory (described\-below in “Current Directory”).

Password Script

[0675] This script needs to be configured for the system. This script allows the user's password to be used within the script without hard-coding the password into it. A common naming convention is getpass. This file should be located in /admin/general/unix. NOTE: the password is not encrypted so it is important that an unauthorized user does not execute this script at the command line. Also, if the password scripts is used within another script it is important the password is not echoed to the screen.

Backup Directory

[0676] The backup directory will store the backup files. The naming convention for this directory is {SID}_—exp where SID is the system id. The file is best housed in the current directory (described below in “Current Directory”).

Configuration of Variables

[0677] Directory paths and file names are hard coded in the export_—backups script. Therefore it is recommended to configure these variables to suit the file structure of the particular database the backup is to be performed on. The following are variables that need to be specified for the system in which the script is run on. These variables are grouped together at the beginning of the script. Look for the commented section entitled “Variable List” to locate the variables within the script.

Current Directory ($Current Dir)

[0678] This variable is set to the directory location in which the backup directory (described above in “Backup Directory”), log file (described below in “Log”), SID file (described above in “System Identification (SID) File”), and day file (described above in “Day File”) are kept. This directory can be placed anywhere.

Day File ($Day_—File)

[0679] This file is described above in “Day File”.

SID File ($SID_—File)

[0680] This file is described above in “System Identification (SID) File.”

Log ($Export_—Log)

[0681] This file contains messages and errors generated from export_—backup. A common naming convention is export.log. During execution, the log file is opened and the entries will be appended to the existing file. If the file does not exist, the script will create a new log file with the name you designate here. Old log files will be kept in an archived form, also in the current directory, and will contain the logged entries for an entire backup cycle. The archived file will be named *.yyyymmmdd (as in 2001Dec.31) where * represents the filename you designate as the log file. The archive will occur if the day file contains the last day of the cycle.

User ($Exp_—User)

[0682] This variable designates which user has export privileges.

Mail Program ($Mailer)

[0683] This variable is set to a command line mail program. The mail program is used to notify the Database Administrator of problems occurred during execution of the backup or to inform the Administrator of a successful backup.

DBA ($DBA)

[0684] This variable is set to the Database Administrator that is responsible for getting email messages.

Configuration of Backup Cycle

[0685] The backup cycle can be changed to suit the particular requirements for the database. There are six cycle variables in the script that need to be configured if the cycle is changed. They are located in the top portion of the script in a section entitled “Cycle Variables” immediately following the “Variable List” described above in section four. The values of these variables are used to name the backup file and to determine the schedule for when the backup files rollover.

Maximum Day Files ($maxday)

[0686] This variable should be set to the maximum number of daily backup files that are kept minus one. For example, if seven daily backups are kept, this value should be set to six. The naming scheme for the daily backup files is such that day00 is the current day's backup and day06 is six days ago.

Week Ending Day ($week start)

[0687] This variable is a string and should be set to the three letter abbreviation of the week day name in which you want the weekly backup files to rollover. For example, if you want the week backup files to roll over on Sundays you would set this variable to “Sun”.

Maximum Week Files ($maxweek)

[0688] This variable should be set to the maximum number of weekly backup files that are kept minus one. On the week ending date, the weekly backup files will be rolled back and day07 will become week01. For example, if you want to keep four weekly backup files, this variable should be set to three.

Maximnum Cycle Days ($maxcycledays)

[0689] This variable should be set to the total number of days in the backup cycle plus one. The value of this variable will be the largest possible value that is in the day file (see section 3.2 above). For a four-week cycle, the maximum cycle days variable should be set to twenty-nine (4 weeks * 7 days per week+1 day=29 cycle days). A value of fifty-seven would represent an eight week cycle (8*7+1=57). When the value in the day file is the same as the maximum cycle days variable the cycle backup will occur.

Cycle Increment ($inc_—value)

[0690] Weekly backups will be kept in longer cycle increments for long term storage. The value of this variable should be set to the increment value. This variable comes into effect when rolling back the week files (described in section 5.3). For example, if you want to keep backups in four-week intervals (starting at the last weekly backup) you would set this variable to four.

Maximum Cycle Files ($maxcycle)

[0691] This variable should be set to the maximum number of backup files that are kept minus the cycle increment value. For example, if you want to keep backup files for fifty-two weeks (one year) and your increment value is four, the value of this variable should be forty-eight. If the day in the day file is equal to the maximum cycle days, the weekly backup files will be rolled back in increments designated by the increment cycle variable. For example, week04 would become week12 if the increment value were eight.

How to Execute

Manually

[0692] The export_—backups script can be run manually by having a user execute the script. Since the backup requires that the database be shutdown, inform all database users of the downtime. Expect up to fifteen minutes for the backup. In order to run the script manually go to the directory where the script is located. Check the permissions, and set if necessary. At the command line, type export_—backups. The script will run without user interaction. The script is complete when the prompt returns.

Crontab

[0693] It is possible to schedule the backup using the UNIX crontab. To create a new crontab or to edit an existing one, type crontab-e at the UNIX command line. To list the entries in the crontab, type crontab-l at the command line. Entries in the crontab file are lines of six fields separated by spaces or tabs. An asterisk (*) in fields one through five indicates all legal values. Values can be separated by commas (for a list of values) or hyphens (for inclusive range). The fields are as follows:

[0694]

	TABLE 9
	Field	Acceptable Data
	1-minute	0–59
	2-hour	0–23
	3-month	1–31
	day
	4-month	1–12
	5-weekday	0–6 (Sunday is 0)
	6-command	Complete path of executable

[0695] By using the crontab, it is possible to schedule daily, weekly, or monthly backups during non-peak hours. Since the backup requires that the database be shutdown, inform all database users of the expected downtime. Expect up to fifteen minutes for the backup. Check the permissions of export_—backups before the first backup and set if necessary.

[0696]

Password Script
#!/bin/sh
#------------------------------------------------------------#
# Script—name: getpass	#
# Description: This script will be used to retrieve the #
#    appropriate password from the .password #
#    file. It can be used from the command #
#    line to retrieve a password or from a #
#    shell script to eliminate hard coding of #
#    passwords. The .password file is located #
#    in $DBSE—HOME, with the executable #
#    located in $DBSE—HOME/bin.   #
#	#
# Dependencies: getpass requires one file, .password   #
#	#
# SID—FILE: Contains a list passwords for  #
#    system, sys, and dbse.    #
#	#
# Command syntax: getpass USERNAME	#
#------------------------------------------------------------#
# what shell do we use?
SHELL=/usr/bin/sh
# where is our home?
DBSE—HOME=/files2/ipsa/vendor/dbs/product/7.3.4 export
DBSE—HOME
# what path do we look for?
PATH=/usr/bin:/bin:${DBSE—HOME}/bin; export PATH
# who are we retrieving the password for
if[ $1 ]
then
	USER=$1
else
	echo “ ”
	echo “USAGE: getpass ‘usemame’”
	echo “ ”
	exit 1
fi
# make sure the .password file exist
if [ ! -s $DBSE—HOME/.password ]
then
	echo “ ”
	echo “ERROR: this machine does not appear to have a password file”
	echo “ ”
	exit 1
fi
# get the appropriate password
PASSWORD=‘cat $DBSE—HOME/.password|grep -i “${USER}
”|awk ‘{print $2}’‘
BAD—SIDS=‘cat $DBSE—HOME/.password|grep -i “S{USER}
”|awk ‘{print $3}’‘
OLD—PASS=‘cat $DBSE—HOME/.password|grep -i “S{USER}
”|awk ‘{print $4}’‘
# if a password was found, print it to the screen
if [ $PASSWORD ]
then
	echo “${PASSWORD}”
	# print a message if passwords appear to not be synced between
	databases
	if [ $BAD—SIDS ]
	then
	BAD—SIDS=‘echo ${BAD—SIDS}|cut -c2-100‘
	echo “”
	echo “WARNTNG:\tThe password for ${USER} may not be
	synchronized ”
	echo “\t\tbetween all databases. The database(s) ${BAD—SIDS}
	appear(s) ”
	echo “\t\tto use the old password ‘${OLD—PASS}’”
	echo “”
	fi
else
	echo “ ”
	echo “ERROR: ${USER} is not a supported username”
	echo “ ”
	exit 1
fi

Data Loading Script

[0697] The script data_—loader is used to load comma delimited data from a text file into a Database by using SQL*Loader. The script can create a control file for this process if one is needed. This script can be run from the cron to load data into many tables, or it can be run from the command line on a single table. It is also possible to do a data re-load using data_—loader. The script performs error checking and reports via email to a specified administrator of success or failure. The script may be written in Perl.

Configuration

Environment Variables

[0698] Environment variables relevant to the Database need to be set. Specifically, the following environment variables need to be set: DBSE_—SID, DBSE_—HOME, DBSE_—BASE, HOME, and PATH (to include the location of the dbse bin directory and the directory that holds the scripts used by this procedure). To configure these variables, look for the section in the code entitled setting environment variables. Set the values of $SID, $Home, and $Base. Verify that the path is correct when setting the PATH environment variable.

Directory Structure

[0699] A directory structure should be set up to house the different types of files required and produced by data_—loader. The six types of files required include data files (*.dat), control files (*.ctl), log files (*.log), bad files (*.bad), data definition files (*.ddl), and archived files (files from the previous five categories).
File Names

[0700] The files used/created by the data_—loader script should have a standardized format. The log files (.log) and bad files (.bad) are named with a date_—stamp to make the files unique for archiving purposes. The current format of the date_—stamp is ddMonYYYY. The value of $Date_—stamp should be changed to suit the frequency of the data loading. The naming convention should be table_—name.date_—stamp.extension or in the case of the log file generated by the data_—loader script data_—loader.date_—stamp.log. The data files (.dat) should be named table_—name.dat. However, after loading is complete all data files are copied to the archive directory and renamed with a date_—stamp as in the manner described above. The control files (.ctl) will be named table_—name.ctl and will never have a date stamp because they are not archived. The ddl files should be named index_—name.ddl.

SQL Scripts and Stored Procedures

[0701] This script uses a variety of SQL scripts and stored procedures. The scripts should be located in their own directory (following the OFA directory structure). Set $SQL_—dir to this directory location. In order for these scripts to function properly, the table owner must have the ability to select from any table (grant select any table table owner). The SQL scripts and stored procedures used by data_—loader are as follows:

[0702]

TABLE 10
Script Name	Type	Input	Output	Functionality
get—index	script	table	table—name.idx	Spool the index
		name		names of the
				given table to
				the output file.
				(section 0)
drop—index—p	procedure	index	none	Drops the
		name		given index
				(section 0)
create—ctl	script	table	table—name.ctl	Calls the stored
		name		procedure
				create—ctl—p
				and spools the
				control—tb
				table entries to
				the output file.
				(section 0)
create—ctl—p	procedure	table	control—tb	Inserts rows
		name		into control—tb
				to create con-
				trol file
				(section 0)
p—exec	procedure	string	none	Executes the
				SQL command
				passed as a
				parameter
				(section 0)
sp—kill—users—p	procedure	none	none	Kills SPSS user
				processes
				(section 0)
get—direct—load	script	table	table—name.dir	Spools the in-
		name	load	dex names that
				are in direct
				load state of
				the given table
				to the output
				file (section 0)

Input and Output Files
Required Files

• • [0703] table_—list (file containing a list of tables to load—used if no table is indicated at run time)
  • [0704] table_—name.dat (comma delimitated file containing data to load into table_—name)
  • [0705] index_—name.ddl (used to create indexes that were dropped)
  • [0706] table_—name.date_—stamp.dat (copy of table_—name.dat after load for archiving purposes)
    Files Generated
  • [0707] data_—loader.date_—stamp.log (log file generated by data_—loader)
  • [0708] table_—name.idx (generated by the SQL script get_—index, it is deleted at the end of the script—see section 0)
  • [0709] table_—name.ctl (file is generated by SQL script create_—ctl if a ‘-c’ is indicated at run time—see section 0)
  • [0710] table_—name.date_—stamp.log (log file generated by SQL*Loader—see section 0 for an explanation of the date stamp)
  • [0711] table_—name.date_—stamp.bad (bad file generated by SQL*Loader—see section 0 for an explanation of the date stamp)
  • [0712] table_—name.dirload (file generated by SQL script get_—direct_—load, it is deleted if the files has zero size—see section 0 for an explanation)
    Messaging

[0713] The data_—loader script will send email to an administrator to report success or failure of the data loading process. Set $Mailer to the text string that will execute the mailer in command line mode. Set $DBA to the email address of the administrator.

Users

[0714] The data_—loader script will need to log on to SQL*Plus and SQL*Loader a number of times for a variety of purposes. A variable name should be set for each schema name that will be utilized. Do not hard-code the schema password in the script. Instead use getpass.

Options with SQL*Loader

[0715] The following options are used with SQL*Loader:

• • [0716] control=$Control_—file (see section 0)
  • [0717] log=$Log_—file (see section 0)
  • [0718] bad=$Bad_—file (see section 0)
  • [0719] skip=$Skip_—number (default is zero, only changes if doing reload, see section 0)
  • [0720] errors=100
  • [0721] direct=TRUE
  • [0722] silent=ALL
    Options Executing data_—loader
    data_—loader
  • [0723] Get tables to be loaded from file (table_—list)
  • [0724] Do not create a control files
  • [0725] Skip zero records while loading
    data_—loader-c schema_—name
  • [0726] Get tables to be loaded from file (table_—list)
  • [0727] Create control files for all tables
  • [0728] schema_—name is the owner of the tables
  • [0729] Skip zero records while loading
    data_—loader-t table_—name
  • [0730] Table to be loaded is table_—name
  • [0731] Do not create control file
  • [0732] Skip zero records while loading
    data_—loader-r table_—name skip_—number
  • [0733] Table to be loaded is table_—name
  • [0734] Do not create control file
  • [0735] Skip skip_—number records while loading
    Procedure

[0736] The following is the procedure the data_—loader script follows to load data into a database.

[0737]

TABLE 11
Step	Activity/Action
1	Read in and set parameters
	Set database environment
	Set directory structure variables
	Set SQL Script directory
	Set Mailer program and Administrator
	Set Failure Flag to false (0)
2	For each user, set user name and get password using getpass
3	Archive old log files and bad files
4	Set the date stamp for load
	Create data—loader.date—stamp.log
5	Push the table name to be loaded into an array. Get this
	information either from a file (table—list) or from a parameter
6	Find the current indexes on the tables to be loaded
	Drop the indexes that are not on the primary key(s)
7	If necessary, create control files for each table that is to be
	loaded
	Check that the control file was created successfully
8	Alter the database to allow only users with RESTRICTED
	PRIVLEDGES to log on
	Kill user processes that are do not have RESTRICTED
	PRIVLEDGES
9	Set the files names of the data file, control file, log file, and
	bad file
	Check to insure the input files exist, abort loading if they do
	not
10	Start SQL*Loader
11	Check load results by scanning the log file for errors
	Check if there is a bad file generated by SQL*Loader
12	Repeat steps 9–11 for all tables to be loaded
13	Alter database to allow ALL USERS to log on
14	Recreate indexes that were dropped in step 6
15	Check to see if there are any indexes on the loaded table that
	were left in direct load state
	If there are, send a message to the Administrator
	If not, delete the generated file
	Delete the file generated by the get—index SQL script
16	Check the value of the failure flag (it will be set to true if any
	errors occurred at any time during the process above)
	Email the Administrator to report success or failure
17	Archive the data files (if the load is not a re-load) by copying
	the data file to the archive directory and renaming it to include a
	date stamp

Scripts

[0738] The following is the scripts reference above are used by data_—loader. The bolded lines should be configured to the particular system being used.

[0739]

getpass (Born Shell)
#!/bin/sh
#---------------------------------------------------------#
# Script—name: get—pass	#
#	#
# Description: This script will be used to retrieve the	#
#	appropriate password from the .password	#
#	file. It can be used from the command	#
#	line to retrieve a password or from a	#
#	shell script to eliminate hard coding of #
#	passwords. The .password file is located	#
#	in $DBSE—HOME, with the executable	#
#	located in $DBSE—HOME/bin.	#
#	#
# Dependencies: getpass requires one file,.password	#
#	#
#	SID—FILE: Contains a list passwords for  #
#	system, sys, and dbse.	#
#	#
# Command syntax: getpass USERNAME	#
#---------------------------------------------------------#
# what shell do we use?
SHELL=/usr/bin/sh
# where is our home?
DBSE—HOME=/files0/ipsa/vendor/dbse/product/7.3.4; export DBSE—HOME
# what path do we look for?
PATH=/usr/bin:/bin:${DBSE—HOME}/bin; export PATH
# who are we retrieving the password for
if[ $1 ]
then
USER=$1
else
echo “ ”
echo “USAGE: getpass ‘username’”
echo “ ”
exit 1
fi
# make sure the .password file exist
if [ ! -s $DBSE—HOME/.password ]
then
echo “ ”
echo “ERROR: this machine does not appear to have a password file”
echo “ ”
exit 1
fi
# get the appropriate password
PASSWORD=‘cat $DBSE—HOME/.password|grep -i “${USER} ”|awk ‘{print $2}’’
BAD—SIDS=‘cat $DBSE—HOME/.password|grep -i “${USER} ”|awk ‘{print $3}’’
OLD—PASS=‘cat $DBSE—HOME/.password|grep -i “${USER} ”|awk ‘{print $4}’’
# if a password was found, print it to the screen
if[ $PASSWORD ]
then
echo “$ {PASSWORD}”
# print a message if passwords appear to not be synced between databases
if [ $BAD—SIDS ]
then
BAD—SIDS=‘echo ${BAD—SIDS}|cut -c2-100’
echo “ ”
echo “WARNING:\tThe password for ${USER} may not be synchronized”
echo “\t\tbetween all databases. The database(s) ${BAD SIDS} appear(s)”
echo “\t\tto use the old password ‘${OLDPASS}’”
echo “”
fi
else
echo “ ”
echo “ERROR: ${USER} is not a supported username”
echo “ ”
exit 1
fi
get—index (PL/SQL Script)
SET ECHO OFF
REM Procedure Name: get—index
REM
REM Description: Procedure creates a file with a list of current indexes
VARIABLE T—Name varchar2(30)
SET SERVEROUTPUT OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET FLUSH OFF
SET TRIMSPOOL ON
SET TERMOUT OFF
ACCEPT T—Name char
SET PAGES 0
SET HEADING OFF
SPOOL /files6/ipsa/data—loads/&T—Name..idx
select index—name from dba—indexes where table—name = UPPER(‘&T—Name’);
SPOOL OFF
SET TERMOUT ON
SET SERVEROUTPUT OFF
SET VERIFY ON
SET FEEDBACK ON
SET ECHO ON
drop—index—p (PL/SQL Stored Procedure)
CREATE OR REPLACE PROCEDURE drop—index—p (IN—IndexName IN
VARCHAR2)
AS
ws—stmt   varchar2(100);
ws—owner   varchar2(30);
CURSOR c—index—owner (IN—IndexName VARCHAR2)
IS
SELECT table—owner
FROM dba—indexes
WHERE index—name = UPPER(IN—IndexName);
BEGIN
OPEN c—index—owner(IN—IndexName);
FETCH c—index—owner INTO ws—owner;
ws—stmt := ‘DROP INDEX’∥ws—owner∥‘.’∥IN—IndexName;
p—exec (ws—stmt);
CLOSE c—index—owner;
END drop—index—p;
/
show errors;
create—ctl (PL/SQL Script)
SET ECHO OFF
REM Procedure Name: create—ctl
REM Description: Procedure created control file required by SQL*Loader
REM   Uses create—ctl—p.sql to generate control file
REM Tables Accessed: All—Tab—Columns (Data Dictionary Table)
VARIABLE T—Name varchar2(30)
SET SERVEROUTPUT OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET TERMOUT OFF
SET FLUSH OFF
ACCEPT T—Name char
delete control—tb;
commit;
EXECUTE CREATE—CTL—P(‘&T—NAME’);
SET PAGES 0
SET HEADING OFF
SPOOL /files6/ipsa/data—loads/control—files/&T—Name..ctl
select line—text from control—tb order by line—nbr
SPOOL OFF
SET TERMOUT ON
SET SERVEROUTPUT OFF
SET VERIFY ON
SET FEEDBACK ON
SET ECHO ON
create—ctl—p (PL/SQL Stored Procedure)
**Note-there should be a table used specifically for this procedure (control—tb)
with the columns
LINE—NBR number(3) and LINE—TEXT varchar2(100).
CREATE OR REPLACE PROCEDURE create—ctl—p
(IN—TableName  IN  user—tab—columns.table—name%TYPE)
AS
WS—RecNo  number(2) := 0;
WS—line—ctr  number(3) := 0;
WS—index—name varchar2(30);
CURSOR c—index—name(IN—TableName IN user—tab—columns.table—name%TYPE)
is
SELECT index—name
FROM sys.dba—indexes
WHERE sys.dba—indexes.table—name = UPPER(IN—TableName)
AND sys.dba—indexes.index—name like ‘PK%’;
CURSOR c—user—tab—columns(IN—TableName
user—tab—columns.table—name%type) is
SELECT column—name,
data—type,
data—length
FROM user—tab—columns
WHERE user—tab—columns.table—name = UPPER(IN—TableName);
BEGIN
OPEN c—index—name(IN—TableName);
FETCH c—index—name INTO WS—index—name
insert into control—tb values (WS—line—ctr,‘UNRECOVERABLE’);
WS—line—ctr := WS—line—ctr + 1;
insert into control—tb values (WS—line—ctr,‘LOAD DATA’);
WS—line—ctr := WS—line—ctr + 1;
insert into control—tb values (WS—line—ctr,‘INFILE
“/files6/ipsa/data—loads/data—files/‘∥IN—TableName∥’.dat’”);
WS—line—ctr := WS—line—ctr + 1;
insert into control—tb values (WS—line—ctr,‘APPEND’);
WS—line—ctr := WS—line—ctr + 1;
insert into control—tb values (WS—line—ctr,‘INTO TABLE’∥IN—TableName);
WS—line—ctr := WS—line—ctr + 1;
insert into control—tb values (WS—line—ctr,‘SORTED INDEXES
(‘∥WS—index—name∥’)’);
WS—line—ctr := WS—line—ctr + 1;
insert into control—tb values (WS—line—ctr,‘FIELDS TERMINATED BY’∥‘“,’”);
WS—line—ctr := WS—line—ctr + 1;
insert into control—tb values (WS—line—ctr,‘(’);
WS—line—ctr := WS—line—ctr + 1;
FOR c—rec in c—user—tab—columns(IN—TableName) LOOP
IF WS—RecNo = 0 then
IF c—rec.data—type = ‘DATE’ THEN
insert into control—tb values (WS—line—ctr,c—rec.column—name∥
“DATE “MM/DD/YYYY HH24:MI:SS””);
ELSE
insert into control—tb values (WS—line—ctr,c—rec.column—name);
END IF;
WS—RecNo := 1;
ELSE
IF c_rec.data_type = ‘DATE’ THEN
insert into control—tb values (WS—line—ctr,‘,’∥c—rec.column—name∥
‘DATE “MM/DD/YYYY HH24:MI:SS”’);
ELSE
insert into control—tb values (WS—line—ctr,‘,’∥c—rec.column—name);
END IF;
END IF;
WS—line—ctr := WS—line—ctr + 1;
END LOOP;
insert into control—tb values (WS—line—ctr,‘)’);
EXCEPTION
WHEN NO—DATA—FOUND THEN
DBMS—OUTPUT.PUT—LINE(‘The table does not exist:’);
END;
/
show errors
p—exec (PL/SQL Stored Procedure)
CREATE OR REPLACE PROCEDURE p—exec (IN—String IN varchar2)
AS
c—cursor INTEGER;
WS—ret INTEGER;
BEGIN
c—cursor := DBMS—SQL.OPEN—CURSOR;
DBMS—SQL.PARSE(c—cursor, IN—String, DBMS—SQL.V7);
WS ret := DBMS—SQL.EXECUTE(c—cursor);
DBMS—SQL.CLOSE—CURSOR(c—cursor);
END;
/
sp—kill—users—p (PL/SQL Stored Procedure)
CREATE OR REPLACE PROCEDURE sp—kill—users—01
AS
ws—sid	number(22);
ws—serial#	number(22);
ws—stmt	varchar2(100);
ora—err—code	BINARY—INTEGER;
CURSOR c—current—sessions IS
SELECT sid
,serial#
FROM sys.v$session
WHERE username = ‘SPSSUSER’
AND status <> ‘KILLED’;
BEGIN
ora—err—code := 0;
IF NOT c—current—sessions%ISOPEN THEN
OPEN c—current—sessions;
END IF;
LOOP
FETCH c—current—sessions INTO ws—sid
	,ws—serial#;
IF c—current—sessions%NOTFOUND THEN
IF (c—current—sessions%ROWCOUNT = 0) THEN
RAISE NO—DATA—FOUND;
END IF;
CLOSE c—current—sessions;
EXIT;
END IF;
ws—stmt := ‘ALTER SYSTEM KILL SESSION ’“∥ws—sid∥‘,’∥ws—serial#∥’”;
p—exec (ws—stmt);
END LOOP;
CLOSE c—current—sessions;
EXCEPTION
WHEN OTHERS THEN
ora—err—code := SQLCODE;
/* CLOSE c—current—sessions; causes invalid cursor error*/
END sp—kill—users—01;
/
show errors
get—direct—load (PL/SQL)
SET ECHO OFF
REM Procedure Name: get—direct—load
REM Description: Procedure creates a list of files with direct load status
VARIABLE T—Name varchar2(30)
SET SERVEROUTPUT OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET TERMOUT OFF
SET FLUSH OFF
ACCEPT T—Name char
SET PAGES 0
SET HEADING OFF
SPOOL/files6/ipsa/data—loads/&T—Name..dirload
select index—name from dba—indexes where table—name = UPPER(‘&T—Name’)
AND status = ‘DIRECT LOAD’;
SPOOL OFF
SET TERMOUT ON
SET SERVEROUTPUT OFF
SET VERIFY ON
SET FEEDBACK ON
SET ECHO ON

Data Loading Parameter Reporting

[0740] FIG. 13 illustrates an exemplary report indicating required table space allotments in a database. FIG. 14 depicts an exemplary report which lists statistics relating to the data in the database. FIG. 15 is an exemplary report indicating times to load various amounts of data. See the section entitled “REPORTING” below for more information about creating and using such reports.

Data Loading Archive Cleaner

[0741] The script clean_—archives is used to delete files from the archive directory used by the data loading script data_—loader. The script can use the Perl Module DataManip to compute the date stamp of the files that are to be deleted. This module can be obtained from the Perl web site (http://www.perl.com/pace/pub). The files put in the archive directory by the data loading script data_—loader will have a date stamp in the file name. This script will compute a date x days ago. It will then delete all the files in the archive directory that match that date. Depending on the frequency of data loading, the date stamp may include a time stamp as well. It may be more feasible to delete the archived files that match the day and not the time.

Configuration

Variables

• • [0742] $Archive_—dir (this variable will represent the location where the archive files are located)
  • [0743] $Days (this variable will represent the number of days ago to compute, default is seven)
    Executing clean_—archives
    clean_—archives [days]
  • [0744] If a number days is a parameter, it will computer days number of days ago and delete those files
  • [0745] If no date is passed, the default of seven days is used
    Procedure

[0746] The following is the procedure the clean_—archives script follows to remove files from the archive directory:

[0747]

TABLE 12
Step	Activity/Action
1	Read in and set days parameter, if necessary OR set days to the
	default
2	Computer the date to be deleted
3	Open the archive directory and read in the file names
4	For each files name, check to see if the date stamp on that file
	matches the date computed in step 2
	If it matches, delete the file

Purge Records Script

[0748] The script purge_—records is used to delete records from a Database. The script runs a PL/SQL script that deletes records from tables after a certain length of time. This script can be run from the cron or from the command line. The script performs error checking and reports via email to a specified administrator of success or failure. This script may be written in Perl.

Configuration

Environment Variables

[0749] Environment variables relevant to the Database need to be set. Specifically, the following environment variables need to be set: DBSE_—SID, DBSE_—HOME DBSE_—BASE, HOME, and PATH (to include the location of the dbse bin directory and the directory that holds the scripts used by this procedure). To configure these variables, look for the section in the code entitled setting environment variables. Set the values of $SID, $Home, and $Base. Verify that the path is correct when setting the PATH environment variable.

Directory Structure

[0750] A directory structure should be set up to house the files generated by purge_—records. See section 0 for a list and description of these files.

[0751] File Names

[0752] The files used/created by the purge_—records script should have a standardized naming format. The log file generated by the purge_—records script will be named purge.log. This file will hold a months worth of log entries. At the first day of the month, it will be renamed purge.logdate_—stamp. The current format of the $Date_—stamp is MonYYYY, however $Date_—stamp can be changed if more frequent archiving of the log file is necessary.

[0753] The file generated by the SQL Script is called purge.sql.log. This file is the spooled output of the SQL Script. It will be checked for errors/exceptions. If none are found, it is deleted. If there are errors found, it is renamed purge.sql.log.date_—stamp with the date stamp format as described above.

SQL Script

[0754] This script uses a SQL script called purge_—records. The scripts should be located in its own directory (following the OFA directory structure). Set $SQL_—dir to this directory location. The SQL script used bypurge_—records is as follows:

[0755]

	TABLE 13
	Script
	Name	Type	Input	Output	Functionality
	purge—	script	none	purge.sql.log	Deletes rows
	records				of tables that
					are past the
					specified date
					(section 0)

Output Files
Files Generated

• • [0756] purge.sql.log (log file generated by SQL scriptpurge_—records)
  • [0757] purge.log (log files generated by this script)
    Messaging

[0758] The purge_—records script will send email to an administrator to report success or failure of the purging process. Set $Mailer to the text string that will execute the mailer in command line mode. Set $DBA to the email address of the administrator.

Users

[0759] The purge_—records script will need to log on to SQL*Plus to execute the SQL script purge_—records. A variable name should be set to the schema that owns the tables that are going to be purged. Do not hard-code the schema password in the script. Instead use getpass.

Executing Purge_—records

[0760] The purge_—records script does not accept any parameters. It can be run from the command line or from the cron. To execute the script, type purge_—records.

Procedure

[0761] The following is the procedure the purge_—records script follows to load data into a database.

[0762]

TABLE 14
Step	Activity/Action
1	Set database environment
	Set directory structure variables
	Set SQL Script directory
	Set log file name variables
2	Create a new log file purge.log if one does not exist, or open an
	existing purge.log for appending
3	Set the mailer program and administrator's email address
4	Set the date stamp for load
	For the schema owner, set the user name and get password using
	getpass
	Set the fail flag to false (0)
5	Log onto SQL*Plus
	Execute the SQL script purge_records
6	Check to see if a the SQL script generated a log file
	If so, scan the log file for errors. If errors are found set the fail
	flag to true. Print to the log file the number of rows that were
	deleted
	If the log file does not exist, the script did not run correctly. Set
	the fail flag to true.
7	Check the value of the failure flag (it will be set to true if any
	errors occurred at any time during the process above)
	If the flag is false the purge was successful. Delete the log file
	generated by the SQL script and send an email report to the
	administrator
	If the flag is true the purge was unsuccessful. Rename the file
	generated by the SQL script to include the date stamp and send an
	email report to the administrator
8	If it is the first day of the month, rename purge.log to include
	the date stamp.
	Otherwise print the date/time to the log and close it

Scripts

[0763] The following is the scripts reference above are used by purge_—records. The bolded lines should be configured to the particular system you are using.

[0764]

getpass (Born Shell)
#!/bin/sh
#---------------------------------------------------------#
# Script—name: get—pass	#
#	#
# Description: This script will be used to retrieve the	#
#	appropriate password from the .password	#
#	file. It can be used from the command	#
#	line to retrieve a password or from a	#
#	shell script to eliminate hard coding of #
#	passwords. The .password file is located	#
#	in $DBSE—HOME, with the executable	#
#	located in $DBSE—HOME/bin.	#
#	#
# Dependencies: getpass requires one file,.password	#
#	#
#	SID—FILE: Contains a list passwords for  #
#	system, sys, and dbse.	#
#	#
# Command syntax: getpass USERNAME	#
#---------------------------------------------------------#
# what shell do we use?
SHELL=/usr/bin/sh
# where is our home?
DBSE—HOME=/files0/ipsa/vendor/dbse/product/7.3.4; export DBSE—HOME
# what path do we look for?
PATH=/usr/bin:/bin:${DBSE—HOME}/bin; export PATH
# who are we retrieving the password for
if[ $1 ]
then
USER=$1
else
echo “ ”
echo “USAGE: getpass ‘username’”
echo “ ”
exit 1
fi
# make sure the .password file exist
if [ ! -s $DBSE—HOME/.password ]
then
echo “ ”
echo “ERROR: this machine does not appear to have a password file”
echo “ ”
exit 1
fi
# get the appropriate password
PASSWORD=‘cat $DBSE—HOME/.password|grep -i “${USER} ”|awk ‘{print $2}’’
BAD—SIDS=‘cat $DBSE—HOME/.password|grep -i “${USER} ”|awk ‘{print $3}’’
OLD—PASS=‘cat $DBSE—HOME/.password|grep -i “${USER} ”|awk ‘{print $4}’’
# if a password was found, print it to the screen
if[ $PASSWORD ]
then
echo “$ {PASSWORD}”
# print a message if passwords appear to not be synced between databases
if [ $BAD—SIDS ]
then
BAD—SIDS=‘echo ${BAD—SIDS}|cut -c2-100’
echo “ ”
echo “WARNING:\tThe password for ${USER} may not be synchronized”
echo “\t\tbetween all databases. The database(s) ${BAD SIDS} appear(s)”
echo “\t\tto use the old password ‘${OLDPASS}’”
echo “ ”
fi
else
echo “ ”
echo “ERROR: ${USER} is not a supported username”
echo “ ”
exit 1
fi
purge—records (SQL Script)
SET ECHO ON
SET FEEDBACK ON
SET FLUSH OFF
SET HEADING OFF
SET SERVEROUTPUT ON
SET TERMOUT OFF
SET VERIFY OFF
SPOOL/files6/ipsa/purge/purge.sql.log
REM *Purge records in PERF—FACT—TB table
delete from PERF—FACT—TB where PERF—FACT—TB.PERF—TIME—KEY—CD
in
(select PERF—METRIC—TIME—TB.PERF—TIME—KEY—CD
from PERF—METRIC—TIME—TB
where
to—char(PERF—METRIC—TIME—TB.PERF—DT, ‘MM/DD/YYYY’)
<=(select to—char(sysdate - 40, ‘MM/DD/YYYY’) from dual));
REM *Purge records in EVENTS—FACT—TB table
delete from EVENTS—FACT—TB where
EVENTS—FACT—TB.PERF—TIME—KEY—CD in
(select PERF—METRIC—TIME—TB.PERF—TIME—KEY—CD
from PERF—METRIC—TIME—TB
where
to—char(PERF—METRIC—TIME—TB.PERF—DT ‘MM/DD/YYYY’)
<=(select to—char(sysdate - 40, ‘MM/DD/YYYY’) from dual));
REM *Purge records in PERF—FACT—DLY—TB table
delete from PERF—FACT—DLY—TB where
PERF—FACT—DLY—TB.PERF—TIME—KEY—CD in
(select PERF—METRIC—TIME—TB.PERF—TIME—KEY—CD
from PERF—METRIC—TIME—TB
where
to—char(PERF—METRIC—TIME—TB.PERF—DT, ‘MM/DD/YYYY’)
<=(select to char(sysdate - 397, ‘MM/DD/YYYY’) from dual));
SPOOL OFF

Database Table Data Extractor

[0765] The script table_—extract is used to extract data from a Database table and write it to a flat file in a comma delimitated format. The purpose of this script is to extract the data from non-fact tables. Therefore, there not all tables can be extracted. The script can be configured to extract data from as many tables as required. The script may be written in Perl.

Configuration

Environment Variables

[0766] Environment variables relevant to the Dbse Database need to be set. Specifically, the following environment variables need to be set: DBSE_—SID, DBSE_—HOME, DBSE_—BASE, HOME, and PATH (to include the location of the dbse bin directory and the directory that holds the scripts used by this procedure). To configure these variables, look for the section in the code entitled setting environment variables. Set the values of $SID, $Home, and $DBase. Verify that the path is correct when setting the PATH environment variable.

Users

[0767] The table_—extract script will need to log on to SQL*Plus to execute the SQL script table_—extract. A variable name should be set to the schema that owns the tables that are going to be extracted. Do not hard-code the schema password in the script. Instead use getpass (see below).

PL/SQL

[0768] For every table that is to be extracted, a corresponding PL/SQL select statement is required. Determine which columns should be extracted and what, if any, filters are applicable.

• [0769] d. utput Files
  Files Generated
  • [0770] table_—name.dat (contains data returned from the select statement for table_—name in a comm a delimitated format)
    Executing table_—extract
    table_—extract table_—name output_—directory
  • [0771] Extract data from table_—name
  • [0772] Put file table_—name.dat into output_—directory
    Procedure

[0773] The following is the procedure the table_—extract script follows to extract data from a database into a text file.

[0774]

TABLE 15
Step	Activity/Action
1	Read in and set parameters (table—name and output—directory)
	Set database environment
2	For each user, set user name and get password using getpass
3	Log onto SQL*Plus
	Set SQL system variables
	Set Spool to be output—directory/table—name.dat
4	Execute the correct select statement for table—name, if non exist
	print out an error
5	Log off SQL*Plus
6	The output file table—name.dat will now be in a format that has
	extra spaces between fields.
	Open the file and remove all extra white space
	Rewrite the file in the correct format

Scripts

[0775] The following is the script reference above this is used by purge_—records. The bolded lines should be configured to the particular system you are using.

[0776]

getpass (Born Shell)
#!/bin/sh
#---------------------------------------------------------#
# Script—name: get—pass	#
#	#
# Description: This script will be used to retrieve the	#
#	appropriate password from the .password	#
#	file. It can be used from the command	#
#	line to retrieve a password or from a	#
#	shell script to eliminate hard coding of #
#	passwords. The .password file is located	#
#	in $DBSE—HOME, with the executable	#
#	located in $DBSE—HOME/bin.	#
#	#
# Dependencies: getpass requires one file,.password	#
#	#
#	SID—FILE: Contains a list passwords for  #
#	system, sys, and dbse.	#
#	#
# Command syntax: getpass USERNAME	#
#---------------------------------------------------------#
# what shell do we use?
SHELL=/usr/bin/sh
# where is our home?
DBSE—HOME=/files0/ipsa/vendor/dbse/product/7.3.4; export DBSE—HOME
# what path do we look for?
PATH=/usr/bin:/bin:${DBSE—HOME}/bin; export PATH
# who are we retrieving the password for
if[ $1 ]
then
USER=$1
else
echo “ ”
echo “USAGE: getpass ‘username’”
echo “ ”
exit 1
fi
# make sure the .password file exist
if [ ! -s $DBSE—HOME/.password ]
then
echo “ ”
echo “ERROR: this machine does not appear to have a password file”
echo “ ”
exit 1
fi
# get the appropriate password
PASSWORD=‘cat $DBSE—HOME/.password|grep -i “${USER} ”|awk ‘{print $2}’’
BAD—SIDS=‘cat $DBSE—HOME/.password|grep -i “${USER} ”|awk ‘{print $3}’’
OLD—PASS=‘cat $DBSE—HOME/.password|grep -i “${USER} ”|awk ‘{print $4}’’
# if a password was found, print it to the screen
if[ $PASSWORD ]
then
echo “$ {PASSWORD}”
# print a message if passwords appear to not be synced between databases
if [ $BAD—SIDS ]
then
BAD—SIDS=‘echo ${BAD—SIDS}|cut -c2-100’
echo “ ”
echo “WARNING:\tThe password for ${USER} may not be synchronized”
echo “\t\tbetween all databases. The database(s) ${BAD SIDS} appear(s)”
echo “\t\tto use the old password ‘${OLDPASS}’”
echo “ ”
fi
else
echo “ ”
echo “ERROR: ${USER} is not a supported username”
echo “ ”
exit 1
fi

[0777] FIG. 16 depicts an embodiment of the present invention which graphically conveys availability in a network with service assurance capabilities. In operation 1600, report parameters are selected relating to availability of monitored elements, services, and processes of a network. A database is polled in operation 1602 for data that matches the report parameters. A graph is generated in operation 1604 from the data that matches the report parameters. In operation 1606, the generated graph is displayed to graphically represent the monitored elements, services, and processes of the network.

[0778] In one aspect of the present invention, the step of selecting the report parameters further includes the steps of selecting at least one metric class parameter relating to the activity of the network, selecting a graph type parameter, and selecting at least one report criteria parameter relating to the activity of the network. The generated graph may be of the selected graph type parameter.

[0779] In another aspect of the present invention, a selectable list of metrics parameters, a selectable list of graph type parameters, and a selectable list of report criteria may be displayed. Further, the graph type parameter may be dependant upon the selected metric class parameter. The report criteria parameter may also be dependant upon the selected graph type parameter.

[0780] In one embodiment, the graph type parameter may be a line graph, a bar graph, a boxplot graph, and/or a spectrum graph. In addition, the graph may displayed via a browser.

[0781] More details follow.

IPSA Batch and Ad hoc Reporting

[0782] This section provides an overview of the IPSA reporting process. It sequencially covers the procedures for implementing both batch reporting and web-based ad hoc reporting. The following files and directories are used:

• [0783] IPSA_—StartSPSS.cmd Located anywhere, it is used to start the reporting process (see Configuration). Instantiates two SPSS processes (spssw.exe, spsswin.exe) and IPSA_—SPPS.exe
• [0784] <SABATCH>Batch queue file location, as specified in the IPSA_—StartSPSS.cmd file (see Configuration).
• [0785] IPSA_—BatchQueue.txt Queue file that, if present, is passed to SPSS and contains batch report specifications. This file is deleted after being read by IPSA_—SPSS.exe. See Adhoc.doc and graphs.doc for content explanations.
• [0786] <SAADHOC>Adhoc queue file location, as specified in
• [0787] <SARPHME>\InputQueue\IPSA_—Reporting_—Config.txt (see Configuration).
• [0788] IPSA_—AdhocQueue.txt Queue file passed to SPSS that contains ad hoc report parameters. This file is created by the web interface (<WW ROOT>\cgi-bin\adhoc.pl) and deleted after use by IPSA_—SPSS.exe. See Adhoc.doc and graphs.doc for content explanations.
  • [0789] IPSA_—AdHocQueue Working <instance>.txt

[0790] Adhoc working file with a unique IPSA_—SPSS.exe instance number, <instance>, that is defined in IPSA_—StartSPSS.cmd (see Configuration).

• [0791] IPSA_—ProcessTheBatchQueue.txt The presence of this file notifies IPSA_—SPSS.exe to read <SABATCH>\IPSA_—BatchQueue.txt and pass it to SPSS. This file is polled for every five seconds.
• [0792] <SARPHME> The reporting home directory, as specified in the IPSA_—StartSPSS.cmd file (see Configuration). All files needed to generate reports are located within this directory.
• [0793] \InputQueue IPSA_—SPSS.exe configuration and control files.
  • [0794] IPSA\_—Reporting_—Config.txt
• [0795] IPSA_—SPSS.exe configuration file. Includes database location and login information, output directories and image format specification for the generated graphs (see Configuration).
• [0796] IPSA_—ControlQueue.txt Contains commands to be issued to IPSA_—SPSS.exe during execution. Used for terminating the process.
• [0797] \Logs Log files generated from IPSA_—SPSS.exe execution
  • [0798] IPSA_—VB_—SPSS.log Error messages from program execution.
• [0799] \SPSS All SPSS related files.
• [0800] \Macros SPSS macros
• [0801] !incdef.SPS
  • [0802] !bargrph.SPS bar graph
• [0803] !bxgrph.SPS boxplot
  • [0804] !spect.SPS spectrum graph
• [0805] !xygrph.SPS xy line graph
• [0806] \Templates SPSS chart looks
• [0807] avail.clo availabilty graph
  • [0808] bar.clo bar graph
• [0809] box.clo boxplot
• [0810] exception.clo exception graph
• [0811] line.clo xy line graph
• [0812] \Working Data Files SPSS macros use this directory for temporary files.
• [0813] \Web\HtmlTemplates HTML fragments for use to dynamically produce HTML
  • [0814] todays_—urls_—head_—template.txt batch TOC header
  • [0815] todays_—urls_—detail_—template.txt batch TOC report title
  • [0816] todays_—urls_—tail_—template.txt batch TOC footer
  • [0817] detail_—urls_—head_—template.txt batch/adhoc list-of-reports header
  • [0818] detail_—urlsdetail_—template.txt batch/adhoc list-of-reports details _—detail_—urls_—tail_—template.txt batch/adhoc list-of-reports footer
• [0819] <SAWEB> The report output home directory, as specified in <SARPHME>\InputQueue\IPSA_—Reporting_—Config.txt (see Configuration).
• [0820] \Adhoc\<date>_—INFO Adhoc reports issued on the date <date> where <date> is of the form YYYYMMDD. This directory is created by the report generation script.
  • [0821] <adhoc_—report title><date><unique id>.html the generated adhoc report
• [0822] \Batch\Daily Daily batch reports home directory
• [0823] <date>_—INFO Generated batch reports. <date> is of the form YYYYMMDD, but is the date prior to the date when the report request was issued. This directory is created by the report generation script.
• [0824] \Images Images for the HTML pages
  • [0825] Graph1.gif list bullet
  • [0826] SA1.gif Service Assurance logo
• [0827] WEB ROOT> Root directory of the web server where the reports will be housed.
• [0828] bground.gif background image for the adhoc web pages
• [0829] \cgi-bin Adhoc files location. This directory must be accessible from a web browser via HTTP and have “execute” permission assigned to it.
• [0830] adhoc.pl Perl script used to generate ad hoc reports. This file is accessed via HTTP from a web browser to start the ad hoc report data gathering process.
• [0831] adhoc.setup.txt Setup and configuration file for the adhoc.pl script. Contains report types and descriptions.
  Batch Request
  Overview

[0832] To enable report generation, execute IPSA_—StartSPSS.cmd. Both SPSS and a VB file-polling application are invoked in the form of IPSA_—SPSS.exe, sppssw.exe, and spsswin.exe. These are processes that can be monitored using Window NT Task Manager. With report criteria specified in <SABATCH>IPSA_—BatchQueue.txt, create the file <SAADHOC>IPSA_—ProcessTheBatchQueue.txt. The contents of this file are unimportant, as only its presence is polled for. IPSA_—SPSS.exe reads the contents of IPSA_—BatchQueue.txt and passes it to SPSS. Graphs are generated in <SAWEB>\Batch\Daily\as well as an HTML file that points to the images.

[0833] Multiple graphs will be generated if, for example, 100 elements are reported on and the maximum number of elements per axis has been set to 10. In this case, 10 graphs would be generated and the HTML file would link to each of them. To terminate the application, alter <SARPHME>\InputQueue\IPSA_—ControlQueue.txt as stated in the “Controlling IPSA_—SPSS during execution” section of this document.

Installation

[0834] Run SetupVisualBasicRuntime\Setup.exe to install needed Visual Basic runtime components to your system. The installation directory that you choose must be specified in IPSA_—StartSPSS.cmd if you plan to use the command file.

Configuration

[0835] IPSA_—StartSPSS.cmd is often used to start the reporting process. This command file changes to the IPSA installation directory and executes IPSA_—SPSS.exe, passing three modifyable parameters:

• • [0836] SARPHME: <SARPHME>, reporting home directory
  • [0837] SABATCH: <SABATCH>, location of batch input queue
  • [0838] INSTNCE: <instance>, the unique instance of this IPSA_—SPSS.exe process
• [0839] <SARPHME>\InputQueue\IPSA_—Reporting_—Config.txt contains reporting and environment configuration for both the VB and SPSS applications: @{
• [0840] SADSN=ipsa01 SAUID=spssuser
• [0841] SAPWD=spssuser SASRVER=IPSA01
• [0842] SAWEB=“C:\Inetpub\WWWroot”
• [0843] SAWEBRL=“/web_—reports”
• [0844] SAADHOC=“c:\Data\Reporting\InputQueue”
• [0845] DBESCPE=\MAXAXIS=10 GRPHOUT=“JPEG File”
• [0846] }@
• [0847] SADSN: DSN for the database
• [0848] SAUID: database user id
• [0849] SAPWD: database password
• [0850] SASRVER: database server
• [0851] DBESCPE: escape character
• [0852] SAWEB: the root directory of the web server (e.g. c:\InetPub\WWWroot).
• [0853] SAWEBRL: report output directory, relative to <WEB ROOT> (e.g. if the reports will be in c:\InetPub\WWWroot\web_—reports, use SAWEBRL=“/web_—reports”).
• [0854] SAADHOC: directory location of the IPSA_—AdhocQueue.txt file
• [0855] MAXAXIS: maximum number of objects that can exist on any single axis.
• [0856] GRPHOUT: type of graph image to be produced (valid values are “JPEG File” or “Windows Bitmap”)
  Set-Up Request

[0857] Update <SABATCH>IPSA_—BatchQueue.txt with your report criteria. See adhoc.doc for content specification. This file is read by IPSA_—SPSS.exe and the contents sent to SPSS to designate what data is pulled from the database and reported upon. If allfour of the following fields are not specified, defaults are used for allfour.

• [0858] STRTYR, STRTMON, STRTDAY: default is all day yesterday
• [0859] NUMDAYS: default is 1 day

[0860] In addition, the wildcards “*” and “?” may be used when specifying element names and locations:

• [0861] ELMNM “*” for multi-char
• [0862] ELMLOC=“?” for one-char
  Start SPSS

[0863] Run IPSA_—Start_—SPSS.cmd to start IPSA_—SPSS.exe and instantiate SPSS. Run Windows Task Manager (taskmgr.exe) from Start/Run . . . to verify that the following Processes are running: IPSA_—SPSS.exe, spssw.exe, and spsswin.exe.

[0864] note: An enhancement would be to have IPSA_—SPSS.exe run as a Windows NT Service.

Propagate the Action

[0865] The presence of <SAADHOC>\IPSA_—ProcessTheBatchQueue.txt will notify the IPSA_—SPSS.exe to read <SABATCH>\IPSA_—BatchQueue.txt and pass the report parameters to SPSS (see the next section, “IPSA_—SPSS Operation”). The content of this file does not matter, as only its presence is checked for. This file is deleted when IPSA_—SPSS.exe has processed it.

[0866] note: This is problematic when multiple instances of IPSA_—SPSS.exe exist and all are expected to wait for this file to be created. A possible solution is to have multiple copies of IPSA_—ProcessTheBatchQueue.txt located in separate directories, each being accessed by a single IPSA\-SPSS.exe process.

IPSA_—SPPS Operation

[0867] This process polls for the existence of

• [0868] <SAADHOC>\IPSA_—ProcessTheBatchQueue.txt. Once found, <SABATCH>\IPSA_—BatchQueue.txt is read and its values passed to SPSS for processing.

[0869] The batch queue could be separate for each instance of this process.

[0870] Two places that all instances must access are (1) Ad-hoc queue and (2), web documents dir.

[0871] Controlling IPSA_—SPSS during execution

[0872] Commands may be issued to IPSA_—SPSS.exe by creating <SARPHME>\InputQueue\IPSA_—ControlQueue.txt and entering commands into it. IPSA_—SPSS.exe reads this file (if it exists) and executes the commands contained within.

[0873] For example, the following file contents will terminate the SPSS and IPSA_—SPSS.exe processes:

• [0874] @{
• [0875] SAQUIT=yes
• [0876] }@

[0877] If this method is not used, the processes must be killed using Windows Task Manager (see 3.4). IPSA_—ControlQueue.txt is deleted after use.

[0878] note: Currently the only command that IPSA_—SPSS recognizes is “SAQUIT yes” (see above).

[0879] note: This file currently resides in <SARPHME>\InputQueue\but can be reconfigured to exist in the queue directory for multiple instances of IPSA_—SPSS.exe.

Output

[0880] A table of contents for the generated graphs is created in <SAWEB>\Batch\Daily<date>_—INFO, as well as the graph image files. This table of contents is viewable in a web browser.

• [0881] <SARPHME>\Logs\will contain a log file named IPSA_—VB_—SPSS.log if any errors are encountered.
  Updating the Setup program

[0882] From the Visual Basic IDE, open the IPSA_—SPSS.VBP project file. Make any needed code changes, then create IPSA_—SPSS.EXE from the File menu. Close the IDE. From the Visual Basic program group, start the Application Setup Wizard. Open the IPSA_—VB_—SPSS.SWT template file and follow the prompts to create the setup program, answering “no” to all questions related to “remote automation components”. When prompted, choose a destination output directory and create the setup files.

[0883] Once the setup program has been created, edit the SETUP.LST file in the destination output directory that you chose. Towards the end of the text, change all solitary occurrences of “SPSS” to “IPSA” (see below). Save and close the file when completed.

• [0884] File9=1,,IPSA_—SPSS.ex_—,IPSA_—SPSS.exe,$(AppPath),,,Sep. 29, 1999 16:14:09,153088,1.0.0.0,“ ”,“IPSA”,“$(AppPath)\IPSA_—SPSS.exe”
  [Setup]
• [0885] Title=IPSA
• [0886] DefProgramGroup=IPSA
• [0887] DefaultDir=$(ProgramFiles)\IPSA
• [0888] Setup=setup1.exe
• [0889] AppExe=IPSA_—SPSS.exe
• [0890] AppToUninstall=IPSA_—SPSS.exe
• [0891] AppPath=
  Ad hoc Request
  Overview

[0892] The ad hoc reporting works in a manner practically identical to the batch reporting. A queue file is created with report parameters contained within it and IPSA_—SPSS.exe polls for its presence. Once found, the report parameters are passed to SPSS, where the graphs are created. The only real difference is that the queue file is not created by hand, it is generated from a web-based interface. The presence of the IPSA_—AdhocQueue.txt file is the trigger for the report generation. This is analogous to the IPSA_—ProcessTheBatchFile.txt file from batch reporting.

Requirements

• • [0893] HTML 3.2 compliant web browser (e.g. Netscape Navigator 3.0 or higher, MS Internet Explorer 4.0 or higher).
  • [0894] adhoc.pl file located in the cgi-bin directory of a web server
  • [0895] adhoc.setup.txt file located in the same directory as adhoc.pl (above)
    Navigation From the main Ad hoc screen 1700 located at http://<web_—server>/cgi-bin/adhoc.pl, choose a metric class from the selection list and click Next 1702 (see FIG. 17A). If multiple graph types 1710 are available for the chosen metric, they are displayed next (see FIG. 17). After choosing a graph type, enter report criteria into the form (see FIG. 18). Exemplary fields include Title 1800, Element 1802, Location, etc. Specify multiple elements 1804 or locations 1806 by either typing their name in the entry field or selecting them from the drop-down list and clicking “Add”. Acceptable wildcards are “*” for “all” and “?” for “zero or one”. Choose multiple metrics by holding the CTRL key down while selecting each desired metric.

[0896] Click on Submit to create or update <SAADHOC>\IPSA_—AdhocQueue.txt. IPSA_—SPSS.exe reads its contents and pass the parameters to SPSS. The generated graphs are created in <SAWEB>\Adhoc<date>_—INFO and returned to the user via the browser. Upon graph generation, IPSA\-AdhocQueue.txt is deleted.

Reporting Requirements Phase 2

[0897] The tables shown in FIGS. 19–22 detail the Batch Reports recommended, the eight generic report types, and the Adhoc requirements. The remainder of this section references these matrices several times. In particular, FIG. 19 illustrates exemplary batch reporting requirements. FIG. 20 lists some of the generic report types. FIG. 21 shows exemplary ad hoc reporting requirements. FIG. 22 illustrates sample metric information.

[0898] FIGS. 19–22 provide historical record of collected performance data and network events. Exception reporting is limited to views of events that occurred in real-time and does not include finding exceptions in the historical data by analyzing past data.

Batch Reporting

General Information (Example)

• • [0899] Daily batch report processing will be scheduled to occur at 1:00 am using Windows NT WinAT to execute SPSS reporting scripts.
  • [0900] Daily batch processing will include both performance and event reporting operations.
  • [0901] Examples of the eight generic report types referenced in this document and in FIGS. 19–22 can be found in FIGS. 23–30.

[0902] Daily performance reporting processing tasks

• • [0903] Import all ECM and Patroller comma-separated-value files into DBSE using. SQL Loader. The data files will be moved to the data repository (i.e. data_—feeds directory) by either the Patroller History Propagator or the Solaris cron facility (for ECM files).
  • [0904] Generate and publish (save) the 34 html-based report files listed in the matrix shown in FIGS. 19–22. The reports will be published to the documents directory of the web server.
  • [0905] Calculate daily roll-up statistics for each different metric collected and store the rolled-up statistics.
  • [0906] Delete expired daily detail data (metric records older than 40 days months).
  • [0907] Delete expired rolled-up data (rolled-up records older than 13 months).

[0908] Daily event reporting processing tasks

• • [0909] Analyze all availability-type events. These events include any event specified as an event signifying an outage. An event will be received when an object goes down and comes back up. These event pairs must be located, and an outage duration calculated.
  • [0910] Using the result of the above analysis, record an event for each day of the outage with an accurate daily outage duration.
  • [0911] Generate and publish the 4 exception reports listed in FIGS. 19–22.
  • [0912] Generate and publish the Network Availability, Process Availability and Service Availability reports listed in FIGS. 19–22.
  • [0913] Delete expired events data (event records older than 13 months).
  • [0914] Build and publish a daily web page with links to the 6 htrnl event reports.

[0915] Other daily processing tasks (applies to both event and performance data)

• • [0916] Build and publish a daily web page with links to the 34 performance reports, the 4 exception reports, and the 2 availability reports. This page, and all batch reports must be available by 6 am the next day.
  • [0917] Delete expired html report files (older than 7 days)
    Ad-Hoc Reporting

[0918] A web-based ad-hoc capability is required to produce reports of the same eight generic report types as the batch reports.

[0919] The adhoc utility should present a menu hierarchy as shown in the Adhoc Reports table of FIG. 21. In this table, the expressions <element name>, <element location> and <instance> can be input by the user in the following forms:

• • [0920] A single name, location, or instance (i.e. “nsmmrt03”)
  • [0921] A list of names, locations, or instances (i.e. “nsmmrt03,nsmmrt04”)
  • [0922] A variation of either a single name or list of names using * as a multi-character wildcard.
  • [0923] A variation of either a single name or list of names using? as a single-character wildcard.

[0924] Once the online menu/form is completed by the user, a “Submit” button should execute a database query with the selected choices. The “Submit” button should also validate any data that is entered if applicable, for proper format (not for valid content, however).

[0925] The query should be able access detail and rolled-up data as recent as midnight the previous day. The previous day's detail and rolled-up data should be available for adhoc queries after 6 am the next day.

Report Type Examples

[0926] FIGS. 23–30 show simple examples of each of the eight recommended report types.

Graph Explanations and Requirements

[0927] This section is an overview of what the graphs, such as those found in FIGS. 23–30, are and what they do. There will be some tips as to how to change certain properties if needed, how to run the graphs from the script, and other useful information.

Graphs

[0928] Running graphs from the script

[0929] To run the graph from the script the script (macro) open and the script that passes in the parameters. The pairs that go together are:

• • [0930] !xygrph.sps & callxy.sps: xy-line graphs for daily details
  • [0931] !bargrph.sps & callbar.sps: bar graphs for daily availability
• [0932] !bxgrph.sps & callbx.sps: boxplots for daily comparisons and monthly trends
• [0933] !spect.sps & callsp.sps: spectrum graphs for event exceptions and daily availability (high level and detail)

[0934] To run these scripts (macros), first you need to have the macro window open (the file with the !name) and go under the menu bar Run> All. This will run a define on the macro. After the processor is done running the define, switch over to the call file (i.e. callxy.sps) and select only the area that has the parameters. (There are little headers in this file that describe what each parameter set will accomplish when sent to the macro) Then under the menu bar Run> Selection. This will then pass the parameters to the macro and produce the graph in an output window.

[0935] Exporting graphs manually from the output window

[0936] To export a graph using what is in the output window, first click on the graph to select the graph area. Go under the menu File>Export which will bring up a dialog box. Select in the drop down box if you wish to export charts only, the document as a whole, or the document (no charts). If you are just exporting a JPEG, charts only would be a good selection. If you wish to export a Windows Bitrnap (BMP) then select the document as a whole. This will export the HTML file and the BMP. (It is easier to view a BMP in a HTML file because the BMP file will launch a paint program just to view it) Then after deciding on what you are going to export, click on the options button next to the drop down box and here you can make selections as to size, resolution, etc. Those optimal settings will be detailed later for each graph. Click OK to get back to the main dialog box. Next set the path for the exported chart by clicking on Browse. The path to the web server is O:\ipsa\product development\reporting\Web\WWWRoot.

[0937] Select your path, click OK, and then export the document. It should then be able to be viewed on twmmnt02.

Chart Properties

[0938] To change the chart properties (on IGRAPHs) and set it to a CHARTLOOK, double click on the graph in the output viewer. This will activate it into interactive mode. Go up to Format>Chart Properties in the menu bar and make necessary changes in the tabbed dialog box. Some changes include: Text size (the title and subtitle text size seems not to take in the CHARTLOOK option when set here-FYI), Font, Symbol attributes (shape, color, fill, etc), Axis attributes (line width, tick marks), colors (for legends, shapes, etc), and other features that need to be changed. After changing what you want, click Apply to view changes and OK to exit the dialog box. Then go to the menu bar Format>Chartlook and click on Save As with the “as displayed” feature highlighted in the box to the left. Save the chartlook to a location that can be called from the macro. This chartlook should then set the settings for the next graphs run with the /CHARTLOOK=‘file’ subcommand in the IGRAPH syntax.

XY-Line Graph (See FIGS. 23–24)

[0939] What it shows

[0940] The xy-line graph reports the daily details for CPU Utilization (for routers, Unix, and NT),Interface Utilization and Response Time for routers, Memory Utilization (for Unix and NT), Network I/O (for Unix and NT), Memory Utilization (Unix and NT), and eventually Reponse time for FTP, SMTP, NNTP, and HTTP.

Parameters

[0941] In the parameter call:

• [0942] SET MPRINT ON.
• [0943] !XYGRPH ELMNM=nsmnmrt03 ELMTY=NT ELMLOC=Minneapolis
• [0944] METRCNM=CpuBusy_—yercent STRTYR=1999 STRTMON=06
• [0945] STRTDAY=30 NUMDAYS=1 SARPHME=“o:\IPSA\Product Development\Reporting”
• [0946] SADSN=“ipsa01” SAUID=“spssuser” SAPWD=“spssuser”
• [0947] SASRVER=“ipsa01”.

[0948] The possible variables to pass in are as follows:

• [0949] ELMNM (the element name) any elmnt_—nm located in the Network_—Elmnt_—Tb. With our test data we have these elements available: nsmmrt03, gfmmnt03, nsmmws16, nsmmrt04, nsmmrt05, nsmmrt06, and nsmmrt07.
• [0950] ELMTY (the element type) any elmnt_—type_—cd located in the Network_—Elmnt_—Tb. With our test data we have these types available: RT (router), UX (Unix) and NT (NT).
• [0951] ELMLOC (the element location) any elmnt_—city_—nm located in the Elnnt_—Loc_—Tb.
• [0952] METRCNM (the metric name) any metric_—nm located in the Perf Metric_—Tb. With our test data we have these metrics available: CpuBusy_—Percent, InterfaceUtilization_—Percent, InterfaceUtilization_—BPS, CpuUtilization_—Percent, MemoryFree\_—Pages, NetworkTraffic_—PPS, DiskBusy_—Percent, CpuProcessorTime_—Percent, MemoryFree_—Mbytes, and DiskTime_—Percent. Other metric names include FtpResponseTime_—Seconds, SmtpResponseTime_—Seconds, NntpResponseTime_—Seconds, and HttpResponseTime\-Seconds.
• [0953] STRTYR (the year) any perf_—yr located in the Perf_—Metric_—Time_—Tb.
• [0954] STRTMON (the month) any perf_—mon located in the Perf_—Metric_—Time_—Tb. With our test data we usually use 06 for the month of June. NOTE: Begin each one digit month with a zero!
• [0955] STRTDAY (the day) any perf_—day located in the Perf_—Metric_—Time_—Tb. With our test data we usually use 30 for the day. NOTE: Begin each one digit day with a zero!
• [0956] NUMDAYS (the number of days the data will include) any number valid (note that most graphs will be cluttered if too many days are spanned with the exception of a monthly detail graph set up specifically to handle that number of cases). For this DAILY graph, use the number 1.
• [0957] SARPHME (path) This sets up the path to where certain files (if needed) can be saved or referenced from.
• [0958] SADSN (DSN name) This specifies the DSN name used in the GET.
• [0959] SAUID (User ID name) This specifies the User ID name used in the GET.
• [0960] SAPWD (Password) This specifies the password used in the GET.
• [0961] SASRVER (Server Name) This specifies the server used in the GET.
  Manual Settings

[0962] For optimal output for the xy-line graph these settings should be made: (in the chart options in Export dialog box)

• [0963] Resolution: 200×200
• [0964] Size: 6×6 (or between 4×4 to 6×6)
• [0965] Format: YCC Color
• [0966] Quality: 100%
• [0967] Color Translation: Normal
• [0968] Operation: Progressive
  Chartlook

[0969] This graph calls the chartlook line.clo found in the O:\ipsa\product development\reporting\spss\templates directory.

Bar Graph (See FIG. 27.)

What it shows

[0970] The bar graph reports the daily availability for Network, Services, and Processes.

[0971] It calculates the percentage of the availability for all of the elements, representing them as bars with the percentage displayed within the bars.

Parameters

[0972] In the parameter call:

• [0973] SET MPRINT ON.
• [0974] !BARGRPH EVENT=“DNS Service Failed” ELMLOC=Minneapolis
• [0975] STRTYR=1999 STRTMON=06 STRTDAY=30 NUMDAYS=1
• [0976] SARPHME=“o:\IPSA\Product Development\Reporting”
• [0977] SADSN=“ipsa01” SAUID=“spssuser” SAPWD=“spssuser”
• [0978] SASRVER=“ipsa01”.

[0979] The possible variables to pass in are the same as the xy-line graph except for:

• [0980] EVENT (the event) any valid event_—string_—txt that is located in the Events_—Fact_—Tb.
  Manual Settings

[0981] For optimal output for the bar graph these settings should be made: (in the chart options in Export dialog box)

• [0982] Resolution: 200×200
• [0983] Size: 3×3
• [0984] Format: YCC Color
• [0985] Quality: 100%
• [0986] Color Translation: Normal
• [0987] Operation: Progressive
  Chartlook

[0988] This graph calls the chartlook bar.clo found in the O:\ipsa\product development\reporting\spss\templates directory.

Boxplot Graph (See FIGS. 25–26.)

[0989] What it shows

[0990] The boxplot graph reports daily comparisons and monthly trends.

Parameters

[0991] In the parameter call:

[0992] *************************************

[0993] DAILY COMPARISON BOXPLOT

[0994] ***********************************

• [0995] SET MPRINT ON.
• [0996] !BXGRPH ELMNM=(nsmmrt03 nsmmrt04 nsmmrt05) ELMTY=RT
  • [0997] ELMLOC=Minneapolis METRCNM=CpuBusy_—Percent
  • [0998] STRTYR=1999 STRTMON=06 STRTDAY=30 NUMDAYS=1 CODE=1
  • [0999] SARPHME=“o:\IPSA\Product Development\Reporting”
  • [1000] SADSN “ipsa01” SAUID=“spssuser” SAPWD=“spssuser”
  • [1001] SASRVER=“ipsa01”.

[1002] *******************************

[1003] MONTHLY TREND BOXPLOT

[1004] ******************************

• [1005] SET MPRINT ON.
• [1006] !BXGRPH ELMNM=(nsmmrt05) ELMLOC=Minneapolis METRCNM=CpuBusy_—Percent
  • [1007] STRTYR=1999 STRTMON=06 STRTDAY=01 NUMDAYS=30 CODE=2
  • [1008] SARPHME=“o:\IPSA\Product Development\Reporting”
  • [1009] SADSN=“ipsa01” SAUID=“spssuser” SAPWD=“spssuser”
  • [1010] SASRVER=“ipsa01”.

[1011] The possible variables to pass in are the same as the xy-line graph except for:

• [1012] ELMNM (element name) any valid elmnt_—nm from the Network_—Elmmt_—Tb. It is given one or more names in the daily comparison and only one name for the monthly trend.
  Manual Settings

[1013] For optimal output for the bar graph these settings should be made: (in the chart options in Export dialog box)

• [1014] Resolution: 200×200
• [1015] Size: 3×3
• [1016] Format: YCC Color
• [1017] Quality: 100%
• [1018] Color Translation: Normal
• [1019] Operation: Progressive

[1020] ***NOTE: The monthly boxplot can be exported with these settings, but will NOT turn out very well. This type of graph will not have readable fonts until (hopefully) the next version of SPSS.

Chartlook

[1021] This graph calls the chartlook box.clo found in the O:\ipsa\product development\reporting\spss\templates directory.

Exception Text Report (See FIG. 30)

Spectrum Graph (See FIGS. 28–29.)

[1022] What it shows

[1023] This can make three chart types: Daily exception spectrums (for routers, Unix, and NT), Daily Availability Spectrums (High Level overview) for ALL Network, Service, and Process events, and Daily Availability Spectrums (Low Level overview) for EACH event under Network, Service, and Process detailing the elements that the event occurred upon.

Parameters

[1024] In the parameter call:

[1025] ********************************************

[1026] DAILY EVENT EXCEPTION GRAPH:

[1027] for elements: RT, LX, NT, etc.

[1028] **********************************

• [1029] SET MPRINT ON.
• [1030] !EXCEPT ELMNM=(nsmmrt03 nsmmrt04 nsmnmrt05) ELMT_—TY=RT ELMLOC=Minneapolis
  • [1031] CODE=1 STRTYR=1999 STRTMON=06 STRTDAY=30 NUMDAYS=1
  • [1032] SARPHME=“o:\IPSA\Product Development\Reporting”
  • [1033] SADSN=“ipsa01” SAPWD=“spssuser” SAPWD=“spssuser”
• [1034] SASRVER=“ipsa01”.

[1035] **************************************************************

[1036] DAILY EVENT AVAILABILITY GRAPH:

[1037] for Network, Service, and Process AS A WHOLE

[1038] *******************************************************

• [1039] SET MPRINT ON.
• [1040] !EXCEPT AVAIL=Service ELMLOC=Minneapolis CODE=2
  • [1041] STRTYR=1999 STRTMON=06 STRTDAY=30 NUMDAYS=1
  • [1042] SARPHME=“o:\IPSA\Product Development\Reporting”
  • [1043] SADSN=“ipsa01” SAUID=“spssuser” SAPWD=“spssuser”
  • [1044] SASRVER=“ipsa01”.

[1045] *******************************************

[1046] DAILY EVENT AVAILABILITY GRAPH:

[1047] for EACH Network, Service, and Process

[1048] *********************************************

• [1049] SET MPRINT ON.
• [1050] !EXCEPT EVENT=“DNS Service Failed” ELMLOC=Minneapolis CODE=3
  • [1051] STRTYR=1999 STRTMON=06 STRTDAY=30 NUMDAYS=1
  • [1052] SARPHME=“o:\IPSA\Product Development\Reporting”
  • [1053] SADSN=“ipsa01” SAUID=“spssuser” SAPWD=“spssuser”
  • [1054] SASRVER=“ipsa01”.

[1055] The possible variables to pass in are the same as in the graphs detailed earlier, except for:

• [1056] AVAIL (availability type) valid parameters include ‘Network’, ‘Service’, or ‘Process’. This selects ALL events under these specifications, not just a single event type.
  Manual Settings

[1057] For optimal output for the spectrum graph these settings should be made: (in the chart options in Export dialog box)

• [1058] Resolution: 200×200
• [1059] Size: 4.5×4.5
• [1060] Format: YCC Color
• [1061] Quality: 100%
• [1062] Color Translation: Normal
• [1063] Operation: Progressive
  Chartlook

[1064] The exception graph calls the chartlook exception.clo and the availability graphs call the chartlook avail.clo found in the O:\ipsa\product development\reporting\spss\templates directory.

Adhoc Web Demo

[1065] The Ad hoc web demo demonstrates an HTML-based front end to the SA reporting tool. It allows the user to submit ad hoc reporting criteria from a user-friendly interface. This data is picked up by SPSS and the generated graph is displayed in the browser.

Requirements:

• • [1066] HTML 3.2 compliant web browser (e.g. Netscape Navigator 3.0 or higher, MS Internet Explorer 4.0 or higher).
  • [1067] adhoc.pl file located in the cgi-bin directory of a web server
  • [1068] adhoc.setup.txt file located in the same directory as adhoc.pl (above)
  • [1069] *<wwwroot>/BatchTest/directory containing SPSS graphs
    Overview:

[1070] From the main Ad hoc screen, a metric class is chosen and the Next button is clicked. If multiple graph types are available for the chosen metric, they are displayed to the user. After a graph type is chosen, the report criteria can be entered into the form that is displayed. Submitting the data will display a graph for the specified data.

[1071] Note: in this demo, only a minimal number of graphs exist, so only strict attention to detail will grant a valid report being displayed. If a report is requested that does not exist, the user will be told that no data points exist for the given criteria. Either use the “back” button to edit your criteria, or follow the hyperlink to create a new report query.

Bar Graph

[1072] Choose the Service Availability metric class

• [1073] Title: <anything or nothing>
• [1074] Element(s): gfmmnt03 nsmmrt03 nsmmrt04 nsmmrt05 nsmmrt06 nsmmrt07
• [1075] Location: Minneapolis (default example)
• [1076] Event: DNS Service Failed (default)
• [1077] Press Submit
• [1078] Box Plots
• [1079] Daily Comparison Box Plot
• [1080] Choose the Network Element Performance metric class
• [1081] Choose Daily Comparison Boxplot
• [1082] Title: <anything or nothing>
• [1083] Elements: nsmmrt03 nsmmrt04 nsmmrt05
• [1084] Metric(s): CPU Busy (%)
• [1085] Location: Minneapolis (default)
• [1086] Element Type: Router (default)
• [1087] Press Submit
• [1088] Monthly Trend Box Plot
• [1089] Choose the Network Element Performance metric class
• [1090] Choose Monthly Trend Boxplot
• [1091] Title: <anything or nothing>
• [1092] Elements:*
• [1093] Metric(s): CPU Busy (%)
• [1094] Location: Minneapolis (default)
• [1095] Element Type: Router (default)
• [1096] Press Submit
• [1097] Exception Graphs
• [1098] Daily Event Exceptions
• [1099] Choose the Events metric class
• [1100] Choose Daily event exceptions from the Occurrence and Availability menu
• [1101] Title: <anything or nothing>
• [1102] Location: Minneapolis (default)

[1103] Choose from any of the following three combinations:

• [1104] Element(s): gfmmnt03
• [1105] Element Type: NT
• [1106] Element(s): nsmmrt03 nsmmrt04 nsmmrt05
• [1107] Element Type: Router
• [1108] Element(s): nsmmws16
• [1109] Element Type: Unix

[1110] High Level Daily Event Exceptions

[1111] Choose the Events metric class

[1112] Choose High Level Daily event exceptions from the Occurrence and Availability menu

• [1113] Title: <anything or nothing>
• [1114] Location: Minneapolis (default)
• [1115] Event Type: Network or Service

[1116]

TABLE 16
Low Level Daily Event Exceptions
REQUIRED FILES FOR
REPORTING
IPSA—StartSPSS.cmd	Located anywhere, it is used to start the reporting
	process (see Configuration). Instantiates two SPSS
	processes (spssw.exe, spsswm.exe) and
	IPSA—SPPS.exe
<SABATCH>	Batch queue file location, as specified in the
	IPSA—StartSPSS.cmd file (see Configuration).
IPSA—BatchQueue.txt	Queue file that, if present, is passed to SPSS and
	contains batch report specifications. This file is deleted
	after being read by IPSA—SPSS.exe. See Adhoc.doc
	and graphs.doc for content explanations.
<SAADHOC>	Adhoc queue file location, as specified in
	<SARPHME>\InputQueue\IPSA—Reporting—
	Config.txt(see Configuration).
IPSA—AdhocQueue.txt	Queue file passed to SPSS that contains ad hoc report
	parameters. This file is created by the web interface
	(<WWW ROOT>\cgi-bin\adhoc.pl) and deleted after
	use by IPSA—SPSS.exe. See Adhoc.doc and
	graphs.doc for content explanations.
IPSA—AdHocQueue—Working—	Adhoc working file with a unique IPSA—SPSS.exe
instance>.txt	instance number, <instance>, that is defined in
	IPSA—StartSPSS.cmd (see Configuration).
IPSA—ProcessTheBatchQueue.txt	The presence of this file notifies IPSA—SPSS.exe to
	read <SABATCH>\IPSA—BatchQueue.txt and pass it
	to SPSS. This file is polled for every five seconds.
<SARPHME>	The reporting home directory, as specified in the
	IPSA—StartSPSS.cmd file (see Configuration). All
	files needed to generate reports are located within this
	directory.
\InputQueue	IPSA—SPSS.exe configuration and control files.
IPSA—Reporting—Config.txt	IPSA—SPSS.exe configuration file. Includes database
	location and login information, output directories and
	image format specification for the generated graphs
	(see Configuration).
IPSA—ControlQueue.txt	Contains commands to be issued to IPSA—SPSS.exe
	during execution. Used for terminating the process.
\Logs	Log files generated from IPSA—SPSS.exe execution
IPSA—VB—SPSS.log	Error messages from program execution.
\SPSS	All SPSS related files.
\Macros	SPSS macros
!incdef.SPS
!bargrph.SPS	bar graph
!bxgrph.SPS	boxplot
!spect.SPS	spectrum graph
!xygrph.SPS	xy line graph
\Templates	SPSS chart looks
avail.clo	availability graph
bar.clo	bar graph
box.clo	boxplot
exception.clo	exception graph
line.clo	xy line graph
\Working Data Files	SPSS macros use this directory for temporary files.
\Web\HtmlTemplates	HTML fragments for use to dynamically produce
	HTML
	batch TOC header
todays—urls—head—template.txt
	batch TOC report title
todays—urls—detail—template.txt
	batch TOC footer
todays—urls—tail—template.txt
	batch/adhoc list-of-reports header
detail—urls—head—template.txt
	batch/adhoc list-of-reports details
detail—urls—detail—template.txt
detail—urls—tail—template.txt	batch/adhoc list-of-reports footer
<SAWEB>	The report output home directory, as specified in
	<SARPHME>\InputQueue\IPSA—Reporting—
	Config.txt(see Configuration).
\Adhoc\<date>—INFO	Adhoc reports issued on the date <date> where <date>
	is of the form YYYYMMDD. The directory is
	created by the report generation script.
	the generated adhoc report
<adhoc—report—title><date>
<unique id>.html
\Batch\Daily	Daily batch reports home directory
\<date>—INFO	Generated batch reports. <date> is of the form
	YYYYMMDD, but is the date prior to the date when
	the report request was issued. This directory is created
	by the report generation script.
\Images	Images for the HTML pages
Graph1.gif	list bullet
SA1.gif	Service Assurance logo
<WEB ROOT>	Root directory of the web server where the reports will
	be housed.
bground.gif	background image for the adhoc web pages
\cgi-bin	Adhoc files location. This directory must be accessible
	from a web browser via HTTP and have “execute”
	permission assigned to it.
adhoc.pl	Perl script used to generate ad hoc reports. This file is
	accessed via HTTP from a web browser to start the ad
	hoc report data gathering process.
adhoc.setup.txt	Setup and configuration file for the adhoc.pl script.
	Contains report types and descriptions.

[1117] Choose the Events metric class

[1118] Choose Low Level Daily event exceptions from the Occurrence and Availability menu

• [1119] Title: <anything or nothing>
• [1120] Location: Minneapolis (default)
• [1121] Event: DNS Service Failed or SMTP Service Failed
• [1122] XY Line Graph