Customizing the timestamp that the probe adds to each event received

The probe can create a timestamp for each event received. This consists of two steps: specifying the column name from the source data that the probe will use to create the timestamp and specifying the format that the probe will use for the timestamp.

By default, Netcool/OMNIbus uses the FirstOccurrence and LastOccurence fields to indicate event timestamps. These two fields are in UTC UNIX timestamp format. Whatever column from the data source you choose to assign to these default OMNIbus fields, you must be format them as a timestamp using either SQL or the rules file. You can specify that the probe uses one of the following formats for the timestamp:

UNIX timestamp format - expressed as the number of seconds that have elapsed since Jan 1, 1970
General textual representation format - expressed in a customizable combination of years, months, days, hours, minutes, and seconds; for example: 2013-01-29 10:45:00

There are two ways in which you can define the format of the timestamp: within the probe rules file or within the SQL SELECT statement. The method that you chose depends on how you want to convert or format the timestamp.

Scenario 1: The column that you are using for the timestamp is in a general textual representation format and you want convert it to UNIX timestamp format. In this scenario, you can specify the timestamp using the rules file method or the SQL method.
Scenario 2: The column that you are using for the timestamp is in UNIX timestamp format and want to convert it to a general textural representation format. In this scenario, you must use the rules file method.
Scenario 3: The column that you are using for the timestamp is in a general textual representation format and you want to convert it to a different general textual representation format. In this scenario, you can either use SQL to convert to UNIX format and then use the rules file method to convert the timestamp to a different general textual representation format, or you can use SQL method.

Both methods are described at the end of this topic.

Method 1: Defining the format of the timestamp within the probe rules file

To define the format of the timestamp as described in Scenario 1 within the probe rules file, use the following steps:

Configure the SQL statement in the file specified by the SelectSqlFile property to specify the name of the column that the probe will use to create a timestamp for each event. For example, the following MS SQL command selects, and makes available for converting into a timestamp, the AlertDateTime from ISS SiteProtector:
Select AlertDateTime, SensorDataID, AlertName from SensorData1
Configure the rules file to specify the format that the probe will use for each timestamp field. For example, the following code in the rules file instructs the probe to convert the selected column from a yyyy-MM-dd hh:mm:ss textual representation into UNIX timestamp format:
```
if( exists ( $AlertDateTime ) ) {
### AlertDateTime original format "yyyy-MM-dd hh:mm:ss", 
### for example: 2013-01-29 10:45:00
$AlertDateTime = datetotime($AlertDateTime, "yyyy-MM-dd hh:mm:ss")
@FirstOccurrence =$AlertDateTime
@LastOccurrence = $AlertDateTime
}
```

Note: In Scenario 2, you can use the same method, but using the timetodate function instead of the datetotime function.

For details about using the datetotime function and the timetodate function within the probe rules file, see the Netcool/OMNIbus Probe and Gateway Guide.

Method 2: Defining the format of the timestamp within the SQL SELECT statement

To define the format of the timestamp as described in Scenario 1 within the SQL SELECT statement, use the following steps:

Configure the SQL statement in the file specified by the SelectSqlFile property to specify the name of the column that the probe will use to create a timestamp and to convert it into UNIX timestamp format. For example, the following MS SQL command selects AlertDateTime and converts it into UNIX timestamp format:
Select DATEDIFF(s, '19700101', AlertDateTime) AS UTC_AlertDateTime, SensorDataID, AlertName from SensorData1

Map the converted UNIX timestamp onto a Netcool/OMNIbus field. For example, the following code maps $UTC_AlertDateTime to a Netcool/OMNIbus field:

if( exists ( $UTC_AlertDateTime ) ) {
### AlerDateTime original format in UTC
@FirstOccurrence =$UTC_AlertDateTime
@LastOccurrence = $UTC_AlertDateTime
}

Note: In Scenario 3, you can use the same method, but using the CONVERT() function instead of the DATEDIFF() function. For example, to convert AlertDateTime from

yyyy-MM-dd
hh:mm:ss

format to MMM dd yyyy hh:mmAM(or PM) format, use the following MS SQL SELECT statement:

SELECT CONVERT(VARCHAR(24),AlertDateTime,100) AS AlertDateTime FROM SensorData1