AnsweredAssumed Answered

SQL-query to get QoS-data of instances?

Question asked by rith on May 4, 2018
Latest reply on Jun 4, 2018 by AdrianJohnsonUK

So I will try to explain what we are looking for... please ask questions if it doesn't make sense



We are looking for the correct SQL to get QoS-data of ping, which is the value 0 or 1 (down/up).

What we want is to have an output that generates the time when the first value '0' arrives, and then the first value '1'. And then we will calculate the amount of minutes in between.


This is what we have to generate all the samples from last month. Samplevalue will be 0 or 1 (Ping).

Next thing we want is to get the first row with samplevalue = '0', and then first following samplevalue = '1'.

So the output should only be two rows.

SELECT source, s.samplevalue, s.sampletime from [NimsoftSLM].[dbo].[S_QOS_DATA] d

join [NimsoftSLM].[dbo].[RN_QOS_DATA_0021] s

on d.table_id = s.table_id

and target = 'PING-SLA'

and source = 'TEST01'

and s.samplevalue > 0

and s.sampletime > DATEADD(month, -1, GETDATE())

order by s.sampletime asc

Next thing would be to get more rows, if there would be more samples during the last month that had samplevalue = 0, under a different time span.