→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
francisswest
Champion Sweeper

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.

2 REPLIES 2
Mister_Nobody
Honored Sweeper II

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

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