topic Splunk DB Connect 1: How to use the transaction command with dbquery? in Splunk Search

Splunk DB Connect 1: How to use the transaction command with dbquery?

avivn — Sun, 23 Aug 2015 15:34:32 GMT

Hi,

I am trying to run the transaction command on a SQL query with DB Connect 1.
My problem is when I am using the following query:

| dbquery <connection> <SQL Search> | transaction <file_id>

I get :
No results found

< file_id> - is the column I need to group by.
The Query itself is working if I remove the transaction part.

Also, If I run use transaction on an index, it's working fine.

Any help is appreciated,
Thanks.

Re: Splunk DB Connect 1: How to use the transaction command with dbquery?

acharlieh — Sun, 23 Aug 2015 18:26:18 GMT

If you do table file_id do you get the values you expect coming back or not? (do you have the case of the field name correct? does it actually have dots in it)? Do you want transaction or are you actually wanting to use stats e.g. stats count by file_id or stats list(*) as * by file_id or something else? (What are you wanting to do after the transaction command?)

Re: Splunk DB Connect 1: How to use the transaction command with dbquery?

avivn — Tue, 25 Aug 2015 08:57:56 GMT

First of all thanks for the help.
I tried to do | table * | transaction file_id and got no results as well.
I do need the functionality of the transaction because in the sql table, every time there is a status update in another table it adds a line to the table I am trying to query, So I need to have a single line of each file_id with the latest status data.
After the transaction function I want to continue work with other columns, but now I'll know I'm working on the latest status data.

Re: Splunk DB Connect 1: How to use the transaction command with dbquery?

dturnbull_splun — Tue, 25 Aug 2015 13:20:14 GMT

so if you are after the latest by an id, stats should help you:
| dbquery ... | stats latest(status) as latest latest(foo) as foo latest(bar) as bar by file_id

Re: Splunk DB Connect 1: How to use the transaction command with dbquery?

acharlieh — Tue, 29 Sep 2020 07:04:54 GMT

So you have a join of 2 tables in your SQL statement, and you're looking for the latest for each file_id... I'm assuming that when you run the query without the transaction the field you're looking to operate on is exactly file_id (both punctuation and case matter when it comes to field names). Now if your sql query sorts appropriately by status update, you could use dedup file_id instead of transaction. Or if your status update time field is correctly read as the _time as a unix timestamp, you might even be able to use stats latest(*) as * by file_id

Re: Splunk DB Connect 1: How to use the transaction command with dbquery?

avivn — Thu, 27 Aug 2015 08:38:38 GMT

Hey,
Ended up using dedup file_id instead of using transaction like @acharlieh suggested and it works great !
Thank you both for your help.