Help With query

1

Hi

I have asingle table with 3 columns - computer name, Network interface name, and status of the interface.
Each computer has exactly 2 interfaces (1 and 2), and the status of the Interface could be only UP or DOWN.
This is the table and my qestion bellow:

COMPUTER-NAME INTERFACE-NAME Status
Computer1 Ethernert1 Down
Computer2 Ethernert1 Down
Computer3 Ethernert1 Up
Computer1 Ethernert2 Down
Computer2 Ethernert2 UP
Computer3 Ethernert2 Down

I need a query that retreive only if exactly one interface (per computer) is down.
For example it the table bellow the query should retreive "Computer 2" and "Computer3"
But if both interfaces are DOWN (or up) the query should not retreive nothing.

Thanks

2

SELECT [COMPUTER-NAME]
FROM [TheTable]
WHERE Status='Down'
GROUP BY [COMPUTER-NAME]
HAVING COUNT(*)=1;

4

It is not working if both interfaces are up. It's not provided in the sample data but it's possible that both interfaces are up.

5

hi

you are right .. Rogier

my Boo Boo

thank you for pointing it out

6

hi

hope this helps

another way of doing it

create data script

drop table if exists #Table

create table #Table (COMPUTER_NAME varchar(20) , INTERFACE_NAME varchar(20) , Status varchar(20) )

INSERT INTO #Table
SELECT 'Computer1','Ethernert1','Down' UNION ALL
SELECT 'Computer1','Ethernert2','Down' UNION ALL
SELECT 'Computer2','Ethernert1','Down' UNION ALL
SELECT 'Computer2','Ethernert2','Up' UNION ALL
SELECT 'Computer3','Ethernert1','Up' UNION ALL
SELECT 'Computer3','Ethernert2','Down' UNION ALL
SELECT 'Computer4','Ethernert1','Up' UNION ALL
SELECT 'Computer4','Ethernert2','Up'

select 
     COMPUTER_NAME 
from 
   #Table 
group by 
   COMPUTER_NAME 
having 
  min(status) <> max(status)

image

1 Like
7

Hi

Thanks, It was really helpfull.

I really appriciate help with something more difficult.
Several changes with the previos table:

  1. There is a new column name INTID, for each interface.
  2. There are 2 more interfaces for each computer. But the query only cares about Ethernet1 and Ethernet2. And again, only if exactly one of them is down.
  3. The select statment should contains 2 columns to show - INTID + InterfaceName (select intID, NT-NAME ...)

Is it possible in a single query?

The table should look like this:

C-NAME INT-NAME Status IntID
Computer1 Ethernert1 Down 1
Computer2 Ethernert1 Down 2
Computer3 Ethernert1 Up 3
Computer1 Ethernert2 Down 4
Computer2 Ethernert2 UP 5
Computer3 Ethernert2 Down 6
Computer1 Ethernert3 Down 7
Computer2 Ethernert3 UP 8
Computer3 Ethernert3 Down 9
Computer1 Ethernert4 UP 10
Computer2 Ethernert4 UP 11
Computer3 Ethernert4 UP 12

8

hi hope this helps

create data script

drop table if exists #Table

create table #Table (COMPUTER_NAME varchar(20) , INTERFACE_NAME varchar(20) , Status varchar(20) , IntID int )

INSERT INTO #Table
select 'Computer1','Ethernert1','Down', 1 union all
select 'Computer1','Ethernert2','Down', 4 union all
select 'Computer1','Ethernert3','Down', 7 union all
select 'Computer1','Ethernert4','Up', 10 union all
select 'Computer2','Ethernert1','Down', 2 union all
select 'Computer2','Ethernert2','Up', 5 union all
select 'Computer2','Ethernert3','Up', 8 union all
select 'Computer2','Ethernert4','Up', 11 union all
select 'Computer3','Ethernert1','Up', 3 union all
select 'Computer3','Ethernert2','Down', 6 union all
select 'Computer3','Ethernert3','Down', 9 union all
select 'Computer3','Ethernert4','Up', 12

 ; with cte as 
 (
select computer_name from #table where interface_name in ('ethernert1','ethernert2') group by computer_name having min(status) <> max(status)
 ) 
 select
   b.intid , b.interface_name 
from 
   cte a join #table b on a.computer_name = b.computer_name 
where 
    interface_name in ('ethernert1','ethernert2')

image