Splunk Search

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

splunkIT
Splunk Employee
Splunk Employee

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."

alt text

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?

Tags (1)

gkanapathy
Splunk Employee
Splunk Employee

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
Influencer

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.

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...