Compare rows of a table MYSQL and output differences

Question

So, complete MYSQL newb here. I'm almost embarrassed to post this question, but here it goes:

So, I have the following table...

CREATE TABLE IF NOT EXISTS cran_cisco (
id int not null auto_increment,
device_fqdn varchar(250) DEFAULT 0,
device_ip varchar(250) DEFAULT 0,
link_state varchar(250) DEFAULT 0,
line_protocol varchar(250) DEFAULT 0,
description varchar(250) DEFAULT 0,
date timestamp default now(),
PRIMARY KEY(id)
);

And I have created an external script to add the following information, which will come four times a day. Here's an excerpt of what it might look like:

insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
    values ('test_box_2', '10.10.10.2', 'up', 'up', 'this is a test interface');
insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
    values ('test_box_2', '10.10.10.2', 'up', 'down', 'this is a test interface');
insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
    values ('test_box_2', '10.10.10.2', 'up', 'up', 'this is a test interface');
insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
    values ('test_box_2', '10.10.10.2', 'up', 'down', 'this is a test interface');
insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
    values ('test_box_2', '10.10.10.2', 'up', 'down', 'this is a test interface');
insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
    values ('test_box_2', '10.10.10.2', 'up', 'up', 'this is a test interface');
insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
    values ('test_box_2', '10.10.10.2', 'up', 'up', 'this is a test interface');
insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
    values ('test_box_2', '10.10.10.2', 'up', 'down', 'this is a test interface');
insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
    values ('test_box_2', '10.10.10.2', 'up', 'up', 'this is a test interface');

The table would then look like this:

| id | device_fqdn | device_ip  | link_state | line_protocol | description              | date                |
+----+-------------+------------+------------+---------------+--------------------------+---------------------+
|  1 | test_box_2  | 10.10.10.2 | up         | down          | this is a test interface | 2016-10-07 08:16:42 |
|  2 | test_box_2  | 10.10.10.2 | up         | up            | this is a test interface | 2016-10-07 08:17:22 |
|  3 | test_box_2  | 10.10.10.2 | up         | up            | this is a test interface | 2016-10-07 08:23:55 |
|  4 | test_box_2  | 10.10.10.2 | up         | down          | this is a test interface | 2016-10-07 08:23:55 |
|  5 | test_box_2  | 10.10.10.2 | up         | up            | this is a test interface | 2016-10-07 08:23:55 |
|  6 | test_box_2  | 10.10.10.2 | up         | up            | this is a test interface | 2016-10-07 08:23:55 |
|  7 | test_box_2  | 10.10.10.2 | up         | down          | this is a test interface | 2016-10-07 08:23:55 |
|  8 | test_box_2  | 10.10.10.2 | up         | up            | this is a test interface | 2016-10-07 08:23:55 |
|  9 | test_box_2  | 10.10.10.2 | up         | up            | this is a test interface | 2016-10-07 08:23:55 |
| 10 | test_box_2  | 10.10.10.2 | up         | down          | this is a test interface | 2016-10-07 08:23:55 |
| 11 | test_box_2  | 10.10.10.2 | up         | up            | this is a test interface | 2016-10-07 08:23:57 |
+----+-------------+------------+------------+---------------+--------------------------+---------------------+

I would like to develop a query to report every time the line protocol or link went down. The expect output should be something like this:

| id | device_fqdn | device_ip  | link_state | line_protocol | description              | date                |
+----+-------------+------------+------------+---------------+--------------------------+---------------------+
|  1 | test_box_2  | 10.10.10.2 | up         | down          | this is a test interface | 2016-10-07 08:16:42 |
|  4 | test_box_2  | 10.10.10.2 | up         | down          | this is a test interface | 2016-10-07 08:23:55 |
|  7 | test_box_2  | 10.10.10.2 | up         | down          | this is a test interface | 2016-10-07 08:23:55 |
| 10 | test_box_2  | 10.10.10.2 | up         | down          | this is a test interface | 2016-10-07 08:23:55 |
+----+-------------+------------+------------+---------------+--------------------------+---------------------+

Any help would be greatly appreciated. Thank you in advance.

As promised, the answer to my question is below. I have another question coming, but it's dealing with how to put all of that into a bash script. lol

SELECT  'cran_juniper' AS `set`, c.*
FROM    cran_juniper c
WHERE   ROW(c.device_fqdn, c.device_ip, c.interface, c.admin_state, c.link_state, c.description) NOT IN
        (
        SELECT  device_fqdn, device_ip, interface, admin_state, link_state, description
        FROM    cran_juniper_baseline
        )
UNION ALL
SELECT  'cran_juniper_baseline' AS `set`, b.*
FROM    cran_juniper_baseline b
WHERE   ROW(b.device_fqdn, b.device_ip, b.interface, b.admin_state, b.link_state, b.description) NOT IN
        (
        SELECT  device_fqdn, device_ip, interface, admin_state, link_state, description
        FROM    cran_juniper
        )
into outfile 'today.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
;

I guess you want to compare every row with the previous row for that device and see if link_state and/or line_protocol has changed from up to down, correct? — verhie
– verhie, Commented Oct 7, 2016 at 13:09

EoinS · Accepted Answer · 2016-10-07 13:41:13Z

1

You can use an OR condition in a WHERE clause to check this:

SELECT * from cran_cisco WHERE LINK_STATE = 'down' OR LINE_PROTOCOL = 'down'

If you are trying to accomplish the results as described in the comments, you could try ordering by data and device and creating LAG equivalents to track when a device first goes down:

set @lags = 'start' ;   
set @lagp = 'start' ;  
select id, device_fqdn, device_ip, link_state, line_protocol, description, date from (
select *, @lags,@lagp, @lags:=link_state, @lagp := line_protocol from cran_cisco order by device_fqdn, date) c 
where (link_state = 'down' and @lags = 'up' ) or ( line_protocol = 'down' and @lagp = 'up')

Each lag variable displays the previous rows value. We then pull from that result only where link_state or line_protocol is down and the previous row was up.

Here is an functional example

edited Oct 7, 2016 at 13:41

answered Oct 7, 2016 at 12:50

EoinS

5,4901 gold badge21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andy D'Arata Over a year ago

My friend, you answered the question correctly. I asked it wrong. I will post up a new one. Thank you very much.

EoinS Over a year ago

@AndyD'Arata very kind of you but let's solve your problem ;)

Andy D'Arata Over a year ago

I figured out the answer through TONS Of research! I will add the answer to the bottom of the post so you can see what was going through my mind. lol

piyush pankaj · Accepted Answer · 2016-10-07 12:59:47Z

0

Hope this helps

select * cran_cisco where line_protocol='down' or link_state='down'

edited Oct 7, 2016 at 12:59

answered Oct 7, 2016 at 12:50

piyush pankaj

7551 gold badge13 silver badges24 bronze badges

Collectives™ on Stack Overflow

Compare rows of a table MYSQL and output differences

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related