Remembering a value from a previous query

[billgraziano]

I tried searching for previous discussions but didn't see anything.

I have a situation where the application that writes to my database isn't going to be instrumented anytime soon. But I would like to get incremental transaction counts to add to a running total.

The only way I see to do that is get a count of rows since I last queried and add that to a counter metric.

SELECT MAX(TxnID), COUNT(*)
FROM dbo.Transactions
WHERE TxnID > :last_max_txnid

And that means keeping that max transaction ID from the last time we ran. My other alternative is to store this in a table and have a sql_exporter call a stored procedure that handles all this logic. I think that will work but haven't fully thought through it yet.

Storing it in sql_exporter kind of feels right but it would either need to persist the value somewhere or have logic to know if it is running for the first time.

Has anyone else done anything like this and been happy with the results?