topic Re: Database table doesn't have rising column totally? in Splunk Dev

Database table doesn't have rising column totally?

sieutruc — Mon, 10 Dec 2012 09:55:53 GMT

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 .

Re: Database table doesn't have rising column totally?

ziegfried — Mon, 10 Dec 2012 13:30:10 GMT

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.

Re: Database table doesn't have rising column totally?

sieutruc — Mon, 10 Dec 2012 14:52:24 GMT

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)

Re: Database table doesn't have rising column totally?

ziegfried — Mon, 10 Dec 2012 14:59:35 GMT

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.

Re: Database table doesn't have rising column totally?

sieutruc — Mon, 10 Dec 2012 15:46:38 GMT

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