5 Replies Latest reply on Aug 5, 2013 1:22 PM by zackm

    SQL Query for Discovery Results

    zackm

      Wondering if anyone has had any luck with this:

       

      I am trying to get a report that will run each day for us that shows only 'FOUND' devices from our various Auto-Discoveries that run nightly.

       

      Here is what I have so far:

       

      SELECT Name, PrimaryAddress, Timestamp, IgnoredNodeID, d.NodeID
      FROM DiscoveryNodes d (nolock)
      WHERE Timestamp > 1375690020
      AND
      IgnoredNodeID IS NULL
      AND
      ProfileID IN ('2','3','4','10','11','12','13','14','59','60')
      

       

      Obviously, the Timestamp data is manually entered Epoch time in the current query (I will work on a conversion later once I can prove this works).

       

      The problem I have is this: That Epoch time is 4:07am EDT, 8/5/13. In my web interface, I am showing 10 devices discovered last night/this morning, with the earliest being at 4:07am EDT.

       

      HOWEVER, this query returns 431 devices.

       

      I cannot seem to find a table where I can limit my results based on pre-existing devices vs new devices.

       

      Ideas/Thoughts/Opinions??? (please do not say "just use the web interface") 

        • Re: SQL Query for Discovery Results
          njoylif

          without being knee deep in this...

          select unique(<whatever>) from <your query> join nodes n on d.nodeid=n.nodeid

          or

          select * from discoverednodes where nodeid not in (select nodeid from nodes)

           

          off top of my head...

          1 of 1 people found this helpful
            • Re: SQL Query for Discovery Results
              zackm

              I had thought of that, but still end up with the 431 devices listed and only 10 nodes in the Scheduled Discovery Results list on the Web.

               

              SELECT Name, PrimaryAddress, Timestamp, IgnoredNodeID, d.NodeID
              FROM DiscoveryNodes d (nolock)
              WHERE d.NodeID NOT IN (
                  SELECT n.NodeID from Nodes n)
              AND
              Timestamp > 1375690020
              AND
              IgnoredNodeID IS NULL
              AND
              ProfileID IN ('2','3','4','10','11','12','13','14','59','60')