CODEWITHSUNDEEP
databasesqlite
GdD
GdD

Reputation: 241

Find differences between table entries in Sqlite3

I have a table which contains host system information for a bunch of servers. Every month a script will add hosts and system information to the table with a new entry date. The same server may have multiple entries, each with a different entry date. What I would like to do is use queries to find differences rather than having to pull the data and munch it programmatically. I have 2 main queries I'd like to do:

  1. Find hostnames that have been added and dropped between data entry dates. Every month some servers are new, and some are gone compared to last month. How do I find these hostnames?
  2. Sometimes certain values change for the host data between entry dates, installed java versions being an example. Can I query for rows where a certain value has changed between entry dates?

The table name is hostdata, with fields: hostname TEXT, os TEXT, javaversions TEXT, entrydate TEXT

Upvotes: 0

Views: 72

Answers (1)

CL.
CL.

Reputation: 180060

  1. You could use EXISTS with a subquery. This will return new hosts:

    SELECT *
FROM hostdata
WHERE entrydate = 'thismonth'
  AND NOT EXISTS (SELECT 1
                  FROM hostdata AS last
                  WHERE entrydate = 'lastmonth'
                    AND last.hostname = hostdata.hostname)

    (To get dropped hosts, exchange the month values.)

    However, using a compound query might be simpler:

    SELECT hostname
FROM hostdata
WHERE entrydate = 'thismonth'
EXCEPT
SELECT hostname
FROM hostdata
WHERE entrydate = 'lastmonth'

  2. This is typically done by joining the table with itself, i.e., joining last month's data with this month's data:

    SELECT last.hostname,
       last.os,
       last.javaversions,
       this.javaversions
FROM hostdata AS last
JOIN hostdata AS this ON last.entrydate = 'lastmonth'
                     AND this.entrydate = 'thismonth'
                     AND last.hostname = this.hostname
WHERE last.javaversions <> this.javaversions

Upvotes: 2

Related Questions