Query to count events from two specific events

[gavinmarks]

Grafana version 9.4.3

I would like a time line chart or counter to show how many times a bit of facility has had its battery changed over a given time period

The best indication of this event is taken from two lines of data in the table

where the event can be captured by a ‘wifi alarm’ followed by a ‘wifi reset’ with a voltage increase of more than 1 volt.

I am able to get the data to capture one event, but I am not sure how to compare this to a previous event and verify the battery change over a short time.

SELECT * FROM AGV.dbo.AGVfault
WHERE $__timeFilter(TimeDate) AND Program <> 0 AND Program in ($Program) AND AGV in ($AGV) AND Fault = ‘wifi reset’ AND BattVoltage > 24.1
ORDER BY TimeDate desc

another possible way of detecting the event would be the two faults shown below occurring in the same minute, if this is easier to process?

End goal would be to display on a chart a star at a point in time (x) and the voltage (y) of a selected ‘AGV’ at any point the criteria is met.

Thanks for any advice

[grant2]

@gavinmarks What is your datasource?

[gavinmarks]

Microsoft SQL Server

[clevernessisamyth]

Hello,

have you tried the function LAG to get the previous row and compare it to the current one to check if there was a change or not?

[gavinmarks]

No I haven’t, although I cannot guarantee it will always be the last line as the query could return multiple values within the same time span.

can you give an example of how to apply the lag function? and I will give it a go.

Cheers

[clevernessisamyth]

Depending on what I have understood from your post. Here is a very basic example:

I have a customers table

I will calculate the number of times the age changes. For this I will use the lag function:

select age, lag(age, 1) over (order by customer_id) as previous_age
from Customers

And then I do the count:

select count(1) as nb_changes
from (
  select age, lag(age, 1) over (order by customer_id) as previous_age
  from Customers
)
where age != previous_age

[sicoval31]

Why not use the transform tab in a panel with the calculation one and the change count value ?

[gavinmarks]

Hi. I’m not really sure how to use the calculation tab?

I can manage to plot both query’s on the same chart to give a visual indication which shows the data I want to capture, but as you can see the time stamp for the two events, they are not exactly the same time, so I cant work out how to to capture this as one vent.

my solution so far is to use two query’s on the same chart and just use formatting to highlight where the two overlap. This works in a fashion, but is not very robust.

is it possible to combine the two query’s in to one, so that i can count the number of occurrences?

[yosiasz]

so you capture the event at hh:mm:second precision? and those two events happened exactly the same time?

[gavinmarks]

Hi. yes the data is captured at hh:mm:ss precision, and technically they occur at the same time, but due to delays in PLC comm’s they can be captured at slightly different times, +/- 3 seconds ish.

The events are alarm reset codes sent from AGV PLC’s to a master PLC then on to MySQL server, then to Grafana. The event I am trying to capture does not actually exist (battery change) as a single event, however by simulating the event I am able to view a selection of alarms generated and combine them to get the same result.

In a perfect world I would really like add in a voltage change, so before the events (V) = x then after the alarms (V) = x + >0.5

[yosiasz]

What is the unique value on all of those columns? Is there an eventid? Also please provide a proper ddl and dml. Help us help you, I doubt anyone will type all that data up from the picture to test things out in our local ms sql

[gavinmarks]

SELECT TimeDate, Program, AGV, CAST(BattVoltage AS real) as ‘WiFi Reset’ FROM AGV.dbo.AGVfault
WHERE $__timeFilter(TimeDate) AND Program <> 0 AND Program in ($Program) AND AGV in ($AGV) AND Fault = ‘wifi reset’ and BattVoltage > 24.0
ORDER BY TimeDate desc

SELECT TimeDate, Program, AGV, CAST(BattVoltage AS real) as ‘Low Bat Reset’ FROM AGV.dbo.AGVfault
WHERE $__timeFilter(TimeDate) AND Program <> 0 AND Program in ($Program) AND AGV in ($AGV) AND Fault = ‘lowbattery reset’ and BattVoltage > 24.0
ORDER BY TimeDate desc

SELECT TimeDate, Program, AGV, CAST(BattVoltage AS real) as ‘Voltage’ FROM AGV.dbo.AGVfault
WHERE $__timeFilter(TimeDate) AND Program <> 0 AND Program in ($Program) AND AGV in ($AGV)
ORDER BY TimeDate desc

[yosiasz]

That is not proper DDL and DML.
those are just queries of tables we have no acvess to. Also they do not answer the questions that were asked?