sql - Conditional Distinct -


Sorry for a little introduction, but I want to make it clear what I'm trying to do. If someone is able to come up with a more appropriate title, please feel free to edit.

I wrote an SNMP collector who checks every switch in our data center once per hour and checks which ports are online, and MS SQL 2's 12 DB DB inspires it It was often that administrators do not report a blocked server or any other device and we are running out of switch ports.

The DB schema looks like this (simplified screenshot):

db The schema

Interface table is a child's table for crawl (crawl = run) the number of the SNMP collector) is not constant for every switch as the number of interfaces but changes between the crawls, like That line card is included or removed.

Now I want to write a query that switch every interface that always had 2 ifOperStatus value and ifOperStatus of 1 was never there.

I have written a query of three nested sub-questions, it is slow to read and hell. There must definitely be an easy way.

My view was to filter the ports that have never been changed by using 

  (COUNT (DISTINCT dbo.interfaces.ifOperStatus) = 1) < / Code>  
 and during the last crawl, ifOperStatus 2 has an internal entry with a list of ports. Ugly, as I said.  
 Then, a sample output from DB will look like this:  
  Sample Questions   
 And I'm looking for a query that gives lines 5-7 because ifOperStatus never changes, but does not return lines 3-4 because ifOperStatus flapped. / p>  
 
 
 How about  
  HAVING (MIN (dbo.Interfaces. ifOperStatus) = 2 and max (dbo.interfaces.ifOperStatus) = 2)   
  MIN  and  MAX  The server is not required, one set of all the prices seen so far, the highest / lowest it can also be avoided the need to connect to the list of ports that had 2 ifOperStatus during the previous crawl.

Comments

Post a Comment

Popular posts from this blog

Java - Error: no suitable method found for add(int, java.lang.String) -

I'm in the middle of homework work and I'm stuck. At this point in my code, I think that I should have a GUI window that opens and allows me to type "inserted text number". Notice is not going anywhere but at this point, If I pass through the problem then it will be in a linked list. I am getting two of the same error for the lines. Add (index, element); And I can not seem to get past it, there is no suitable method for the error "add (int, java. string string)". Code is below, please advise. To clarify - this will not be a method error because it is a linked list. There should not be any way involved. import java.awt. *; Import java.awt.event. *; Import javax.swing. *; Import java.util. *; Import java.util.Scanner; Import java.util.LinkedList; Public class TopTenList JFrame {Private TopTenList tt; See the Private JTextArea list; Private JTextField cmdTextField; Private JTextField resultsTestfield; // This GUI window is the code for the public toptist...
Read more

java - JPA TypedQuery: Parameter value element did not match expected type -

I am using JPA 2.0 and getting the following code in the DAO layer: Public Zero Test () {string key = "status"; String [] Conditions = {"A", "B"}; & Lt; TestTable & gt; Results = Search (Keys, Conditions); } Public listing & lt; TestTable & gt; Search (string key, object value error) {string sql = "test ndf to testlet and jade nand." + Key + "in:" + key; TypedQuery & LT; TestTable & gt; Query = entityManager.createQuery (SQL, TestTable.class); Query.setParameter (key, Arrays.asList (valueArr)) / *** Error Line *** Return query.getResultList (); } In the above error line, it throws the following exception: java.lang.IllegalArgumentException: parameter value element [[Ljava.lang.String; @ cbe5bc] did not match the expected type [java.lang.String] Why is this expected type string while actually this string []? Please help! Note: This is derived from the normal routine and is a simplified code. I can no...
Read more

c++ - static template member variable has internal linkage but is not defined -

Yes, I know, there is a question with almost the same title, but it refers to a different situation. Error message) In my case, I have a .cpp file with a big named designation name (implementation details). There is a property class template with a static data member in that name space, which I need to access from outside the unknown namespace. I give it a little bit of meat: file.hpp namespace Bar {template & lt; Typename A & gt; Struct foo {static_assert (is_same & lt; a, float & gt; :: value} is_same & lt; a, double>: value, ""); Fixed zero set_static_var (a const & x); // ...}; } and file.cpp namespace {template & lt; Typename A & gt; Struct foo_traits {// supports the implementation of several static code bars: foo & lt; & Gt; Fixed A data; }; The template's & lt; & Gt; Float foo_traits & lt; Float & gt; :: datum; // No change if this is in the global namespace template & lt; & Gt; Doub...
Read more