Report showing event errors of specific servers

[stuiver_5]

I want a report which shows windows event errors of specific defined servers. Grouped on server

[Susan_A · Accepted solution]

Please have a look at the modified report below, which lists errors and warnings. If you are interested in building or modifying reports, we would recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder uses standard SQL queries. This seems like a good tutorial.
  
• Updating to Lansweeper 5.2, if you haven't already. Lansweeper 5.2 includes a database dictionary, which is linked at the top of the report builder.

Select Top 1000000 tblNtlogSource.Sourcename As EventSource,
  tblAssets.AssetID,
  tblAssets.AssetName,
  Case tblNtlog.Eventtype When 1 Then 'Error' When 2 Then 'Warning'
    When 3 Then 'Information' When 4 Then 'Success Audit'
    When 5 Then 'Failure Audit' End As Type,
  tblNtlog.Eventcode,
  tblNtlogMessage.Message,
  Count(tblNtlog.EventlogID) As EventCount
From tblAssets
  Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
  Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
    tblNtlog.SourcenameID
  Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where Case tblNtlog.Eventtype When 1 Then 'Error' When 2 Then 'Warning'
    When 3 Then 'Information' When 4 Then 'Success Audit'
    When 5 Then 'Failure Audit'
  End In ('error', 'warning') And tblNtlog.TimeGenerated > GetDate() - 14 And
  tblComputersystem.Domainrole > 1
Group By tblNtlogSource.Sourcename,
  tblAssets.AssetID,
  tblAssets.AssetName,
  Case tblNtlog.Eventtype When 1 Then 'Error' When 2 Then 'Warning'
    When 3 Then 'Information' When 4 Then 'Success Audit'
    When 5 Then 'Failure Audit' End,
  tblNtlog.Eventcode,
  tblNtlogMessage.Message
Order By tblAssets.AssetName,
  EventCount Desc

[stuiver_5]

Hi,

Thanks, and how can I also filter on eventtype like only error events or warnings ?

[Daniel_B]

The following report lists events scanned per server during the past 14 days. If you would like to list events from only specific servers, add a filter criterium to tblAssets.Assetname (for example "In ('Server01', 'Server02', 'Server03')")

Select Top 1000000 tblNtlogSource.Sourcename As EventSource,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblNtlog.Eventcode,
  tblNtlogMessage.Message,
  Count(tblNtlog.EventlogID) As EventCount
From tblAssets
  Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
  Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
    tblNtlog.SourcenameID
  Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Where tblNtlog.TimeGenerated > GetDate() - 14
Group By tblNtlogSource.Sourcename,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblNtlog.Eventcode,
  tblNtlogMessage.Message
Order By tblAssets.AssetName,
  EventCount Desc