Eventlog summary report for all servers

[Delvin]

Hello everyone!
I found a great report, that show me practically everything I want, but I need to add some more things to show like tblNtlog.Eventcode and tblNtlogMessage.Message/ I tried to add them myself, but I had no luck in it... Is it possible to add them in this report?

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

[Delvin]

Daniel, great answer! Thank you very much for your help!

[Daniel_B]

Your report originally contained the AssetID. This field won't be listed in reports, it is only being used to create links to asset pages if you add tblAssets.Assetname as well.

If you would like to count the numbers of same events on all your machines, remove the AssetID from the Select and Group part of the report query:

Select Top 1000000 tblNtlogSource.Sourcename As EventSource,
  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() - 1
Group By tblNtlogSource.Sourcename,
  tblNtlog.Eventcode,
  tblNtlogMessage.Message
Order By EventCount Desc

[Delvin]

Hello everyone again!
The report is working good, but I noticed 1 problem. I turned on scan for warnings events, too(only errors by default). And the thing is some simillar events are being wrote in my report with different EventCount. Looks like this:

EventSource Eventcode Eventtype Message EventCount
Microsoft-Windows-GroupPolicy 1085 Warning Windows failed to apply the Internet Explorer Zonemapping settings. 344
Microsoft-Windows-GroupPolicy 1085 Warning Windows failed to apply the Internet Explorer Zonemapping settings. 342
Microsoft-Windows-GroupPolicy 1085 Warning Windows failed to apply the Internet Explorer Zonemapping settings. 338
Microsoft-Windows-GroupPolicy 1085 Warning Windows failed to apply the Internet Explorer Zonemapping settings. 324
Microsoft-Windows-GroupPolicy 1085 Warning Windows failed to apply the Internet Explorer Zonemapping settings. 310
Microsoft-Windows-GroupPolicy 1085 Warning Windows failed to apply the Internet Explorer Zonemapping settings. 306
Microsoft-Windows-GroupPolicy 1085 Warning Windows failed to apply the Internet Explorer Zonemapping settings. 305

Thanks for answering!

[Delvin]

Thank you very much, Daniel! This is exactly what I need! 😃

[Daniel_B · Accepted solution]

In this case, add tblNtlog.Eventcode and tblNtlogMessage.Message to your report and enable grouping for these fields:

Select Top 1000000 tblNtlogSource.Sourcename As EventSource,
  tblAssets.AssetID,
  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() - 1
Group By tblNtlogSource.Sourcename,
  tblAssets.AssetID,
  tblNtlog.Eventcode,
  tblNtlogMessage.Message
Order By EventCount Desc

[Lamsweeper]

Hi @Daniel_B ,

Do you also perhaps know how to add the host name of each server to this report?

Thank you in advance! 😊

[Delvin]

Daniel, thank you for a fast answer.
I need a count of all events. In my report I can get it, but I see only amounts of them and Eventsource. I will show, how it looks like

EventSource EventCount
Disk 671
Disk 583
Disk 187

You see, that I have three Eventsource "Disk", but errors are differ. That`s why I want to see not only counts and sources. I need to see the description of errors and eventID.
Thanks for the replies! 😃

[Daniel_B]

The report you mentioned lists a count of events. Do you need to have individual events listed (like the example below), or a count of events?

Select Top 1000000 tblNtlog.TimeGenerated,
  tblNtlogSource.Sourcename As EventSource,
  tblAssets.AssetID,
  tblNtlog.Eventcode,
  tblNtlogMessage.Message
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() - 1
Order By tblNtlog.TimeGenerated Desc