cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Delvin
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

7 REPLIES 7
Delvin
Engaged Sweeper
Daniel, great answer! Thank you very much for your help!
Daniel_B
Lansweeper Alumni
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
Engaged Sweeper
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
Engaged Sweeper
Thank you very much, Daniel! This is exactly what I need! 😃
Daniel_B
Lansweeper Alumni
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
Delvin
Engaged Sweeper
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
Lansweeper Alumni
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