Splunk Dev

Database table doesn't have rising column totally?

sieutruc
Contributor

Hello,

My table doesn't have column with an inreasing value totally. It has ID, but that ID changes such that it often increases and sometimes decrease (but its value is unique), so i think it's not appropriate to be a rising column.

There is another timestamp field,it often increases and sometimes keeps the same value when 2 events arrive at the same time.

So my question is how to monitor my table ? Is that timestamp field possible to be rising column ?

I need your help .

0 Karma
1 Solution

ziegfried
Influencer

Multiple entries with the same rising-column value should not be a problem. The only thing where this might becomes a problem is when one of the entries with the same value is inserted into the table after the other one has already been fetched by DB Connect. There is a workaround for this situation by specifying a custom SQL statement for the database input:

SELECT * FROM mytable WHERE $rising_column$ < sysdate-(1/1440) {{AND $rising_column$ > ?}} ORDER BY $rising_column$

This forces that entries with a current timestamp are not fetched immediately, but the one of the subsequent invocations of the database input, when the timestamp is at least one minute older than the current system time. The query syntax is database specific. The example here is for Oracle.

View solution in original post

ziegfried
Influencer

Multiple entries with the same rising-column value should not be a problem. The only thing where this might becomes a problem is when one of the entries with the same value is inserted into the table after the other one has already been fetched by DB Connect. There is a workaround for this situation by specifying a custom SQL statement for the database input:

SELECT * FROM mytable WHERE $rising_column$ < sysdate-(1/1440) {{AND $rising_column$ > ?}} ORDER BY $rising_column$

This forces that entries with a current timestamp are not fetched immediately, but the one of the subsequent invocations of the database input, when the timestamp is at least one minute older than the current system time. The query syntax is database specific. The example here is for Oracle.

sieutruc
Contributor

sorry for my mistake :), it's worked now , thanks for your help

0 Karma

ziegfried
Influencer

You actually don't need to replace $rising_column$. It's automatically substituted with the column you specify as rising column in the input.

Your custom sql query should rather look something like:

SELECT * FROM mytable WHERE $rising_column$ < dateadd(minute,-1,getdate()) {{AND $rising_column$ > ?}} ORDER BY $rising_column$

Note that dateadd is used with -1 instead of 1, so entries are only fetched if they are at least one minute old.

0 Karma

sieutruc
Contributor

My rising column is modifiedTime, do i need to replace $rising_column$ by modifiedTime to have

SELECT * FROM mytable WHERE modifiedTime < dateadd(minute,1,getdate()) {{AND modifiedTime > ?}} ORDER BY modifiedTime

?? (MSSQL)

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...