Functions

update alert.status set EventId = base64_encode(Node) where Serial = 999;

update alert.status set Location = base64_encode('some static string value') where Serial = 999;

Sunday is 0, Monday is 1, and so on.

The output is Monday, Tuesday, and so on.

Unlike dayasnum, Sunday is 1, Monday is 2, and so on.

select Summary, Severity from alerts.status where LastOccurrence < getdate - 600;

The variables class and parent_class can both be either string or integer.

select Node, Summary, AlertGroup, 
Server from alerts.status 
where instance_of(Class, 'DB2') = true;

json_prop_dedup(src_json, dst_json, propnames)

update alerts.status set ExtendedAttr =
      json_prop_dedup('{"resource":{"hostname":"dsthostname.com","port":1234,"dstnode":"mynode"},"ival":10101,"bval":false}',
      ExtendedAttr, 'resource.hostname:resource.port:bval') where Identifier =
      'mytestevent';

json_prop_get(json, propname)

select
      json_prop_get('{"resource":{"hostname":"myhostname","port":100,"node":"mynode2"},"ival":9999,"bval":true,"some":{"new":{"propname":"propvalue"}}}',
      'resource.port') as pvalue from alerts.status where Identifier =  'mytestevent';

select json_prop_get(ExtendedAttr, 'ival') as pvalue from alerts.status where Identifier =
      'mytestevent';

json_prop_set(json, propname, propvalue)

update alerts.status set ExtendedAttr =  json_prop_set(ExtendedAttr,
      'resource.newprop', 'somevalue') where  Identifier = 'mytestevent';

update  alerts.status set ExtendedAttr =
      json_prop_set('{"sval":"string","ival":9999,"bval":true}', 'ival', 1234)  where
      Identifier = 'mytestevent';

select md5_hash(Node) as HashedNode from alerts.status;

update alerts.status set EventId = md5_hash(Node + AlertKey + Agent) where Serial = 999;

update alerts.status set Location = md5_hash('some static string value') where Serial = 999;

January is 0, February is 1, and so on.

The output is January, February, and so on.

Unlike monthasnum, January is 1, February is 2, and so on.

Used with extended attributes.

Returns TRUE if Region exists in the extended attributes as the name of a name-value pair. If the name does not exist, the function will return FALSE.

select nvp_merge(ExtendedAttr,EventData) as merge from status;

update alerts.status set ExtendedAttr = nvp_merge(ExtendedAttr, Location);

If the name exists in the attribute, the function returns the value.

If name_value_pairs is not valid, the empty string is returned and an error logged.

If the key is not present, the empty string is returned ('').

If there are multiple entries for the name, the first one is returned.

Returns the Region attribute.

Adds or replaces keys from a name-value pair string and returns the new name-value pair string. A date is stored in seconds since 1970; that is, the UNIX epoch format.

If name_value_pairs is not a valid string (that is, it is an empty string or contains entries that do not conform to the correct format ), then $key="$value" is returned and an error is logged.

If there are multiple entries for the key, only the first is replaced.

Sets the Region attribute in the extended attributes.

If the split will cause a multi-byte character to be incomplete in the target string, the function returns the largest complete string it can. The next call to the function (providing the int_chunk_len is the same at the previous call) will start from the character that could not be completely extracted.

The function will split a multi-byte string into smaller strings that hold only complete multi-byte characters. This is primarily meant for storing large strings into several smaller database fields.

The int_chunk_len in all calls to the split function on the same string must be the same.

to_char(argument [, 'conversion_spec'])

Converts the argument to a string. The argument can be of any data type except a string.

The comma (,) is required only if a conversion string is specified.

If the argument is a time type, you can specify a second argument consisting of a conversion specification to format the output. This format is defined in reference date/time format section. The default format is EEE MMM dd HH:mm:ss yyyy or in POSIX format %a %b %d %T %Y. Note that the POSIX format with % is deprecated.

to_char(FirstOccurrence) returns a string such as Thu Dec 11 16:02:05 2003

to_char(LastOccurrence, '%Y') returns a string such as 2010

to_int('argument')

Converts the argument to an integer. The argument can be of any data type except integer.

This function strips any leading white space from the argument, and then scans the remaining string. The scan stops when it encounters a character that cannot be converted to a decimal character, or when it reaches the end of the string, whichever happens first. When the scan stops, the function converts the characters to their decimal value, or returns 0 if it failed to encounter any characters that could be converted to decimal.

to_int('3F') returns 3

to_int('UK') returns 0

to_int('F3') returns 0

to_real('argument')

Converts the argument to a 64-bit real number. The argument can be of any data type except real.

This function strips any leading white space from the argument, and then scans the remaining string. The scan stops when it encounters a character that cannot be converted to a decimal character, or when it reaches the end of the string, whichever happens first. When the scan stops, the function converts the characters to their decimal value, or returns 0 if it failed to encounter any characters that could be converted to decimal.

to_real('3F') returns 3.000000

to_real('UK') returns 0

to_real('F3') returns 0

to_time(argument [,'conversion_spec'])

to_date(argument [,'conversion_spec'])

Converts the argument to a time type. The argument can be of any data type except a time type.

The comma (,) is required only if a conversion string is specified.

If the argument is a string type, you can specify a second argument consisting of a conversion specification to format the output. The default format is EEE MMM dd HH:mm:ss yyyy or in POSIX format %a %b %d %T %Y. Note that the POSIX format with % is deprecated.

This function strips any leading white space from the argument, and then scans the remaining string. The scan stops when it encounters a character that cannot be converted to a decimal character, or when it reaches the end of the string, whichever happens first. When the scan stops, the function converts the characters to their decimal value, or returns 0 if it failed to encounter any characters that could be converted to decimal.

to_unsigned(73) returns 73

to_unsigned('UK') returns 0

to_unsigned('F3') returns 0

user_in_group(UID, GroupName string)

user_in_group(GID)

user_in_group(GroupName string)

This function can take one or two arguments.

The single argument form uses the user ID (UID) of the user that is executing the SQL command. The two argument form requires a User ID and a group name or group ID (GID).

select Summary from alerts.status where user_in_group(OwnerGID) = true returns all alarms that have been assigned to a group that the current user is a member of.

insert into alerts.status (Identifier, Severity,Summary,Node) values ('rowkey',5,'UUID Generation',uuidgen_v1());

update alerts.status set NodeAlias = uuidgen_v1(), Location = uuidgen_v1() where Serial = 999;

insert into alerts.status (Identifier, Severity,Summary,Node) values ('rowkey',5,'UUID Generation',uuidgen_v4());

update alerts.status set NodeAlias = uuidgen_v4(), Location = uuidgen_v4() where Serial = 999;