This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SQL Question on comparing Custom Poller table values

Has anyone been able to create a SQL query that compares the current polled values of a custom poller table and historic values then report when any of the values of the rows have changed?  I've been trying to write something to compare rows based on custom poller assignment ID and rowID then report back of the current polled data held in the CustomPollerStatus table is different than data in either the CustomPollerStatuistics_Detail table or even the CustomPollerStatistics View.  Any thoughts would be we

  • Hi mharvey,

    I am also looking for same functionality. Any good luck with you? Could you please let me know if you found any work around for that?

    Thanks,

    Sateesh

  • well, if your custom poller is defined as a rate then:

    Select Top 1000 * From CustomPollerStatus where Rate <> RawStatus

    (or am I missing something here?)

    if you want to compare historical data the something like:

    SELECT TOP 1000 *

    FROM   custompollerstatus CPS

           INNER JOIN custompollerstatistics_detail CPSD

                   ON CPS.custompollerassignmentid = CPSD.custompollerassignmentid

                      AND CPS.rowid = CPSD.rowid

    WHERE  cps.rate = cps.rawstatus

           AND CPS.rawstatus <> CPSD.rawstatus

           AND CPSD.datetime = (SELECT Max(datetime)

                                FROM   custompollerstatistics_detail CPSD2

                                WHERE

                   CPSD2.custompollerassignmentid = CPSD.custompollerassignmentid

                   AND CPSD2.rowid = CPSD.rowid)



  • Thanks Richard.  The historical is what I was after.  This is exactly what I needed.  Thank you.

  • You're welcome; In the spirirt of more than one way to do something in SQL, the following query might run faster in some environments:

    SELECT TOP 1000 *

    FROM   custompollerstatus CPS

           INNER JOIN custompollerstatistics_detail CPSD

                   ON CPS.custompollerassignmentid = CPSD.custompollerassignmentid

                      AND CPS.rowid = CPSD.rowid

           INNER JOIN (SELECT Max(datetime) AS DateTime, custompollerassignmentid, rowid

                       FROM   custompollerstatistics_detail

                       GROUP  BY custompollerassignmentid, rowid) CPSD2

                   ON CPS.custompollerassignmentid = CPSD2.custompollerassignmentid

                      AND CPS.rowid = CPSD2.rowid

                      AND CPSD.datetime = CPSD2.datetime

    WHERE  CPS.rawstatus <> CPSD.rawstatus