cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
stuiver_5
Engaged Sweeper
I want a report which shows windows event errors of specific defined servers. Grouped on server
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
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

View solution in original post

3 REPLIES 3
Susan_A
Lansweeper Alumni
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
Engaged Sweeper
Hi,

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

Daniel_B
Lansweeper Alumni
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