Get uptime as a percentage of selected time window

Grafana MSSQL
1

I am struggling on how to craft a query to figure out my machine uptime.
I have a system which logs machine state every 10 seconds. If the machine is running, it’ll be a 1, otherwise it’ll be a zero.

The problem is, how do I count times where there are no entries? The OPC server that is feeding the SQL server just won’t log any entry if the machine is offline. Obviously there won’t be any entries either if the SQL server is down.

For accurate data and uptime percentages, I want to count these “missing rows” (records at 10 second intervals that don’t actually exist) as 0’s.

I have a pi chart with the following query which accurately counts the 1’s & 0’s but the machine was turned completely off for several hours, so the ratio is way off.

SELECT
  0 As time, /*Create a column named 'time' filled with zeros, grafana REALLY wants a time column.*/
  count(IS_RUNNING) as 'Counts', /*Create a column called 'Counts' that holds the sample count of each state.*/
  cast(IS_RUNNING as nvarchar(max)) as 'State' /*Create a column called 'State' that displays what value each count is.*/
FROM
  AV_01_LIVE /*Data table selection.*/
WHERE
  TIME BETWEEN '2022-08-11T10:24:59Z' AND '2022-08-11T13:24:59Z' /*Filter by time selection.*/
GROUP BY IS_RUNNING /*Command to group the data appropriately.*/

Any help on how to alter this? Thank you.

2

Welcome

Can you please provide DDL and DML for this table as follows, with a good amount of sample data

create table #AV_01_LIVE(IS_RUNNING bit, TIME  datetime)

insert #AV_01_LIVE
values(1, '2022-08-11T10:24:59Z')

etc.

We dont have access to your database so doing the above will help us help you. Otherwise it would be guess work and back and forth.

Maybe you can run with this

declare @date datetime = '2022-08-11 00:00:00.000';

;with src
as
(
SELECT TOP (10000)        
		TIME   = dateadd(ss,ISNULL(CAST(
                          ROW_NUMBER() OVER (ORDER BY (SELECT 1))
                       AS INT),0)*10, @date)
		

   FROM master.sys.all_columns ac1
  CROSS JOIN master.sys.all_columns ac2
  CROSS JOIN master.sys.all_columns ac3
), fillgaps as 
(
select 
   case 
       when a.TIME is null then src.TIME
	   else a.TIME end as TIME,
 case 
       when a.TIME is null then 0
	   else a.IS_RUNNING end as IS_RUNNING
  from src
  left join AV_01_LIVE a  on a.TIME = src.TIME
  )
  SELECT
  count(1) as 'Counts',
  cast(IS_RUNNING as nvarchar(max)) as 'State'
FROM fillgaps
WHERE TIME BETWEEN '2022-08-11T10:24:59Z' 
     AND '2022-08-11T13:24:59Z'
GROUP BY IS_RUNNING
3

Wow,
Thank you for the feedback and actually giving a good whack at it.

I am a controls engineer, so my main forte is PLCs & C#, I am somewhat out of my depth with SQL statements but am rapidly getting on board. I’ll study what you’ve sent closely tomorrow and try to understand how to make it work. I really appreciate it.

Here is the DDL script for the table.

CREATE TABLE [dbo].[AV_01_LIVE](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[TIME] [datetime] NULL,
	[IS_RUNNING] [smallint] NULL,
	[PART_NUMBER] [varchar](6) NULL,
	[QUANTITY_GOOD_BATCH] [int] NULL,
	[QUANTITY_BAD_BATCH] [int] NULL,
	[QUANTITY_TOTAL_BATCH] [int] NULL,
	[QUANTITY_GOOD_SHIFT] [int] NULL,
	[QUANTITY_BAD_SHIFT] [int] NULL,
	[QUANTITY_TOTAL_SHIFT] [int] NULL,
	[JOB_NUMBER] [varchar](16) NULL,
	[TIME_SINCE_LAST_STOP] [real] NULL,
	[JOB_UPTIME] [real] NULL,
 CONSTRAINT [PK_AV_01_LIVE] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

As for the requested DML, I guess I don’t exactly understand what you’re requesting on that one…Sorry. Are you asking for like a INSERT statement so you can create a dummy dataset? If so, see below. If not, please excuse my ignorance.
EDIT I reread your reply several times, I think this IS what you’re looking for.

INSERT INTO [dbo].[AV_01_LIVE]
           ([TIME]
           ,[IS_RUNNING]
           ,[PART_NUMBER]
           ,[QUANTITY_GOOD_BATCH]
           ,[QUANTITY_BAD_BATCH]
           ,[QUANTITY_TOTAL_BATCH]
           ,[QUANTITY_GOOD_SHIFT]
           ,[QUANTITY_BAD_SHIFT]
           ,[QUANTITY_TOTAL_SHIFT]
           ,[JOB_NUMBER]
           ,[TIME_SINCE_LAST_STOP]
           ,[JOB_UPTIME])
     VALUES
           ('2022-08-10 13:16:10.227',
           '1',
           'AB1234',
           '1000',
           '1000',
           '1000',
           '1000',
           '1000',
           '1000',
           'S12345',
           '3.14159',
           '3.14159')

Again, I appreciate the help and I’ll chew over what you’ve given me and get back to you.

1 Like
4

Nice on the DDL and DML! Cause we cant read your mind nor your database, telepathic Jedi skills have deteriorated during lockdown. Providing DDL and DML helps us get your data into our database.

if at all possible work with your DBA/SQL dev to sort this out into a stored procedure. This is more of a SQL thing than a grafana thing.

image
image738×327 54.4 KB

Also I would name the State with a more meaningful value than just casting IS_RUNNING to the yuge nvarchar(max)

5

You’re talking to the SQL dev.
I’m my company’s (400ish employees) sole programmer, who typically sticks to industrial machines, so I am just problem solving my way through the current assignment and picking up the skills as needed. I will investigate stored procedures as my primary option and continue to pick apart what you’ve already sent me.

As for your last line, I didn’t really understand how the nvarchar(max) worked. I found a solution that got me to the goal then I went through and tried to comment every line with my best interpretation of how its working. I didn’t understand how the CAST method created a separate column but I ran mutliple variants of the query and thats the best I could sues out what was happening… I’m very much in the “try something, fall flat on my face, try again” phase of things…I’ve made good progress but yeah, lots of code snippet copying then trying to figure out whats happening.

Thanks for your help!

1 Like
6

Ok, I figured it out. The PRINT statement was my salvation. It allowed me to brute force my way through to get where I needed. Here is the code. I REALLY APPRECIATE YOUR HELP.

/*Get the start time window.*/
/*declare @START_TIME as DateTime = $__timeFrom();*/
declare @START_TIME as DateTime = '2022-08-15T11:20:01Z';
PRINT 'Start time: ' + CAST(@START_TIME AS VARCHAR);

/*Get the end time window.*/
/*declare @END_TIME as DateTime = $__timeTo();*/
declare @END_TIME as DateTime = '2022-08-15T11:21:43Z';
PRINT 'End time: ' + CAST(@END_TIME AS VARCHAR);

/*Define what the machine sample rate is (in seconds).*/
declare @SAMPLE_RATE smallint = 10;
PRINT 'Sample rate: ' + CAST(@SAMPLE_RATE AS VARCHAR) + ' seconds' ;  

/*Now that we have some local variables set up, We need to figure out how many entries SHOULD exist within the given time frame.*/

/*Get the expected number of records.*/
declare @EXPECTED_RECORD_COUNT int = ROUND(CAST(DATEDIFF(ss, @START_TIME, @END_TIME) AS real) /  @SAMPLE_RATE, 0 , 0) + 1; 
/*Needed to add 1 and round to get correct value, may be a index 0 vs index 1 issue or I dunno, it works, no more brain power for why.*/
PRINT 'Expected record count: ' + CAST(@EXPECTED_RECORD_COUNT AS VARCHAR);  

/*Now we need to figure out how many records ACTUALLY exist in the given time frame*/
declare @ACTUAL_RECORD_COUNT int;
SELECT
	@ACTUAL_RECORD_COUNT = COUNT(ID)
FROM
	/*$MACHINE_DB_LIVE*/
	AV_01_LIVE
WHERE
	TIME BETWEEN @START_TIME AND @END_TIME
PRINT 'Actual record count: ' + CAST(@ACTUAL_RECORD_COUNT AS VARCHAR);

/*Figure out how many records are missing*/
declare @MISSING_RECORD_COUNT int = @EXPECTED_RECORD_COUNT - @ACTUAL_RECORD_COUNT;
PRINT 'Missing record count: ' + CAST(@MISSING_RECORD_COUNT AS VARCHAR);

/*Get the counts of machine states, cast into a temporary table @TEMPORARY_TABLE */
declare @TEMPORARY_TABLE TABLE(COUNTS int, STATE smallint)
INSERT INTO @TEMPORARY_TABLE
SELECT
	count(IS_RUNNING),
	IS_RUNNING
FROM
	/*$MACHINE_DB_LIVE*/
	AV_01_LIVE 
WHERE
	TIME BETWEEN @START_TIME AND @END_TIME
GROUP BY IS_RUNNING

/*Insert a row into the temporary table representing the missing entries.*/
INSERT INTO @TEMPORARY_TABLE(COUNTS, STATE)
VALUES(@MISSING_RECORD_COUNT,2);

/*Return the modified table & pivot for Grafana*/
SELECT  
	[0], [1], [2]
FROM(SELECT STATE, COUNTS FROM @TEMPORARY_TABLE) AS SOURCE_TABLE
PIVOT(
	AVG(COUNTS)
	FOR STATE IN ([0], [1], [2])
)
AS PIVOT_TABLE;

EDIT
Here is the resulting chart based off of my live data. Note that I broke off “offline” into its own slice.
Untitled

1 Like