Can't extract a value from log line to present in a stat-visual on dashboard

[dbnl]

First of all: I’m quite a newbee to Grafana, but I have read a lot of documentation and experimented with it, but I’m now running into an issue that I can’t solve.
Need to mention that I have no influence on the data that I get, can’t have anything changed there (I’m more kind of an end-user).

I have the following (example) of log lines; looks like there are no labels or whatsoever available to me, so need to do it with this:

Common labels: {"filename":"/var/log/usage.log","job":"varlogs","service_name":"varlogs"}

2024-09-06 06:50:07.510	Sep  6 07:50:07 f-prd PlotServer[502]: Connection: 85 licenses remain
2024-09-06 06:49:59.225	Sep  6 07:49:58 f-prd PlotServer[502]: Connection: 84 licenses remain
2024-09-06 06:13:36.323	Sep  6 07:13:36 f-prd PlotServer[502]: Connection: 85 licenses remain
2024-09-06 06:13:25.541	Sep  6 07:13:25 f-prd PlotServer[502]: Connection: 84 licenses remain
2024-09-06 05:06:33.797	Sep  6 06:06:33 f-prd PlotServer[502]: Connection: 85 licenses remain
2024-09-06 04:58:20.048	Sep  6 05:58:19 f-prd PlotServer[502]: Connection: 84 licenses remain

(there are many more other lines, with other types of messages in this same log file, so this is already a filtered view)

What I need is to have the number after “Connection:”, and ultimately I want to display the number from the most recent log line in a stat visual on a dashboard. In this example it would be 85.

I’m perfectly able to filter only these lines, but then I’m stuck.

I believe “last_over_time” should return me at least the exact line I’m looking for, but I can’t get that to live. I’ll draw out the steps I’ve done:

last_over_time({filename="/var/log/usage.log"} |~ `(?i)Connection: ([0-9]+) licenses remain` [$__auto])

This returns a parse error: invalid aggregation last_over_time without unwrap.

Ok, from what I understand from this, is that I need label the value that I’m looking for and tell Loki that it’s a numeric value.

last_over_time({filename="/var/log/usage.log"} |~ `(?i)Connection: (?P<lic>[0-9]+) licenses remain` | unwrap lic [$__auto])

Result: no data

I have consulted ChatGPT as well for this, but running in circles there as well.
Options that ChatGPT has suggested:

last_over_time(
{filename="/var/log/usage.log"} 
| regexp `(?i)Connection: (?P<lic>\d{2}.\d) licenses remain` 
| line_format "license={{.lic}}" 
| logfmt
| unwrap license
[$__auto])

Result: no data

last_over_time(
{filename="/var/log/usage.log"} 
|~ `Connection: (?P<lic>[0-9]+) licenses remain`
| unwrap lic
| label_format license={{.lic}}
[$__auto])

Result: syntax error: unexpected label_format, expecting IDENTIFIER or (

I’m stuck. Anyone with suggestions to extract that one number?

[tonyswumac]

Your last query was almost correct. Try this:

last_over_time(
  {filename="/var/log/usage.log"} 
    | regexp `Connection: (?P<lic>[0-9]+) licenses remain`
    | label_format license="{{.lic}}"
    | unwrap lic
  [$__auto]
)

[dbnl]

Thanks @tonyswumac for thinking along with me

Running the query gives me a time-series. I did however expect a single value as the query result, that’s why I’m using last_over_time. But that is maybe my wrong understanding of last_over_time?

Anyway, I’m now using your query in a stat visual on my dashboard, I added a Transformation “Reduce” with Mode “Series to rows” and Calculations “Last” to it, and it seems I get the correct value. I’ll follow it in my logs to see if this indeeds reflects the one result that I’m expecting at all times.

I was actually also expecting that I would be able to refer to a label “license” in a Transformation, but that probably doesn’t work as I expect either…

[tonyswumac]

last_over_time returns a time series metrics, yes. However, if you only ever want 1 result you can configure your graph to only ask for 1 result (thereby forcing the interval to be the duration of the dashboard), and setting your query to be instant.

Example:

[dbnl]

Thanks @tonyswumac, all clear to me now. And I managed to get it to work, thanks to your first comment.