Report of Event Log Errors including "STATEMENT" over time period

[francisswest]

I had a need to find a specific event log error on some of our servers, and count them up to provide a quick glance at how many times a service is failing on a server.  This specifies the server name type (for instance, if all of your database servers include the phrase 'dbserver' in them), scans for the event log message including "MSG HERE", adds them together, and gives a count for each error amount of specified assets.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname As OS,
  tblAssets.Lastseen As [Last successful scan],
  Count(tblNtlog.Eventcode) As [Error Count]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblNtlog On tblNtlog.AssetID = tblAssets.AssetID
  Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
  Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
      tblNtlog.SourcenameID
  Inner Join tblNtlogUser On tblNtlogUser.LoguserID = tblNtlog.LoguserID
  Inner Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetName Like '%dbserver%' And Case tblNtlog.Eventtype
    When 1 Then 'Error'
    When 2 Then 'Warning'
    When 3 Then 'Information'
    When 4 Then 'Security Audit Success'
    When 5 Then 'Security Audit Failure'
  End = 'Error' And tblNtlog.TimeGenerated > GetDate() - 14 And
  tblState.Statename = 'Active' And
  tblNtlogMessage.Message Like '%PUTWHATERRORYOUWANTTOGETAREPORTOF%'
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Lastseen
Order By [Error Count] Desc

 Replace "dbserver" with the asset name.  Replace "PUTWHATERRORYOUWANTTOGETAREPORTOF" with some specific language from the event log error.  Replace "14" with however many days you want it to count backwards.

[Mister_Nobody]

Don't join tables which inuse. It leads to high usage of SQL Server resources.

[francisswest]

Oof, didnt know that, thanks!  Do you have a suggestion on how the report could be improved to reduce server load?