0

I would like to get the latest record for each server.

Here is some example data:

TimeGenerated        SourceName            ComputerName  Message
2014-11-22 21:48:30  Windows Update Agent  Server1       Update Failed
2014-11-22 21:42:30  Windows Update Agent  Server2       Update Failed
2014-11-22 21:45:30  Windows Update Agent  Server2       Update Failed
2014-11-22 21:43:30  Windows Update Agent  Server1       Update Failed

Desired Output:

TimeGenerated        SourceName            ComputerName  Message
2014-11-22 21:48:30  Windows Update Agent  Server1       Update Failed
2014-11-22 21:45:30  Windows Update Agent  Server2       Update Failed

I tried:

SELECT * FROM TABLE 
GROUP BY ComputerName 
ORDER BY TimeGenerated ASC

But that outputs inconsistent results and does not give me the latest in most cases.

I also tried some sub queries, but failed miserably.

outis
  • 75,655
  • 22
  • 151
  • 221
sspeed
  • 19
  • 3

3 Answers3

1
SELECT *
FROM yourtable
INNER JOIN (
   SELECT MAX(timeGenerated) as maxtime, ComputerName
   FROM yourtable
   GROUP BY ComputerName
) AS latest_record ON (yourtable.timeGenerated = maxtime)
   AND (latest_record.ComputerName = yourtable.ComputerName)

Inner query gets the latest timestamp for every computer name. The outer query then joins against that query result to fetch the rest of the fields from the table, based on the time/computername the inner query finds. If you have two events logged with identical max times, you'd get two records for that computername.

Marc B
  • 356,200
  • 43
  • 426
  • 500
0

Try this:

SELECT ComputerName, Max(date)
FROM TABLE
GROUP BY ComputerName
SMA
  • 36,381
  • 8
  • 49
  • 73
  • This also appears to work. I thought I'd received inconsistent data without a join on a subquery before, but this seems to work too. – sspeed Nov 24 '14 at 17:39
0

I you only use a GROUP function, you can only display the columns which are part of that SELECT statement. If you need more data displayed, you need to work with a subquery. For example, if you want to "select *", you can't just use only a GROUP BY and no subquery. So, it depends what you want to display.

tvCa
  • 796
  • 6
  • 13