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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...