Scanning Time Issues with Data Interpretations and Displays

Grafana Dashboards
1

Hi Grafana Community,

I am very new to Grafana and do not work in programming, so reading through some of the other posts on here I am sure my questions will be very rudimentary so my apologies in advance.

I work in a manufacturing facility and virtually all of our inline instrumentation data can be routed to grafana. I am currently building a dashboard for a certain piece of equipment where I am visualizing data from 3 level sensors, 2 pressure sensors, and a sequencer step number.

I am trying to create a gauge which counts the number of cycles the system has run during the selected timeframe. It seemed like the easiest way to do this was to use a filter by value and reduce to count transformation to count each time the system reaches a certain a certain step (lets say step 25 for example). However, due to our scanning time being about every 5 seconds it counts each of those scans as its own value and is reporting way higher counts than reality.

Is there an easy work around for this? Something to tell the system to only count each step 25 when it is a change in step and not when the previous scan also has step 25 as the step?

Again I am not working in any sort of coding or programming, I have no way to alter the data that grafana receives, so my options are limited to things that are available in dashboard creation in grafana.

Let me know if you have any suggestions!

Thank you.

2

Great detailed explanation but key parts missing are

  • your grafana version
  • your data source is it mysql? rest api?
  • sample data for each data type listed above
3

Thank you for the quick reply! My Grafana Version is 9.5.2, i believe our data source is mysql based but to be honest i am not entirely sure, and here is a sample data set for the step numbers that I am trying to utilize for this, you can see how it scans every 5 seconds and takes each scan as its own value so for example step 25 shows up 10 times in a row and i only want it to count that as 1 value

image
image923×624 46.1 KB

4

please provide your data as useable csv inline text and not as an image.

5

My apologies, like this?

time ElementName Step Number
6/5/2024 3:36 SDF001 50
6/5/2024 3:36 SDF001 50
6/5/2024 3:36 SDF001 50
6/5/2024 3:37 SDF001 50
6/5/2024 3:37 SDF001 50
6/5/2024 3:37 SDF001 50
6/5/2024 3:37 SDF001 50
6/5/2024 3:37 SDF001 50
6/5/2024 3:37 SDF001 50
6/5/2024 3:37 SDF001 50
6/5/2024 3:37 SDF001 55
6/5/2024 3:37 SDF001 55
6/5/2024 3:37 SDF001 55
6/5/2024 3:37 SDF001 55
6/5/2024 3:37 SDF001 55
6/5/2024 3:38 SDF001 55
6/5/2024 3:38 SDF001 55
6/5/2024 3:38 SDF001 5
6/5/2024 3:38 SDF001 15
6/5/2024 3:38 SDF001 15
6/5/2024 3:38 SDF001 15
6/5/2024 3:38 SDF001 25
6/5/2024 3:38 SDF001 25
6/5/2024 3:38 SDF001 25
6/5/2024 3:38 SDF001 25
6/5/2024 3:38 SDF001 25
6/5/2024 3:38 SDF001 25
6/5/2024 3:39 SDF001 25
6/5/2024 3:39 SDF001 25
6/5/2024 3:39 SDF001 25
6/5/2024 3:39 SDF001 25
6/5/2024 3:39 SDF001 25
6/5/2024 3:39 SDF001 25
6/5/2024 3:39 SDF001 25
6/5/2024 3:39 SDF001 25
6/5/2024 3:39 SDF001 25
6/5/2024 3:39 SDF001 25
6/5/2024 3:39 SDF001 25
6/5/2024 3:39 SDF001 25
6/5/2024 3:40 SDF001 25
6/5/2024 3:40 SDF001 25
6/5/2024 3:40 SDF001 25
6/5/2024 3:40 SDF001 25
6/5/2024 3:40 SDF001 25
6/5/2024 3:40 SDF001 25
6/5/2024 3:40 SDF001 25
6/5/2024 3:40 SDF001 25
6/5/2024 3:40 SDF001 25
6/5/2024 3:40 SDF001 25
6/5/2024 3:40 SDF001 25
6/5/2024 3:40 SDF001 25
6/5/2024 3:41 SDF001 25
6/5/2024 3:41 SDF001 25
6/5/2024 3:41 SDF001 25
6/5/2024 3:41 SDF001 25
6/5/2024 3:41 SDF001 35
6/5/2024 3:41 SDF001 35
6/5/2024 3:41 SDF001 35
6/5/2024 3:41 SDF001 35
6/5/2024 3:41 SDF001 35
6/5/2024 3:41 SDF001 35
6/5/2024 3:41 SDF001 35
6/5/2024 3:41 SDF001 35
6/5/2024 3:42 SDF001 35
6/5/2024 3:42 SDF001 35
6/5/2024 3:42 SDF001 35
6/5/2024 3:42 SDF001 35
6/5/2024 3:42 SDF001 35
6/5/2024 3:42 SDF001 35
6/5/2024 3:42 SDF001 35
6/5/2024 3:42 SDF001 35
6/5/2024 3:42 SDF001 35
6/5/2024 3:42 SDF001 35
6/5/2024 3:42 SDF001 35
6/5/2024 3:42 SDF001 35
6/5/2024 3:43 SDF001 35
6/5/2024 3:43 SDF001 35
6/5/2024 3:43 SDF001 35
6/5/2024 3:43 SDF001 35
6/5/2024 3:43 SDF001 35
6/5/2024 3:43 SDF001 35
6/5/2024 3:43 SDF001 35
6/5/2024 3:43 SDF001 35
6/5/2024 3:43 SDF001 35
6/5/2024 3:43 SDF001 35
6/5/2024 3:43 SDF001 35
6/5/2024 3:43 SDF001 35
6/5/2024 3:44 SDF001 35
6/5/2024 3:44 SDF001 35
6/5/2024 3:44 SDF001 35
6/5/2024 3:44 SDF001 35
6/5/2024 3:44 SDF001 35
6/5/2024 3:44 SDF001 35
6/5/2024 3:44 SDF001 35
6/5/2024 3:44 SDF001 35
6/5/2024 3:44 SDF001 35
6/5/2024 3:44 SDF001 35
6/5/2024 3:44 SDF001 35
6/5/2024 3:44 SDF001 35
6/5/2024 3:45 SDF001 35
6/5/2024 3:45 SDF001 35
6/5/2024 3:45 SDF001 35
6/5/2024 3:45 SDF001 35
6/5/2024 3:45 SDF001 35
6/5/2024 3:45 SDF001 35
6/5/2024 3:45 SDF001 35
6/5/2024 3:45 SDF001 35
6/5/2024 3:45 SDF001 35
6/5/2024 3:45 SDF001 35
6/5/2024 3:45 SDF001 35
6/5/2024 3:45 SDF001 35
6/5/2024 3:46 SDF001 35
6/5/2024 3:46 SDF001 35
6/5/2024 3:46 SDF001 35
6/5/2024 3:46 SDF001 35
6/5/2024 3:46 SDF001 35
6/5/2024 3:46 SDF001 35
6/5/2024 3:46 SDF001 35
6/5/2024 3:46 SDF001 35
6/5/2024 3:46 SDF001 35
6/5/2024 3:46 SDF001 35
6/5/2024 3:46 SDF001 35
6/5/2024 3:46 SDF001 35
6/5/2024 3:47 SDF001 35
6/5/2024 3:47 SDF001 35
6/5/2024 3:47 SDF001 35
6/5/2024 3:47 SDF001 35
6/5/2024 3:47 SDF001 35
6/5/2024 3:47 SDF001 35
6/5/2024 3:47 SDF001 35
6/5/2024 3:47 SDF001 35
6/5/2024 3:47 SDF001 35
6/5/2024 3:47 SDF001 35
6/5/2024 3:47 SDF001 35
6/5/2024 3:47 SDF001 35
6/5/2024 3:48 SDF001 35
6/5/2024 3:48 SDF001 35
6/5/2024 3:48 SDF001 35
6/5/2024 3:48 SDF001 35
6/5/2024 3:48 SDF001 35
6/5/2024 3:48 SDF001 35
6/5/2024 3:48 SDF001 35
6/5/2024 3:48 SDF001 35
6/5/2024 3:48 SDF001 35
6/5/2024 3:48 SDF001 35
6/5/2024 3:48 SDF001 35
6/5/2024 3:48 SDF001 35
6/5/2024 3:49 SDF001 35
6/5/2024 3:49 SDF001 35
6/5/2024 3:49 SDF001 35
6/5/2024 3:49 SDF001 35
6/5/2024 3:49 SDF001 35
6/5/2024 3:49 SDF001 35
6/5/2024 3:49 SDF001 35
6/5/2024 3:49 SDF001 35
6/5/2024 3:49 SDF001 35
6/5/2024 3:49 SDF001 35
6/5/2024 3:49 SDF001 35
6/5/2024 3:49 SDF001 35
6/5/2024 3:50 SDF001 35
6/5/2024 3:50 SDF001 35
6/5/2024 3:50 SDF001 35
6/5/2024 3:50 SDF001 35
6/5/2024 3:50 SDF001 35
6/5/2024 3:50 SDF001 35
6/5/2024 3:50 SDF001 35
6/5/2024 3:50 SDF001 35
6/5/2024 3:50 SDF001 35
6/5/2024 3:50 SDF001 35
6/5/2024 3:50 SDF001 35
6/5/2024 3:50 SDF001 35
6/5/2024 3:51 SDF001 35
6/5/2024 3:51 SDF001 35
6/5/2024 3:51 SDF001 35
6/5/2024 3:51 SDF001 35
6/5/2024 3:51 SDF001 35
6/5/2024 3:51 SDF001 35
6/5/2024 3:51 SDF001 40
6/5/2024 3:51 SDF001 40
6/5/2024 3:51 SDF001 40
6/5/2024 3:51 SDF001 40
6/5/2024 3:51 SDF001 40
6/5/2024 3:51 SDF001 40
6/5/2024 3:52 SDF001 40
6/5/2024 3:52 SDF001 40
6/5/2024 3:52 SDF001 40
6/5/2024 3:52 SDF001 40
6/5/2024 3:52 SDF001 40
6/5/2024 3:52 SDF001 40
6/5/2024 3:52 SDF001 40
6/5/2024 3:52 SDF001 40
6/5/2024 3:52 SDF001 40
6/5/2024 3:52 SDF001 40
6/5/2024 3:52 SDF001 40
1 Like
6

csv would have been better meaning comma delimited but this should work

7

is there a way to directly upload a CSV? it was saying the file type is not allowed

8 
date,ElementName,Step Number
6/5/2024 3:52,SDF001,40
6/5/2024 3:52,SDF001,40

etc but what you have is good for now.

with src
as
(
select '6/5/2024 3:37' as [time],
'SDF001' as ElementName,
50 [Step Number] union
select '6/5/2024 3:36','SDF001',50 union
select '6/5/2024 3:36','SDF001',50 union
select '6/5/2024 3:36','SDF001',50 union
select '6/5/2024 3:37','SDF001',50 union
select '6/5/2024 3:37','SDF001',50 union
select '6/5/2024 3:37','SDF001',50 union
select '6/5/2024 3:37','SDF001',50 union
select '6/5/2024 3:37','SDF001',50 union
select '6/5/2024 3:37','SDF001',50 union
select '6/5/2024 3:37','SDF001',55 union
select '6/5/2024 3:37','SDF001',55 union
select '6/5/2024 3:37','SDF001',55 union
select '6/5/2024 3:37','SDF001',55 union
select '6/5/2024 3:37','SDF001',55 union
select '6/5/2024 3:38','SDF001',55 union
select '6/5/2024 3:38','SDF001',55 union
select '6/5/2024 3:38','SDF001',5 union
select '6/5/2024 3:38','SDF001',15 union
select '6/5/2024 3:38','SDF001',15 union
select '6/5/2024 3:38','SDF001',15 union
select '6/5/2024 3:38','SDF001',25 union
select '6/5/2024 3:38','SDF001',25 union
select '6/5/2024 3:38','SDF001',25 union
select '6/5/2024 3:38','SDF001',25 union
select '6/5/2024 3:38','SDF001',25 union
select '6/5/2024 3:38','SDF001',25 union
select '6/5/2024 3:39','SDF001',25 union
select '6/5/2024 3:39','SDF001',25 union
select '6/5/2024 3:39','SDF001',25 union
select '6/5/2024 3:39','SDF001',25 union
select '6/5/2024 3:39','SDF001',25 union
select '6/5/2024 3:39','SDF001',25 union
select '6/5/2024 3:39','SDF001',25 union
select '6/5/2024 3:39','SDF001',25 union
select '6/5/2024 3:39','SDF001',25 union
select '6/5/2024 3:39','SDF001',25 union
select '6/5/2024 3:39','SDF001',25 union
select '6/5/2024 3:39','SDF001',25 union
select '6/5/2024 3:40','SDF001',25 union
select '6/5/2024 3:40','SDF001',25 union
select '6/5/2024 3:40','SDF001',25 union
select '6/5/2024 3:40','SDF001',25 union
select '6/5/2024 3:40','SDF001',25 union
select '6/5/2024 3:40','SDF001',25 union
select '6/5/2024 3:40','SDF001',25 union
select '6/5/2024 3:40','SDF001',25 union
select '6/5/2024 3:40','SDF001',25 union
select '6/5/2024 3:40','SDF001',25 union
select '6/5/2024 3:40','SDF001',25 union
select '6/5/2024 3:40','SDF001',25 union
select '6/5/2024 3:41','SDF001',25 union
select '6/5/2024 3:41','SDF001',25 union
select '6/5/2024 3:41','SDF001',25 union
select '6/5/2024 3:41','SDF001',25 union
select '6/5/2024 3:41','SDF001',35 union
select '6/5/2024 3:41','SDF001',35 union
select '6/5/2024 3:41','SDF001',35 union
select '6/5/2024 3:41','SDF001',35 union
select '6/5/2024 3:41','SDF001',35 union
select '6/5/2024 3:41','SDF001',35 union
select '6/5/2024 3:41','SDF001',35 union
select '6/5/2024 3:41','SDF001',35 union
select '6/5/2024 3:42','SDF001',35 union
select '6/5/2024 3:42','SDF001',35 union
select '6/5/2024 3:42','SDF001',35 union
select '6/5/2024 3:42','SDF001',35 union
select '6/5/2024 3:42','SDF001',35 union
select '6/5/2024 3:42','SDF001',35 union
select '6/5/2024 3:42','SDF001',35 union
select '6/5/2024 3:42','SDF001',35 union
select '6/5/2024 3:42','SDF001',35 union
select '6/5/2024 3:42','SDF001',35 union
select '6/5/2024 3:42','SDF001',35 union
select '6/5/2024 3:42','SDF001',35 union
select '6/5/2024 3:43','SDF001',35 union
select '6/5/2024 3:43','SDF001',35 union
select '6/5/2024 3:43','SDF001',35 union
select '6/5/2024 3:43','SDF001',35 union
select '6/5/2024 3:43','SDF001',35 union
select '6/5/2024 3:43','SDF001',35 union
select '6/5/2024 3:43','SDF001',35 union
select '6/5/2024 3:43','SDF001',35 union
select '6/5/2024 3:43','SDF001',35 union
select '6/5/2024 3:43','SDF001',35 union
select '6/5/2024 3:43','SDF001',35 union
select '6/5/2024 3:43','SDF001',35 union
select '6/5/2024 3:44','SDF001',35 union
select '6/5/2024 3:44','SDF001',35 union
select '6/5/2024 3:44','SDF001',35 union
select '6/5/2024 3:44','SDF001',35 union
select '6/5/2024 3:44','SDF001',35 union
select '6/5/2024 3:44','SDF001',35 union
select '6/5/2024 3:44','SDF001',35 union
select '6/5/2024 3:44','SDF001',35 union
select '6/5/2024 3:44','SDF001',35 union
select '6/5/2024 3:44','SDF001',35 union
select '6/5/2024 3:44','SDF001',35 union
select '6/5/2024 3:44','SDF001',35 union
select '6/5/2024 3:45','SDF001',35 union
select '6/5/2024 3:45','SDF001',35 union
select '6/5/2024 3:45','SDF001',35 union
select '6/5/2024 3:45','SDF001',35 union
select '6/5/2024 3:45','SDF001',35 union
select '6/5/2024 3:45','SDF001',35 union
select '6/5/2024 3:45','SDF001',35 union
select '6/5/2024 3:45','SDF001',35 union
select '6/5/2024 3:45','SDF001',35 union
select '6/5/2024 3:45','SDF001',35 union
select '6/5/2024 3:45','SDF001',35 union
select '6/5/2024 3:45','SDF001',35 union
select '6/5/2024 3:46','SDF001',35 union
select '6/5/2024 3:46','SDF001',35 union
select '6/5/2024 3:46','SDF001',35 union
select '6/5/2024 3:46','SDF001',35 union
select '6/5/2024 3:46','SDF001',35 union
select '6/5/2024 3:46','SDF001',35 union
select '6/5/2024 3:46','SDF001',35 union
select '6/5/2024 3:46','SDF001',35 union
select '6/5/2024 3:46','SDF001',35 union
select '6/5/2024 3:46','SDF001',35 union
select '6/5/2024 3:46','SDF001',35 union
select '6/5/2024 3:46','SDF001',35 union
select '6/5/2024 3:47','SDF001',35 union
select '6/5/2024 3:47','SDF001',35 union
select '6/5/2024 3:47','SDF001',35 union
select '6/5/2024 3:47','SDF001',35 union
select '6/5/2024 3:47','SDF001',35 union
select '6/5/2024 3:47','SDF001',35 union
select '6/5/2024 3:47','SDF001',35 union
select '6/5/2024 3:47','SDF001',35 union
select '6/5/2024 3:47','SDF001',35 union
select '6/5/2024 3:47','SDF001',35 union
select '6/5/2024 3:47','SDF001',35 union
select '6/5/2024 3:47','SDF001',35 union
select '6/5/2024 3:48','SDF001',35 union
select '6/5/2024 3:48','SDF001',35 union
select '6/5/2024 3:48','SDF001',35 union
select '6/5/2024 3:48','SDF001',35 union
select '6/5/2024 3:48','SDF001',35 union
select '6/5/2024 3:48','SDF001',35 union
select '6/5/2024 3:48','SDF001',35 union
select '6/5/2024 3:48','SDF001',35 union
select '6/5/2024 3:48','SDF001',35 union
select '6/5/2024 3:48','SDF001',35 union
select '6/5/2024 3:48','SDF001',35 union
select '6/5/2024 3:48','SDF001',35 union
select '6/5/2024 3:49','SDF001',35 union
select '6/5/2024 3:49','SDF001',35 union
select '6/5/2024 3:49','SDF001',35 union
select '6/5/2024 3:49','SDF001',35 union
select '6/5/2024 3:49','SDF001',35 union
select '6/5/2024 3:49','SDF001',35 union
select '6/5/2024 3:49','SDF001',35 union
select '6/5/2024 3:49','SDF001',35 union
select '6/5/2024 3:49','SDF001',35 union
select '6/5/2024 3:49','SDF001',35 union
select '6/5/2024 3:49','SDF001',35 union
select '6/5/2024 3:49','SDF001',35 union
select '6/5/2024 3:50','SDF001',35 union
select '6/5/2024 3:50','SDF001',35 union
select '6/5/2024 3:50','SDF001',35 union
select '6/5/2024 3:50','SDF001',35 union
select '6/5/2024 3:50','SDF001',35 union
select '6/5/2024 3:50','SDF001',35 union
select '6/5/2024 3:50','SDF001',35 union
select '6/5/2024 3:50','SDF001',35 union
select '6/5/2024 3:50','SDF001',35 union
select '6/5/2024 3:50','SDF001',35 union
select '6/5/2024 3:50','SDF001',35 union
select '6/5/2024 3:50','SDF001',35 union
select '6/5/2024 3:51','SDF001',35 union
select '6/5/2024 3:51','SDF001',35 union
select '6/5/2024 3:51','SDF001',35 union
select '6/5/2024 3:51','SDF001',35 union
select '6/5/2024 3:51','SDF001',35 union
select '6/5/2024 3:51','SDF001',35 union
select '6/5/2024 3:51','SDF001',40 union
select '6/5/2024 3:51','SDF001',40 union
select '6/5/2024 3:51','SDF001',40 union
select '6/5/2024 3:51','SDF001',40 union
select '6/5/2024 3:51','SDF001',40 union
select '6/5/2024 3:51','SDF001',40 union
select '6/5/2024 3:52','SDF001',40 union
select '6/5/2024 3:52','SDF001',40 union
select '6/5/2024 3:52','SDF001',40 union
select '6/5/2024 3:52','SDF001',40 union
select '6/5/2024 3:52','SDF001',40 union
select '6/5/2024 3:52','SDF001',40 union
select '6/5/2024 3:52','SDF001',40 union
select '6/5/2024 3:52','SDF001',40 union
select '6/5/2024 3:52','SDF001',40 union
select '6/5/2024 3:52','SDF001',40 union
select '7/5/2024 3:52','SDF001',40 
), con as (
select cast([time] as datetime) [time],
       ElementName as metric, 
	   [Step Number] as value,
	   ROW_NUMBER() 
OVER (PARTITION BY ElementName, [Step Number] 
ORDER BY [time] desc) AS intRow
 from src
 )
 select metric, [time], value
  from con
 where intRow = 1
   and $__timeFilter([time])

in ms sql you use

ROW_NUMBER() OVER (PARTITION BY ElementName, [Step Number] ORDER BY [time] desc) AS intRow
to partition the data so you can use it to eliminate duplicates

image
image1280×605 67.8 KB

1 Like