Get records not updated in last 30 minutes

I am trying to write a script that will get records that have not updated the table in more than 30 minutes.

Example records:

DNS_NAME    LAST_PERF_TIME
esxnu01 2013-12-24 12:10:00.000
esxnu02 2013-12-24 12:05:00.000
esxnu01 2013-12-24 12:15:00.000
esxnu03 2013-12-24 12:05:00.000
esxnu04 2013-12-24 12:20:00.000
esxnu02 2013-12-24 12:25:00.000

CREATE TABLE [dbo].[VPX_HOST](
[DNS_NAME] [nvarchar](255) NOT NULL,
[LAST_PERF_TIME] [datetime] NULL)

INSERT INTO [dbo].[VPX_HOST]
      ([DNS_NAME],[LAST_PERF_TIME])
 VALUES
      ('esxnu01', '2013-12-24 12:10:00.000')
      ,('esxnu02','2013-12-24 12:05:00.000') 
      ,('esxnu01','2013-12-24 12:15:00.000')
      ,('esxnu03','2013-12-24 12:05:00.000')
      ,('esxnu04','2013-12-24 12:20:00.000')
      ,('esxnu02','2013-12-24 12:25:00.000')

The same server logs multiple updates into the VPX_HOST table (which looks like VMware's Virtual Center, I'm guessing) so this gets a little tricky.

This is technically what you're asking for:

SELECT DISTINCT DNS_NAME
FROM dbo.VPX_HOST no_updates
WHERE NOT EXISTS (SELECT * FROM dbo.VPX_HOST updates WHERE no_updates.DNS_NAME = updates.DNS_NAME AND updates.LAST_PERF_TIME > DATEADD(mm, -30, GETDATE()))
ORDER BY DNS_NAME

But you won't actually like it because if you ever have a DNS_NAME drop offline permanently, like if you throw away a host, it'll keep showing up in here. So let's modify the query so that it only includes hosts that added an entry in the last day, but not in the last 30 minutes:

SELECT DISTINCT DNS_NAME
FROM dbo.VPX_HOST no_updates
WHERE no_updates.LAST_PERF_TIME > DATEADD(dd, -1, GETDATE())
AND NOT EXISTS (SELECT * FROM dbo.VPX_HOST updates WHERE no_updates.DNS_NAME = updates.DNS_NAME AND updates.LAST_PERF_TIME > DATEADD(mm, -30, GETDATE()))
ORDER BY DNS_NAME

That first WHERE clause on the 3rd line says only give me servers who have updated in the last day. If you wanted to go farther back, you could change the dd, -1 to something like dd, -3 to go 3 days back.

Edit about performance - be aware that this is going to scan the VPX_HOST table, so the bigger that table gets, the slower this query will get. If you're going to run it frequently, you'd probably want an index on (DNS_NAME, LAST_PERF_TIME).

StackExchange Database Administrators Q#55557, answer score: 10