DX Infrastructure Management

Expand all | Collapse all

SQL-query to get QoS-data of instances?

  • 1.  SQL-query to get QoS-data of instances?

    Posted 05-04-2018 10:26 AM

    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.



  • 2.  Re: SQL-query to get QoS-data of instances?

    Posted 05-04-2018 11:31 AM

    Tried the query in my lab after modifying for the right names and get nothing returned. Could not figure out why it returns nothing when it should have returned plenty of rows.

     

    Is that the problem you are having?

     



  • 3.  Re: SQL-query to get QoS-data of instances?

    Posted 05-04-2018 11:43 AM

    It works fine here. Are you sure you have this right?

    "RN_QOS_DATA_XXXX"



  • 4.  Re: SQL-query to get QoS-data of instances?

    Posted 05-04-2018 11:48 AM

    Not concerned about it working for me.

    since it works for you, what is the question?



  • 5.  Re: SQL-query to get QoS-data of instances?

    Posted 05-04-2018 12:02 PM

    The questions is:

    From my example SQL-query... How do we get an output that only generates two rows. The first row should be the first row that contains samplevalue = '0', and the next row should contain the first following samplevalue = '1'.

     

    So if the result of my query would look like this. I want to catch the first value of 0, and then the first following value of 1. Marked in green...

     

     

    SourceSamplevalueTime
    TEST011.002018-04-04 17:40:47.000
    TEST011.002018-04-04 17:41:47.000
    TEST011.002018-04-04 17:42:47.000
    TEST010.002018-04-04 17:43:47.000
    TEST010.002018-04-04 17:44:47.000
    TEST010.002018-04-04 17:45:47.000
    TEST010.002018-04-04 17:46:47.000
    TEST011.002018-04-04 17:47:47.000
    TEST011.002018-04-04 17:48:47.000
    TEST011.002018-04-04 17:49:47.000
    TEST011.002018-04-04 17:50:47.000
    TEST011.002018-04-04 17:51:47.000
    TEST011.002018-04-04 17:52:47.000
    TEST011.002018-04-04 17:53:47.000

     

    The first value of '0' would be the time when the machine started to not response to Ping, and then the next time of value '1', would be when the machine was able to Ping again.



  • 6.  Re: SQL-query to get QoS-data of instances?

    Posted 05-10-2018 03:59 PM

    Thorin,

     

    I've no idea on that. Perhaps there is someone out there with some solid SQL experience that will know if it can be done.



  • 7.  Re: SQL-query to get QoS-data of instances?

    Posted 06-04-2018 07:50 AM

    You need to do a sub select on the same table which then compares one value (main query) with the value from the sub select (which gets the previous value).

    And select the rows which don't match.

    Should work OK although with the current indexes.



  • 8.  Re: SQL-query to get QoS-data of instances?

    Posted 06-04-2018 09:53 AM

    Hi rith

     

    I'm afraid that I only just saw this post - I hope this might still be useful.

     

    Assuming you are on at least SQL Server 2012, you can use the LAG() analysis function to get things to work as you wish.  I haven't tested this for performance on a fully populated UIM instance, but have used similar code in Oracle data warehouses many times.

     

    with my_qos_data as (

    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

     

    /*

                  select 'TEST01' as source, '2018-05-01 01:00' as sampletime, 1 as samplevalue

    union  select 'TEST01' as source, '2018-05-01 02:00' as sampletime, 1 as samplevalue

    union  select 'TEST01' as source, '2018-05-01 03:00' as sampletime, 1 as samplevalue

    union  select 'TEST01' as source, '2018-05-01 04:00' as sampletime, 0 as samplevalue

    union  select 'TEST01' as source, '2018-05-01 05:00' as sampletime, 0 as samplevalue

    union  select 'TEST01' as source, '2018-05-01 06:00' as sampletime, 0 as samplevalue

    union  select 'TEST01' as source, '2018-05-01 07:00' as sampletime, 0 as samplevalue

    union  select 'TEST01' as source, '2018-05-01 08:00' as sampletime, 0 as samplevalue

    union  select 'TEST01' as source, '2018-05-01 09:00' as sampletime, 1 as samplevalue

    union  select 'TEST01' as source, '2018-05-01 10:00' as sampletime, 1 as samplevalue

    union  select 'TEST01' as source, '2018-05-01 11:00' as sampletime, 1 as samplevalue

    union  select 'TEST01' as source, '2018-05-01 12:00' as sampletime, 1 as samplevalue

    */

    )

    , state_change as (

      select

           source

      , sampletime

      , samplevalue

      , samplevalue - (lag(samplevalue,1) over (partition by source order by sampletime)) as statechange

      from my_qos_data

    )

    select *

    from state_change

    where statechange <> 0

    ;

     

    Looking at the query above:

    • Firstly, it retains your original query as the first sub-select (my_qos_data) - you can ignore the commented out code, that was just to get something to work with to check the syntax was correct.
    • Next, it creates the second sub-select (state_change) uses the LAG() function to compare each row with the previous one (partitioned by source, ordered by sampletime) to determine any ‘state change' in the QOS value
    • Finally, it returns only those rows where a ‘state change' was detected.

     

    So, using the sample data in the /* commented code */, the output is:

     

     

    Hope this helps you, and others, get closer to what you were looking for.



  • 9.  Re: SQL-query to get QoS-data of instances?

    Posted 06-04-2018 04:28 AM

    Hi Thorin, my suggestion is to try posting your question on a SQL Expert forum.

    Let us know if you manage to solve this outside the UIM community!