Annotation Query, select multiple fields as tags

Howdy,

I am new to the annotation queries but understand that the main fields are event, event stop, text, and tags.

I am querying a ms sql database for event data to display as annotations in grafana. My query works as expected but I am only able to select one field as the tags type. When I attempt to select two fields as the tag type the query crashes as a 400 error.

My query below works as expected but I would really appreciate if someone had syntax or a method for selecting more than one column to produce more the than one tag

SELECT
m.event_start as time,
m.event as text,
m.siteid as tags

I have tried (), , {}, AND, &, and nothing has worked so far.

Hi @reisenrich1,

Welcome to the :grafana: community support forums !!

We are excited that you joined our OSS community. Please read about some of the FAQs in the community :slight_smile:

Did you already check the documentation regarding it i.e.

It should be possible to select 2 tags. For e.g. I use a test case and I was able to create multiple tags which got saved.

Also found more examples in the Documentation for Annotations (it is for MySQL but should be compatible with MSSQL) where you have to define the Annotation Query via Dashboard => Settings => Annotation => Select the MSSQL Datasource and define the query:

Let us know if this helps.

Hi @usman.ahmad
Could you please provide the syntax/query that you used to populate the annotations with multiple tags?
I think that is the answer @reisenrich1 (and myself) is looking for.

1 Like

I would love to see the example!

Found some examples from the Docs and also from an older post (but with a good explanation).

If this still does not work then let me know (especially via the Docs), I will then have to create a test machine and dig out further.

Looking forward to hear your feedbacks :slight_smile:

I am using influxdb as data source with grafana 10, and I can confirm that simple and range annotations with flux queries works as expected! What a great feature!

Hi @danielvamos how did you get it work with influxdb? I have a bucket with annotations data (value = 1) but in Grafana I see “no event found”, even if the hints at field selection show the data gathered from the query

from(bucket: "demo5.0")
  |> range(start: -12h)
  |> filter(fn: (r) => r["_measurement"] == "annotations")
  |> filter(fn: (r) => r["_field"] == "faults")

Hey,

The trick is to have two timestamp for each row in your query result and assign them to time and end time in your grafana mapping.

Here is my query:

// filter all events groupped by ref tag
cycles = from(bucket: vBucket)
  |> range(start: vStart, stop: vStop)
  |> filter(fn: (r) => r._measurement == vMeasurement)
  // some super secret query
  |> sort(columns: ["_time"], desc: false)
  //|> yield(name: "cycles")

// find the first, and the last cycle for a series of cycle with the same ref tag - this will be the rangeStart and rangeEnd values for annotation
firstCycle = cycles |> first() |> map(fn: (r) => ({ r with _field: "first", _value: r._time })) //|> yield(name: "first")
lastCycle =  cycles |> last()  |> map(fn: (r) => ({ r with _field: "last", _value: r._time })) //|> yield(name: "last") 

// Add the first and last values for each events with name first and last.
firstAndLast = union(tables: [firstCycle, lastCycle])
  |> pivot(rowKey:["tag1", "tag2", "tag3", "etc"], columnKey: ["_field"], valueColumn: "_value")
  |> group()
  |> sort(columns: ["_time"], desc: false) 
  |> map(fn: (r) => ({ r with ref: "ref: " + r.ref }))
  |> yield()

And this is how I map the query results to grafana

And one note, in order to make it work, the field value must be integer, i think… I am sure that I had some trouble with _value type. I had to convert it to integer. Please check.

Hope it helps!