Deployment Architecture

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

photuris
Explorer

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?!

1 Solution

pmdba
Builder

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.

View solution in original post

pwesterbeek
Engager

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
0 Karma

justinfranks
Path Finder

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.

alt text

This works for me in the same situation.

pmdba
Builder

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
Explorer

@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
Path Finder

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

0 Karma

justinfranks
Path Finder

@nadid See my answer.

0 Karma
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 ...