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

[avivn]

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.

[acharlieh · Accepted solution]

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

[avivn]

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

[acharlieh]

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

[avivn]

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.

[dturnbull_splun]

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