Table of Contents

Cisco Info Server Interactive Commands

SQL Interactive Interface

Usage
Operation

Database Migration

Usage
Default Files
Operation

Messages

Usage
Operation

Data Types

Notes on char and varchar

Database Tables

Alert Related Tables

alerts.status Table
alerts.details Table
alerts.journal Table

Client Tool Support Tables

alerts.objclass Table
alerts.objmenus Table
alerts.objmenuitems Table
alerts.resolutions Table
alerts.conversions Table
alerts.col_visuals Table
alerts.colors Table

User Authentication Tables

master.names Table
master.groups Table
master.members Table
master.profiles Table
master.stats Table

Automation Tables

auto.triggers Table
auto.actions Table

Desktop Tools Tables

tools.actions Table
tools.action_access Table
tools.menus Table
tools.menu_items Table
tools.prompt_defs Table
tools.menu_defs Table

Java Event List Tables

jel.jel_props Table

Service Tables

service.status Table

Cisco Info Server Properties
Proxy Server Properties
Cisco Info Server Command Line Options

Backward Compatible and Deprecated Command Line Options

Proxy Server Command Line Options
Cisco Info Server SQL

Select
Update
Delete
Insert
Boolean Operators

SQL Syntax

The Detailed Syntax
Search Conditions
Date Literals

getdate
dayname
monthname
dayofmonth
minuteofhour
hourofday
dayasnum
monthasnum

Statement Syntax

SQL Commands

create database

Syntax
Usage
Example

create table

Syntax
Usage
Example

password

Syntax
Usage
Example

use

Syntax
Usage
Example

delete

Syntax
Usage
Example
Direct Delete

insert

Syntax
Usage
Example

select

Syntax
Usage
Examples

update

Syntax
Usage
Examples
Direct Update

shutdown

Syntax
Usage
Example

describe

Syntax
Usage
Example

users

Syntax
Usage
Example

list databases

Syntax
Usage
Example

list tables

Syntax
Usage
Examples

sync

Syntax
Usage
Examples

get prop

Syntax
Usage
Example

set prop

Syntax
Usage
Example

show props

Syntax
Usage
Example

save props

Syntax
Usage
Examples

svc update

Syntax
Usage
Example

vacuum

Syntax
Usage
Example

Cisco Info Server Reference

This appendix contains Cisco Info Server reference information. It contains the following sections:

• "Cisco Info Server Interactive Commands"

• "Data Types"

• "Notes on char and varchar"

• "Cisco Info Server Properties"

• "Proxy Server Properties"

• "Cisco Info Server Properties"

• "Proxy Server Command Line Options"

• "Cisco Info Server Command Line Options"

• "SQL Syntax"

• "SQL Commands"

Cisco Info Server Interactive Commands

This section contains information about the interactive commands provided by the Cisco Info Server.

SQL Interactive Interface

The nco_sql command allows you to connect to Cisco Info Server (or Cisco Info Gateways) and use the SQL interface to interact with and control the Cisco Info Server.

---

Note Only users with the Allow ISQL Access option enabled can connect to the database using nco_sql.

---

Usage

where:

-server takes a parameter which is the name of the server to connect to. Default is INFOSERVER.

-user takes a parameter which is the name of a Cisco Info Center user. Default is the user's UNIX login name.

-password takes a parameter for the user. We recommend this option not be used as it makes the password visible.

Operation

Once logged in using nco_sql or isql, you can specify any SQL commands. The commands are not issued until the command go is issued. For example:

nco_sql -server INFOSERVER

Password:

1> update alerts.status set Severity=0 where Severity<3;

1> update alerts.status set Severity=5 where Severity>=3;

1> go

Database Migration

The nco_migrate command takes two definitions of an Cisco Info Server database, an old and new definition, and migrates the data, assuming it to be valid for the old definition, to the new definition. It should be used when fields have been added or removed from existing Cisco Info Server tables. nco_migrate preserves the data.

Usage

where:

-server takes a parameter which is the name of the server to be migrated. This defaults to NCOMS.

-orig takes a parameter which is the name of the file that contains the old SQL definition with which the database was created. This defaults to servername.orig. The option -old can be used as an alias.

-sql takes a parameter which is the name of the file that contains the new SQL definition. This is the definition to convert to. This defaults to servername.sql. The option -new can be used as an alias.

-defaults takes a parameter which is the name of a default file.

-verbose sets nco_migrate to provide verbose output during the migration process.

-version displays version information about nco_migrate and then exits.

-fuzzy enables region storage. You must use this option to migrate databases stored in region storage.

-help displays help information about nco_migrate and then exits.

Default Files

To allow for automatic settings to be made to new fields, a defaults file can be created and used by specifying the -defaults option with a file name. The file should contain entries in the form:

<databasename>.<tablename>.<fieldname>=<value>

where <databasename> is the name of the database, <tablename> is the name of the table, <fieldname> is the name of the field to set and <value> is the value to set it to.

<value> may be a quoted string or integer value depending on the type of the field being set.

For example, when a field called HtmlPage is added to the alerts.status table and existing entries should be set to http://www.envysoft.com then the defaults file would contain:

alerts.status.HtmlPage="http://www.envysoft.com/"

Note If you specify an existing field in the defaults file, any data stored in that field is overwritten by the default setting.

Operation

When you run nco_migrate, it takes the original SQL file (.orig) and the new SQL file (.sql), and migrates the data in the $OMNIHOME/db directory. The existing files are backed up as .orig and the new files are created in their place. The .bak files in the $OMNIHOME/db directory are not changed.

Messages

The nco_message command is used to display a message in a window.

Usage

where:

-title sets the title of the dialog.

-message takes a message string to display.

-type takes a parameter which is the name of the message type. The message type can be either: info, warn, or, error. This defaults to error.

-tag sets the help page tag.

-stdin reads the message from the standard input rather than from -message.

-version displays version information about nco_message and then exits.

-help displays help information about nco_message and then exits.

Operation

You can run the nco_message command from within a script. It takes the message you specify and displays it in a window.

Data Types

The data types shown in Table A-1are used in the Cisco Info Server tables.

Table A-1: Cisco Info Server Data Types

Data Type	Description
char	A string of characters. Maximum size is defined by the field size.
varchar	Identical in operation to char, however less storage space is required. String update is slower but all other operations take similar times. See "Notes on char and varchar" below for additional information.
int	An integer value.
time	Time and date, as a value in seconds, since Midnight, Jan 1st 1970.
timestamp	Time and date, as above, however, automatically populated by the Info Server when the record is created.
ltime	A time field which has modified semantics to allow for it to be updated by Cisco Info Mediators.
incr	An integer field which is automatically populated with an Info Server internal variable when the record is inserted. (Used for unique serial number generation).
optime	A time field which is automatically updated with current date and time whenever an insert or update operation is performed.
opcount	An integer field which is incremented whenever a record is inserted or an attempt is made to insert a record which already exists.

Notes on char and varchar

The char type and varchar type both define a string of characters up to a maximum set by the field size value. These field types are interchangeable.

The Info Server in previous versions of Cisco Info Center did not include the varchar type. The char type has been retained because its performance is better for the update string operation. Only mass updates of a varchar type that changes the length of the string causes noticeable performance reductions.

By default, all character fields use the varchar field type. The varchar type uses less storage space than char and for operations such as scan, delete, insert and deduplicate, performance is better.

Database Tables

This section lists the Cisco Info Server database tables. They are grouped into the following:

• "Alert Related Tables"

• "Client Tool Support Tables"

• "User Authentication Tables"

• "Automation Tables"

• "Desktop Tools Tables"

• "Java Event List Tables"

• "Edit this table only through JACE. Do not try to edit it directly."

Note You should not change the field types of primary fields. This applies to the interchangeable varchar and char field types. The default varchar type should always be used. Because primary keys are never updated, performance is not an issue.

Alert Related Tables

alerts.status Table

The alerts.status table is the storage point for all events and correlations of those events. Table A-2 describes the alerts.status table fields.

alerts.details Table

The Details table contains the detail attributes of the events in the system. Table A-3 describes the alerts.details table fields.

alerts.journal Table

The Journal table provides a history of work performed on events. Table A-4 describes the alerts.journal table fields.

Client Tool Support Tables

alerts.objclass Table

The alerts.objclass table is used to determine which menu and icons to use for a particular class of object. Table A-5 describes the alerts.objclass table fields.

alerts.objmenus Table

The alerts.objmenus table is used to provide the list of menus.Table A-6 describes the alerts.objmenus table fields.

alerts.objmenuitems Table

The alerts.objmenuitems table is used to provide the contents of menus. Table A-7 describes the alerts.objmenuitems table fields.

alerts.resolutions Table

The alerts.resolutions table is used to maintain the Resolutions option in the Event List. Table A-8 describes the alerts.resolutions table fields.

alerts.conversions Table

The alerts.conversions table is used to provide easy conversion from a numeric value to a string for any column. Table A-9 describes the alerts.conversions table fields.

alerts.col_visuals Table

The alerts.col_visuals table is used to provide the default visuals for fields when displayed in the Cisco Info Admin Desktop tools. Table A-10 describes the alerts.col_visuals table fields.

alerts.colors Table

The alerts.colors table is used to create the colors required by the Cisco Info Admin Desktop. Table A-11 describes the alerts.colors table fields.

User Authentication Tables

master.names Table

The master.names table is used to provide user identification and authorization. Table A-12 describes the master.names table fields.

master.groups Table

The master.groups table is used to provide groups identification and authorization. Table A-12 describes the master.groups table fields.

Table A-13: master.groups Table

Field Name	Field Type	Field Size	Description
Name	char	64	UNIX user name.
GID	int		Group ID.

master.members Table

The master.members table is used to provide user identification and authorization. Table A-14 describes the master.members table fields.

master.profiles Table

The master.profiles table is used to provide the user restriction information. Table A-14 describes the master.profiles table fields.

Table A-15: master.profiles Table

Field Name	Field Type	Field Size	Description
KeyField	char	64	Unique key field for the table.
Restrict1	char	255	First line of restriction.
Restrict2	char	255	Second line of restriction.
Restrict3	char	255	Third line of restriction.
Restrict4	char	255	Fourth line of restriction.
AllowISQL	int		ISQL access.

master.stats Table

The master.stats table is used to control the -stats Cisco Info Server command line option. Table A-16 describes the master.stats table fields.

Automation Tables

auto.triggers Table

The auto.triggers table is used to control automation triggers. Table A-17 describes the auto.triggers table fields.

auto.actions Table

The auto.actions table is used to control automation triggers. Table A-17 describes the auto.actions table fields.

Table A-18: auto.actions Table

Field Name	Field Type	Field Size	Description
Name	char	64	The name of the action.
HasData	int
Data1	char	255	The first line of the data effect SQL.
Data2	char	255	The second line of the data effect SQL.
Data3	char	255	The third line of the data effect SQL.
Data4	char	255	The fourth line of the data effect SQL.
Hasexec	int		Indicates whether or not the action has an external effect.
ExecPath	char	255	The pathname of the external effect executable.
ExecArgs	char	255	The arguments of the external effect executable.
ExecHost	char	64	The hostname of the host to run the external effect executable.
EUID	int		Effective user ID.
HasPhone	int		Reserved for future use.
PhoneArgs1	char	255	Reserved for future use.
PhoneArgs2	char	255	Reserved for future use.
PhoneArgs3	char	255	Reserved for future use.
PhoneArgs4	char	255	Reserved for future use.
Comment1	char	255	The first line of the action description.
Comment2	char	255	The second line of the action description.
Comment3	char	255	The third line of the action description.
Comment4	char	255	The fourth line of the action description.
UpdateJournal	int	-	When enabled a journal entry is made by the action in every alert which matched the trigger condition of the trigger which executed the action.
Operator	int	-	The operator used in the row count condition. This can be 0>, 1>=, 2<, 3<=, 4=, 5!=
TargetCount	int	-	The row count comparison value used by the operator.

Desktop Tools Tables

tools.actions Table

The tools.actions table is used to control desktop tools. Table A-19 describes the fields in the tools.actions table.

Table A-19: Fields in the tools.actions Table

Field Name	Field Type	Field Size	Description
ActionID	int	-	The identifier of the tool.
Name	varchar	64	The name of the tool.
Owner	int	-	Indicates whether the tool has an owner or not.
Enabled	int	-	Indicates whether the tool is enabled or not.
Description1	varchar	255	The first line of the description.
Description2	varchar	255	The second line of the description.
Description3	varchar	255	The third line of the description.
Description4	varchar	255	The fourth line of the description.
HasInternal	int	-	Indicates whether the tool has an internal effect or not.
InternalForEach	int	-	When set, starts the internal effect for each matched row.
InternalEffect1	varchar	255	The first line of the internal effect.
InternalEffect2	varchar	255	The second line of the internal effect.
InternalEffect3	varchar	255	The third line of the internal effect.
InternalEffect4	varchar	255	The fourth line of the internal effect.
HasExternal	int	-	Indicates whether the tool has an external effect or not.
ExternalForEach	int	-	When set, starts the external effect for each matched row.
ExternalEffect1	varchar	255	The first line of the external effect.
ExternalEffect2	varchar	255	The second line of the external effect.
ExternalEffect3	varchar	255	The third line of the external effect.
ExternalEffect4	varchar	255	The fourth line of the external effect.
JournalForEach	int	-	When set, adds the journal entry for each matched row.
Platform	int	-	Indicates the type of platform the external effect runs on. 0 - NT, 1 - UNIX, 2 - UNIX and NT.
JournalText1	varchar	255	The first line of the journal entry.
JournalText2	varchar	255	The second line of the journal entry.
JournalText3	varchar	255	The third line of the journal entry.
JournalText4	varchar	255	The fourth line of the journal entry.
HasForcedJournal	int	-	Forces a journal entry dialog to be opened when the tool executes.
RedirectOut	int	-	When selected, output is echoed through a read only window in whichever tool used the command.
RedirectErr	int	-	When selected, errors is echoed through a read only window in whichever tool used the command.

tools.action_access Table

The tools.action_access table is used to control access to desktop tools. Table A-20 describes the fields in the tools.action_access table.

Table A-20: Fields in the tools.action_access Table

Field Name	Field Type	Field Size	Description
ActionID	int	-	The unique identifier of the tool, taken from the actions table.
GroupID	int	-	Indicates to which group the tool is available.
ClassID	int	-	Indicates to which class the tool is available.
ActionAccessID	int	-	Primary key field for table.

tools.menus Table

The tools.menus table is used to control desktop tool menus. Table A-21 describes the fields in the tools.menus table.

Table A-21: Fields in the tools.menus Table

Field Name	Field Type	Field Size	Description
MenuID	int	-	Primary key field for the menus table.
Name	varchar	64	The name of the menu.
Owner	int	-	Indicates whether the menu has an owner.
Enabled	int	-	Indicates whether the menu is enabled or disabled (greyed out).

tools.menu_items Table

The tools.menu_items table is used to control desktop tool menu items. Table A-22 describes the fields in the tools.menu_items table.

Table A-22: Fields in the tools.menu_items Table

Field Name	Field Type	Field Size	Description
KeyField	varchar	32	A key field for this menu item. Created from the menu_id (from the tools.menus table) and the menu_item_id.
MenuID	int	-	The unique menu identifier taken from the tools.menus table.
MenuItemID	int	-	The primary key identifier for this menu item.
Title	varchar	64	The name that appears on the menu.
Description	varchar	255	The description of the menu item.
Enabled	int	-	Indicates whether the menu item is enabled or disabled (greyed out).
InvokeType	int	-	Indicates what action is taken when the menu item is accessed. 0 - an action is called (from the tools.actions table) 1 - a separator line is added. 2 - a submenu is displayed.
InvokeID	int	-	Indicates the action identifier of the action defined in InvokeType.
Position	int	-	Indicates the position (order) of this item on the menu.
Accelerator	varchar	32	Indicates the keyboard short-cut of this menu item.

tools.prompt_defs Table

The tools.prompt_defs table is used to store all prompt definitions. Table A-23 describes the fields in the tools.prompt_defs table

.
Table A-23: Fields in the tools.prompt_defs Table

Field Name	Field Type	Field Size	Description
Name	varchar	64	The name of the prompt.
prompt	varchar	64	The prompt title which appears at the top of the prompt window.
Default	varchar	64	The default value to enter if no value is entered by the user.
Value	varchar	255	The list of available values.
Type	int	-	The prompt type. Type 7 is link to a menu definition with the same name as the prompt.

tools.menu_defs Table

The tools.menu_defs table is used to control desktop tool menu items. Table A-24 describes the fields in the tools.menu_defs table

.
Table A-24: Fields in the tools.menu_defs Table

Field Name	Field Type	Field Size	Description
Name	varchar	64	The name of the menu. This must match the name of the type 7 prompt definition.
DatabaseName	varchar	64	The database which is used to build the menu items.
TableName	varchar	64	The table which is used to build the menu items.
ShowField	varchar	64	The field in the table to show as the menu pull down list.
AssignField	varchar	64	The actual field which is used to enter a value into the prompt.
OrderByField	varchar	64	The field used to order the menu.
WhereClause	varchar	255	This field allows the user to filter the table to show a subset of the full table list.
Direction	int	-	0 - Ascending, 1 - Descending

Java Event List Tables

jel.jel_props Table

The jel.jel_props table is used to control Java Event List and Java JELD. It is maintained by the JACE configuration tool (do not edit it directly). Table A-25 describes the fields in the jel.jel_props table.

---

Note Edit this table only through JACE. Do not try to edit it directly.

---

Service Tables

service.status Table

The service.status table is used to control the additional features required to support Netcool/Internet Service Monitors. Table A-26 describes the fields in the service.status table.

Table A-26: Fields in the service.status Table

Field Name	Field Type	Field Size	Description
Name	varchar	255	The name of the service.
CurrentState	int	-	Indicates the state of the service. Can be Good (0), Bad (1), Marginal (2), or Unknown (3).
StateChange	time	-	Indicates the last time the service state changed.
LastGoodAt	time	-	Indicates the last time the service was Good (0).
LastBadAt	time	-	Indicates the last time the service was Bad (1).
LastMarginalAt	time	-	Indicates the last time the service was Marginal (2).
LastReportAt	time	-	The time of the last service status report.

Cisco Info Server Properties

Note that some properties are read only; these are information only properties for use by upgrade scripts and other utilities.

The Cisco Info Server properties currently supported are shown in Table A-27.

Table A-27: Cisco Info Server Properties Table

Property Name	Editable	Type	Default	Description
ServerMajorRel	No	int	3	Cisco Info Server major release number.
ServerMinorRel	No	int	3	Cisco Info Server minor release number.
ServerRevision	No	int	0	Cisco Info Server revision level.
AuditAutomation	Yes	boolean	False	Audit changes to the Automation system (the auto database).1
AuditNames	Yes	boolean	False	Audit changes to the authentication systems (the master database).
AuditConfig	Yes	boolean	False	Audit changes to the configuration tables (all tables in the alerts database except status, journals, and details).
AllowISQL	Yes	boolean	True	Allow connections to the Cisco Info Server using ISQL or nco_sql.
AllowConnections	Yes	boolean	True	Allow connections to the Cisco Info Server with any tool.
DebugLevel	Yes	int	0	Sets debug level of the Cisco Info Server. Overridden by the -debug command line option.
Granularity	Yes	int	60	Sets the granularity of the Cisco Info Server. Overridden by the -granularity command line option.
LogInterval	Yes	int	300	Sets the interval to consolidate the persistent database. Overridden by the -interval command line option.
AutoTick	Yes	int	1	Sets the clock tick interval for Automation handling. Overridden by the -autotick command line option.
UpdateAlertKey	Yes	boolean	False	When set, the new AlertKey value is copied on deduplication.
UpdateSummary	Yes	boolean	False	When set, the new Summary value is copied on deduplication.
ReawakenClosed	Yes	boolean	False	Sets the option to reawaken closed alarms when a new alarm arrives. (See Chapter 1, "Cisco Info Server").
DeackOnReawaken	Yes	boolean	False	When set (when an alert is reawakened), the Acknowledged field is cleared.
DebugTriggers	Yes	boolean	False	Sets the option to log the execution of triggers into the log file.
DebugActions	Yes	boolean	False	Sets the option to log the execution of actions into the log file.
UpTime	No	int		Time when the server came up.
MonitorConnections	Yes	boolean	True	Generates an alarm to flag that a process has connected to the Cisco Info Server.
StatsInterval	Yes	int	900	Controls the time interval for gathering statistics.
DeleteLogSize	Yes	int	1024	Sets a maximum log size. When this size is reached the log is archived and a new log is started.
DeleteLogLevels	Yes	int	0	Controls the level of detail sent to the log file after an alert is deleted.
RegionStorage	Yes2	boolean	False	Enables region storage.

1The Audit features, when enabled, create SystemWatch alerts in the alerts.status table, which informs users the change has occurred. 2Can be edited in the .props file but only when the Info Server has been shutdown.

Proxy Server Properties

Note that some properties are read only; these are information only properties for use by upgrade scripts and other utilities.

The Proxy Server properties currently supported are shown in Table A-27.

Table A-28: Proxy Server Properties

Property Name	Editable	Type	Default	Description
ConnectionRatio	Yes	int	10	Ratio of incoming and outgoing connections.
DebugMode	Yes	boolean	False	Sets the Proxy Server debug option.
MaxConnections	Yes	int	30	Maximum number of connections to the Proxy Server.
RemoteServer	Yes	char	NCOMS	Sets the name of the Cisco Info Server to connect to.
ServerName	Yes	char	NCO_PROXY	The name of the Proxy Server.

Cisco Info Server Command Line Options

The nco_objserv command has the following syntax:

Table A-29 lists the defaults for the nco_objserv command line options.

Backward Compatible and Deprecated Command Line Options

The command line options shown in Table A-30 are supported for backward version compatibility. Their use is already deprecated by other options on the command line and these options may not be supported in future versions.

Proxy Server Command Line Options

The nco_proxyserv command uses the following format:

Table A-30 lists the defaults for the nco_proxyserv command line options.

Table A-31: Proxy Server Command Line Options

Option	Default	Description
-name <string>	NCO_PROXY	Sets the servername. When omitted, defaults to NCO_PROXY.
-propsfile <string>	$OMNIHOME/etc/<servername>.props	Sets the Proxy Server properties file name. This defaults to NCO_PROXY.props.
-logfile <string>	$OMNIHOME/log/<servername>.log	Sets the filename for the log file to be written to.
-max	30	Sets the number of connections for Cisco Info Mediators and Cisco Info Gateways.
-ratio	10	Sets the ratio of incoming to outgoing connections to the Proxy Server.
-debug <numeric>	0	Turns on debug messages. The <numeric> value denotes the amount of debug information required; available levels are 0 (Silent), 1, 2,and 3 (Ultra-verbose).
-version	off	Displays version information about the Proxy Server then exits.
-help	off	Displays help on nco_proxyserv command line options then exits.

Cisco Info Server SQL

The following sections show example Cisco Info Server SQL commands.

Select

The select command allows you to find information in a table. For example, to select all records whose Severity field is equal to 4, specify:

select * from alerts.status where Severity = 4;
 

To select all records whose Node field starts with the word "terminal" followed by any other characters, specify:

select * from alerts.status where Node like 'terminal.*';

Update

The update command allows you to change the information in the database. For example, to set the Severity to 0 for any record in the status table where Node is equal to Fred, specify:

update alerts.status set Severity = 0 where Node = 'Fred';

You can also simultaneously specify more than one command. For example, the following command searches for a record whose Severity is equal to 1 and Node is equal to Fred, then sets the Severity to 0 and changes the Summary field to the string Discarded:

update alerts.status set Severity = 0, Summary = 'Discarded' where Severity = 1 and Node = 'Fred';

Delete

The delete command allows you to remove records from the status table. For example, to remove all records whose Node field is equal to Fred, specify:

delete from alerts.status where Node = 'Fred';

Insert

The insert command allows you to create new records in the status table. For example to insert an alert into the status table specifying the values in only four fields, enter:

insert into status (Identifier, Severity, Tally, Serial) values ('MasterMachineStats15', 5, 12, 21);
 

The data to be inserted follows the values keyword in a parenthesized comma separated list of literal values.

By identifying specific column names after table-name the values are entered in sequence in those fields. All other fields are populated with default values.

The updating keyword forces the specified columns to be updated if the insert is deduplicated. To force the field Severity to deduplicate for the previous example, enter:

insert into status (Identifier, Severity, Tally, Serial) values ('MasterMachineStats15', 5, 12, 21) updating (Severity);

Boolean Operators

You can use a number of boolean operators which evaluate to true or false. Table A-31 describes the available operators.

Table A-32: Boolean Operator in Cisco Info Server SQL

Operators	Description
a = b	a is equal to b
a < b	a is less than b
a > b	a is greater than b
a < > b	a is not equal to b
a like b	a is like b
a not like b	a is not like b

Use the like comparison when you require a wildcard match. Table A-33 shows how you use like comparisons.

SQL Syntax

This section describes the SQL syntax.

The Detailed Syntax

alphachar = a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z

number = 0|1|2|3|4|5|6|7|8|9

string = 'any sequence of ASCII characters'

Note Strings can contain a single ' by escaping the string as \'.

intnum = number{number} | {number}.number{number}

name = alphachar{alphachar|number|_}

Note Examples of legal names include Fred1, Data_base, fast4some, and not_4_some.

column-name = name

table-name = name

database-name = name

table-ref = database-name.table-name | table-name

dateliteral = getdate | dayname | monthname | dayofmonth| hourofday | minuteofhour | dayasnum | monthasnum

literal = intnum | string | dateliteral | property

property = %name |

%ServerName |
%OMNIHOME |
%LogFile |
%HostName

Note name can be any Cisco Info Server property. The subsequent properties refer to internal variables.

scalar = column-name | literal

scalar-expression =

scalar + scalar |
scalar - scalar |
scalar * scalar |
scalar / scalar |
+scalar |
-scalar |
(scalar-expression) |
scalar

arith-expression =

sum(column-name) |
min(column-name) |
max(column-name) |
avg(column-name) |
count(column-name) |
count(*) |
dist(column-name, exactnum)

boolean-expression =

scalar-expression (<|>|=|<>|<=|>=) literal |
scalar-expression [not] like literal |
scalar-expression [not] in (subquery | (literal { ,
literal}))

search-condition = search-condition (and | or) search-condition |

not search-condition |
(search-condition) |
boolean-expression

ordering-spec = column-name asc | desc {, column-name asc | desc}

subquery = select [(scalar-expression{, scalar-expression} | *]

from [table-name]
[where search_condition]
[order by ordering-spec]

data-type =

character [(int-val)] |
int |
time |
incr |
timestamp |
ltime |
optime |
opcount

table-constraint =

primary key (column-name) |
permanent |

table-entry = field-name data-type | table-constraint

state = good | marginal | bad

Search Conditions

A search condition specifies what you are looking for in the tables. The syntax for a search condition is detailed in the previous section. Following are some examples:

name not like 'John.*' and age > 20

(name like 'Jilted' and age <20) or (name like 'John' and age = 22)

not (name like 'Jilted' and age = 20) and not (name like 'John' and age >25)

Date Literals

A set of date literals exist which return date information about the current time. They are used for time-sensitive automation or filters.

getdate

getdate returns the current time to allow for comparisons with time based on the current time. getdate is equivalent to the integer value of the current date and time in seconds, such as a time field. Note that when the getdate function is used in a subtraction, a space character must precede and follow the minus sign, otherwise, the equation is not evaluated correctly.

dayname

dayname returns the name of the current day (for example, mon, tue, fri).

monthname

monthname returns the name of the current month (for example, jan, feb, jul).

dayofmonth

dayofmonth returns the day of the current month (for example, when it is January 21st, dayofmonth returns 21).

minuteofhour

minuteofhour returns the minute of the current hour (for example, when it is 11:31, minuteofhour returns 31).

hourofday

hourofday returns the hour of the current day (for example, when it is 11:31, hourofday returns 11).

dayasnum

dayasnum returns the day of the current week as a number (for example, when it is Sunday, dayasnum returns 0, Monday = 1, Tuesday = 2).

monthasnum

monthasnum returns the month of the current year as a number (for example, when it is January, monthasnum returns 1, December 12).

Statement Syntax

create database <database-name>;

use database <database-name>;

create table <table-name> (<table-entry> {, <table-entry>});

password <string1>, <string2>;

use database <database-name>;

delete from <table-ref> [via <string>] [where <search-condition>];

insert into <table-ref> values (<literal> {, <literal>});

select (* | (<scalar-expression> { , <scalar-expression>})) from <table-ref> [where <search-condition>] [order by <ordering-spec>];

select [<arith-expression> { , <arith-expression> }] from <table-ref> [where <search-condition>];

update <table-ref> set column-name = <scalar-expression> { , column-name = <scalar-expression>} [where <search-condition>];

shutdown;

describe <table-ref>;

users;

list databases;

list tables [in <database-name>];

sync [database <database-name> | table <table-ref>];

get prop <prop-name>;

set prop prop-name to <simple-value>;

show props;

save props [to <filename>];

svc update <name> <state>;

SQL Commands

This section describes each command, its syntax, and its usage, and provides examples.

create database

Syntax

Usage

The create database command creates a new, in-memory database (<database-name>) under which tables may be created.

Example

create database newthings;

create table

Syntax

Usage

Creates a table within the in-memory database currently in use. <table-name> is the name of the new table to create. For each field in the table, <table-name> and a data-type must be specified with a comma between each. This comma list can be followed by any number of <table-entry> entries which define the handling of the table.

Example

create table newtable (

name char(20),

age integer,

unique (name));

password

Syntax

Usage

<string1> is the name of the user whose password is to be changed. <string2> is the new password.

Example

password 'dj','realpassword' ;

use

Syntax

Usage

Uses the named (<database-name>) database as the default for locating tables.

Example

use database newthings;

delete

Syntax

Usage

Deletes all rows from the <table-name> table that match the <search-condition>. When where is not specified and <search-condition> is specified, all rows are deleted.

Example

delete from newtable where age > 20;

Direct Delete

Alternatively, you can use via to delete only one row from the database table. For example, specify:

delete from newtable via <string> where age > 20;

where <string> is an identifier in the database table.

insert

Syntax

Usage

Inserts a row into an existing named table. It is assumed values are specified for all columns in the order they were specified in the create statement for that table. The data to be inserted follows the values keyword in a parenthesized comma separated list of literal values.

Example

insert into newtable values ('fred', 30);

select

Syntax

Usage

The select command extracts fields of data from a table.

1. This syntax is for retrieving fields to a temporary table. Immediately after select you can enter an asterisk (*) (representing all fields) or a comma separated list of specific expressions to be extracted. Enter as followed by a name to change the column title for the field in the temporary table.

2. This is followed by the from keyword and the name of the table from which to extract. This can then be followed by an optional where keyword and search condition and an optional order by keyword with an ordering specification.

3. This syntax returns the result of the arithmetic-expressions based on the table (named after the from keyword) and an optional search condition (defined after the where keyword).

Enter as followed by a name after the arith-expression to change the title for the returned result.

Examples

select * from newtable where age > 20 and name ! like 'test.+'
 
select * from status where Summary=Node+' up';
 
select Severity, Severity+Tally from status;
 

Creates a temporary table containing two columns called Severity and (Severity+Tally). The new field Severity+Tally is populated by adding the content of the two fields together.

select Severity, Severity+Tally as Real_Severity from status;
 
Same as last example except the virtual column Severity+Tally has now been renamed Real_Severity. 
 
select Severity, Severity+Tally as Real_Severity from status where Severity > 20 - Tally;
 
select Severity, Severity+Tally as Real_Severity from status where Severity+Tally > 20;
 
select Severity, Severity+Tally as Real_Severity from status where 20 < Severity+Tally;
 
select MAX(Severity+Tally) AS HighestRealSeverity from status;
 

Create a single row which contains the maximum value returned by the addition of the Severity value and the Tally value.

select MAX(Severity), AVG(Severity), DIST(Severity, 4) from status;
 

Returns three values. The highest severity found, the average severity found and the number of entries found with the severity equal to 4.

select DIST(Node, 'mother') from status;
 

Returns the number of entries found where the contents of the Node fields equals the string `mother'.

select MAX(getdate-LastOccurrence) from status;
 
select (getdate-LastOccurrence)/60 as MinutesAlive from status;
 
select AVG((getdate-LastOccurrence)/60) as ResponseTime from status where OwnerUID=34;

update

Syntax

Usage

Updates a table, named in <table-name>, setting values for the comma separated list of settings that follows the set keyword, for entries in the table that match the search condition which follows the where.

Examples

update newtable set age=90 if age>91;

update newtable set age=10,name='Peter Pan' where name = 'Peter';

Direct Update

Alternatively, you can use via to update one row only in the database table. For example, specify:

update newtable via <string> set age=90 if age>91;

Where <string> is an identifier in the database table.

shutdown

Syntax

Usage

Super user only.

Shuts down the Cisco Info Server after synchronizing the persistent store.

The dump option is used to generate a logical checkpointing .dat file instead of a physical checkpointing .tab file when physical checkpointing is enabled.

Example

shutdown;

shutdown dump;

describe

Syntax

Usage

Prints details of the named table (<table-name>), describing field names, types, and sizes of fields.

Example

describe newtable;

users

Syntax

Usage

Lists the names of all the users connected to the Cisco Info Server.

Example

users;

list databases

Syntax

Usage

Lists the databases in the Cisco Info Server.

Example

list databases;

list tables

Syntax

Usage

Lists the tables in the current database. When in <database-name> is appended, the tables in the specified database are listed.

Examples

list tables;

list tables in alerts;

sync

Syntax

Usage

Forces the consolidation of either an entire database with the <database-name> parameter or of a specific table with <table-ref> appended. Consolidation writes out the current data and brings in the .trans file changes and applies them to the .dat file.

Examples

sync database alerts;

sync table alerts.status;

get prop

Syntax

Usage

Returns the value of the property where <prop-name> is the name of the property. It is returned as a single SQL row.

Example

get prop DebugLevel;

set prop

Syntax

Usage

Sets a property value where <prop-name> is the name of the property to set and <simple-value> is the value to set that property to. <simple-value> can be an integer value (for example, 0, 10, 2000), a boolean value (for example, TRUE or FALSE), or a single quoted string (for example, 'string value').

Note that no string properties are currently defined in the system; the facility has been added for future expansion.

Example

set prop LogInterval to 200;

show props

Syntax

Usage

Lists all the properties, returning the names and values as rows of data.

Example

show props;

save props

Syntax

Usage

Saves the property file in the default property file location. The optional to <filename> option allows the properties to be saved in an alternative file. <filename> should be contained within quotes.

Examples

save props;

save props to '/etc/propsfile'

svc update

Syntax

Usage

Updates the specified service (<name>) to the given <state> in the Cisco Info Server internal service history table.

Example

svc update newservice marginal;

vacuum

Syntax

Usage

Reclaims unused space in the Info Server tables when physical checkpointing is enabled. Has no effect when logical checkpointing is enabled.

Example

vacuum alerts.status;

Posted: Tue Jun 13 15:28:27 PDT 2000
Copyright 1989 - 2000©Cisco Systems Inc.