Adding Make and Model to Report for Failing HDD

[d_i_saunders]

Hi all,

can someone please help me add the make and model to the following report, SQL is not my thing

Thanks in advance 🙂

Select Top (50) Count(tblAssets.AssetName) As BadBlocks,
  tblAssets.AssetName,
  tblAssets.Username
From tblNtlogMessage
  Inner Join tblNtlog On tblNtlogMessage.MessageID = tblNtlog.MessageID
  Inner Join tblAssets On tblAssets.AssetID = tblNtlog.AssetID
Where tblNtlogMessage.Message Like '%has a bad block%'
Group By tblAssets.AssetName,
  tblAssets.Username
Order By BadBlocks Desc

[d_i_saunders]

Thanks!

[MikeMc · Accepted solution]

Try this query:

Select Top 100000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  T1.BadBlocks,
  T1.LastBadBlockReported
From tblAssets
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join (Select tblNtlog.AssetID,
    Count(tblNtlog.AssetID) As BadBlocks,
    Max(tblNtlog.TimeGenerated) As LastBadBlockReported
  From tblNtlog
    Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
  Where tblNtlogMessage.Message Like '%has a bad block%'
  Group By tblNtlog.AssetID) T1 On T1.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By T1.BadBlocks Desc,
  T1.LastBadBlockReported Desc