DB Connect: How to create a trigger to synthesize raising column values?

[splunkIT]

In that Database Input view:
Splunk>Manager>>Data>>Data Inputs>>new, under the Tail Input -
Rising Column field, there is a brief explanation:

" Choose a column with an increasing value. Such as a creation or
modification timestamp or a sequential identifier. You can also create a
trigger to synthesize such a value."

What is intended by "create a trigger to synthesize such a value"? Is it referring to something that can be done in Splunk, or in our database?

[gkanapathy]

As ziegfried says, this refers to modifications to the database schema. Besides using a trigger, some database servers can may be able to do something using "virtual columns" or "computed columns" (e.g., to convert a textual only time stamp into a numeric or datetime value). But this approach can be used without modifying the database by defining it in Splunk via a SELECT instead of changing the schema.

[ziegfried]

Here’s an example use-case:

You have a table "MYTABLE" which does not contain any column that is viable for being used as the rising column for a tail database input. You want to index all newly inserted rows into the table. The suggested approach is to alter the schema and add a new column (SPLUNK_RISINGCOL in the example below). Then create a trigger that automatically sets the value of this column for newly inserted rows based on a sequence, so new rows will always get a bigger value.

The modifications need to be done on the database itself, not in the Splunk side. Of course this can only be done if the database schema can be modified.

The details on how to setup such a trigger are very specific to the kind of database. Here’s an example for Oracle:

ALTER TABLE MYTABLE ADD COLUMN SPLUNK_RISINGCOL NUMBER(11);

CREATE SEQUENCE SEQ_MYTABLE_SPLUNK_RISINGCOL START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER MYTABLE_SPLUNK_RISINGCOL
BEFORE INSERT ON MYTABLE
FOR EACH ROW
BEGIN
    SELECT SEQ_MYTABLE_SPLUNK_RISINGCOL.NEXTVAL INTO :NEW.SPLUNK_RISINGCOL FROM DUAL;
END;

Similar approaches can be used for updated rows as well by creating an BEFORE UPDATE trigger.