Splunk DB Connect: How to use $rising_column$ as an aliased column?

[photuris]

Ok, y'all, I'm in bit of a pickle, and I need a little help out of this conundrum.

So I'm using DB Connect (dbx/dbmon-tail) to fetch data from a SQL Server database into Splunk, periodically updating using the field referenced by $rising_column$ as the incremented datetime column. So far, so good!

Here's the issue:

1) dbx, which is underpinned by JDBC, doesn't allow one to alias columns (SELECT foo AS bar), but instead uses the actual column name in the result set. One can trick it with CAST() though (SELECT CAST(foo AS varchar) AS BAR), to rename a column in the resultset.

2) However, according to SQL rules, one cannot use an aliased column in a WHERE clause. Bummer.

So, why do I want to use aliased columns, specifically $rising_column$ as an aliased column?

Because in my source tables, I'm JOINing a related table with a column name that's the same as the timestamp column in my source table!

1) If I don't alias my column, I get an ambiguous column error when filtering with $rising_column$.

2) If I do alias my column, I can't line it up with $rising_column$, because JDBC doesn't return aliases (unless I trick it with CAST()), and even then, SQL won't let me place aliases in WHERE clauses.

Here's an example:

SELECT
  f.id, f.bar_id, f.name, b.value, f.modified_timestamp
FROM foo f
  LEFT JOIN bar b              -- bar has `modified_timestamp` also!!!
  ON f.bar_id = b.id
{{WHERE $rising_column$ > ?}}  -- `f.modified_timestamp`
;

I can't specify f.modified_timestamp as $rising_column$, because JDBC only returns modified_timestamp as the column name!

I can't specify modified_timestamp because it's an ambiguous column, and will break my query!

I can't alias modified_timestamp using the CAST() trick, because I can't use aliased columns in WHERE clauses!

What to do?!

[pwesterbeek]

I had the same problem and I solved it by putting an extra select statement around the query.

Original
SELECT
f.id, f.bar_id, f.name, b.value, f.modified_timestamp
FROM foo f
LEFT JOIN bar b -- bar has modified_timestamp also!!!
ON f.bar_id = b.id
{{WHERE $rising_column$ > ?}} -- f.modified_timestamp
;

My solution:

select * from (
    SELECT
       f.id, f.bar_id, f.name, b.value, f.modified_timestamp as RisingColumnPar
     FROM foo f
       LEFT JOIN bar b              -- bar has `modified_timestamp` also!!!
       ON f.bar_id = b.id
) X 
 {{WHERE $rising_column$ > ?}}  

 In  Tail Input Settings : set   "Rising Column" to RisingColumnPar

[justinfranks]

I know this is old but someone reading this might find it helpful.

You can use alias's with the rising column.

{{WHERE f.$rising_column$ > ?}}

and then in the tail input settings you place the field name.

This works for me in the same situation.

[pmdba · Accepted solution]

I would suggest creating a view in the database with the column names you want, aliased how you want, and then have Splunk select from the view. This allows you also to keep the most complex part of your code (the view query) in the database where you can modify it easily, and to keep your Splunk DBX input as a basic, no-frills select statement.

[photuris]

@pmdba nailed it! Just created a view like this:

CREATE VIEW monitor_foo AS
  SELECT
   f.id, f.bar_id, f.name, b.value, f.modified_timestamp
  FROM foo f
  LEFT JOIN bar b
  ON f.bar_id = b.id
;

Now I can filter, rename columns, etc. without complains from JDBC. Awesome! Thanks!

[nadid]

What if you don't have create view access rights. Is there any way without modifying the database?

[justinfranks]

@nadid See my answer.