Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2012
    Posts
    8

    Unanswered: Need to only return rows in 5 minute intervals

    I have collected perfmon data that is in every 15 seconds. I need to run a query that will only retrun rows that are 5 minutes from the last row starting at a specific date/time.

    Here is the current query

    Select
    DisplayString,
    MachineName,
    ObjectName,
    CounterName,
    InstanceName,
    CounterValue,
    CAST(LEFT(CounterDateTime,LEN(CounterDateTime)-5) AS DATETIME) AS CounterDateTime
    FROM CounterData
    JOIN CounterDetails ON CounterData.CounterID = CounterDetails.CounterID
    JOIN DisplayToID ON CounterData.GUID = DisplayToID.GUID
    WHERE CAST(LEFT(CounterDateTime,LEN(CounterDateTime)-5) AS DATETIME) > '2012-03-02 11:59:00'
    AND CAST(LEFT(CounterDateTime,LEN(CounterDateTime)-5) AS DATETIME) < '2012-03-02 17:01:00'

    "CounterDateTime" is in every 15 seconds. So starting from '2012-03-02 11:59:00' I need only rows for every 5 minutes after that.

    Any ideas on how to accomplish this?
    THANKS!
    ~ Drew

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What a complete and utter erroneous decision..care to explain WHY?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2012
    Posts
    8

    Replay with the "Why"

    Well, the data I need to compare was already collected. The new data is from a VDI Management tool that collected IOPS data from some VDI's for a project. This VDI tool only collects every 5 minutes.

    Since I am dealing with Historical perfmon data, and I need to compare apples to apples (Old perfmon data to new VDI data) I need a subset of the query which is rows only every 5 minutes.

    Why erronious decision?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What makes you think that the data every 5 minutes is going to have any meaning?

    Is it ONLY the same data?

    You need to show us a sample
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2012
    Posts
    8

    Sample

    Sure, here is a sample of what the query currently returns. These are every 15 seconds. Every 5 minutes will allow me to average the read write values so I can compare to this new VDI tool to see if what its getting is accurate.

    DisplayString MachineName ObjectName CounterName InstanceName CounterValue CounterDateTime
    VDI-E34484_DataCollector0120120306-000004 \\VDI-E34484 PhysicalDisk Disk Reads/sec 0 C: 0 2012-03-06 11:40:11.000 11
    VDI-E34484_DataCollector0120120306-000004 \\VDI-E34484 PhysicalDisk Disk Writes/sec 0 C: 0 2012-03-06 11:40:11.000 11
    VDI-E34484_DataCollector0120120306-000004 \\VDI-E34484 PhysicalDisk Current Disk Queue Length 0 C: 0 2012-03-06 11:40:11.000 11
    VDI-E34484_DataCollector0120120306-000004 \\VDI-E34484 PhysicalDisk Disk Transfers/sec 0 C: 0 2012-03-06 11:40:11.000 11
    VDI-E34484_DataCollector0120120306-000004 \\VDI-E34484 PhysicalDisk Disk Transfers/sec 0 C: 7.36470380160291 2012-03-06 11:40:26.000 26
    VDI-E34484_DataCollector0120120306-000004 \\VDI-E34484 PhysicalDisk Current Disk Queue Length 0 C: 0 2012-03-06 11:40:26.000 26
    VDI-E34484_DataCollector0120120306-000004 \\VDI-E34484 PhysicalDisk Disk Writes/sec 0 C: 6.81917018666937 2012-03-06 11:40:26.000 26
    VDI-E34484_DataCollector0120120306-000004 \\VDI-E34484 PhysicalDisk Disk Reads/sec 0 C: 0.545533614933549 2012-03-06 11:40:26.000 26
    VDI-E34484_DataCollector0120120306-000004 \\VDI-E34484 PhysicalDisk Disk Reads/sec 0 C: 0.2000002979899 2012-03-06 11:40:41.000 41
    VDI-E34484_DataCollector0120120306-000004 \\VDI-E34484 PhysicalDisk Disk Writes/sec 0 C: 2.000002979899 2012-03-06 11:40:41.000 41
    VDI-E34484_DataCollector0120120306-000004 \\VDI-E34484 PhysicalDisk Current Disk Queue Length 0 C: 0 2012-03-06 11:40:41.000 41
    VDI-E34484_DataCollector0120120306-000004 \\VDI-E34484 PhysicalDisk Disk Transfers/sec 0 C: 2.2000032778889 2012-03-06 11:40:41.000 41
    VDI-E34484_DataCollector0120120306-000004 \\VDI-E34484 PhysicalDisk Disk Transfers/sec 0 C: 3.26688241783157 2012-03-06 11:40:56.000 56

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you mean you want to SUM() or AVG() the values in 15 minute Increments?

    I didn't get that from the original post if that's the case

    Is it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2012
    Posts
    8
    No, its every 5 minutes that I need.

    So starting at time X only return rows where Counter Value is "Disk Read/sec" every 5 minutes.

    So really is a sampling of the data. Returning every row gives me every 15 seconds. What is need is every 5 minutes.

    Once I have that data I will be doing an average of counter value in a pivot chart in excel. But thats after I can get the data for every 5 minutes.

    Thanks so much for your assistance!

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Every 5 minutes...starting when?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Mar 2012
    Posts
    8
    CAST(LEFT(CounterDateTime,LEN(CounterDateTime)-5) AS DATETIME) > '2012-03-02 11:59:00'

    So in this case I would start at 11:59:00 3/2/2012.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I guess that this

    DECLARE @d datetime; SET @d = '2012-03-06 11:41:11.000'
    SELECT DATEPART(mi,@d)/5.00

    Where anything is a whole number would be 5 minute intervals

    I guess
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Mar 2012
    Posts
    8
    I was playing around with this in my query. How would I add this to my where clause. I defined the variable at the top. I see that the second select returns 8.20000. Trying to determine best way to apply this to the query.
    THANKS SO MUCH!

  12. #12
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Just curious to know why you aren't using a between clause in where statement?

  13. #13
    Join Date
    Mar 2012
    Posts
    8
    Just did not think about it, but just tried that and it works fine also for getting the date range.

  14. #14
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Helps make the code a little more clear.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What is BETWEEN going to do for Oppie?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •