Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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')
share|improve this question
add comment

1 Answer

up vote 10 down vote accepted

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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.