→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
d_i_saunders
Engaged Sweeper III
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

1 ACCEPTED SOLUTION
MikeMc
Champion Sweeper II
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

View solution in original post

2 REPLIES 2
d_i_saunders
Engaged Sweeper III
Thanks!
MikeMc
Champion Sweeper II
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