Report for EventID XXXX

[SirArion]

I have a query to collect occurances of a particular Event ID but I would like to only see one instance of each computer that is having the problem, not a listing for every trigger in the event log.

Any suggestions on fixing my query?

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tsysOS.OSname,
  tblAssets.SP,
  tblNtlog.Eventcode,
  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 Eventtype,
  tblNtlogFile.Logfile,
  tblNtlogMessage.Message,
  tblNtlogSource.Sourcename,
  tblNtlogUser.Loguser,
  tblNtlog.TimeGenerated
From tblAssets
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
  Inner Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
  Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
  Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
    tblNtlog.SourcenameID
  Inner Join tblNtlogUser On tblNtlogUser.LoguserID = tblNtlog.LoguserID
Where tblNtlog.Eventcode = '10016' And tblNtlog.TimeGenerated > GetDate() - 45
Order By tblNtlog.TimeGenerated Desc

[smaher]

Hello,

I'm having trouble creating this report. When I save & run I get the error:

There was an error parsing the query. [Token line number = 1, Token line offset = 453, Token in error - last time occurred]

Would you have any suggestion to help fix this issues?

Thanks

[Hemoco]

smaher wrote:
Hello,

I'm having trouble creating this report. When I save & run I get the error:

There was an error parsing the query. [Token line number = 1, Token line offset = 453, Token in error - last time occurred]

Would you have any suggestion to help fix this issues?

Thanks

The report we posted is only compatible with SQL Server databases. If you are using an SQL Compact database, you can try the query posted here instead: http://lansweeper.com/forum/yaf_postst7051_Report-help.aspx#post29969

[SirArion]

Excellent report. Works great, thank you for it.

Would be a nice feature if the event logs would link to this kind of report. In other words, looking at an individual machines event log and being able to get a report of every machine you have that is experiencing the problem is immensely helpful in troubleshooting.

[Hemoco · Accepted solution]

Hi,

Use the report down here for the information you're after:

Select Distinct Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tsysOS.OSname,
tblAssets.SP,
tblNtlog.Eventcode,
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 Eventtype,
tblNtlogFile.Logfile,
tblNtlogMessage.Message,
tblNtlogSource.Sourcename,
tblNtlogUser.Loguser,
q1.max As 'last time occured'
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogUser On tblNtlogUser.LoguserID = tblNtlog.LoguserID
Left Join (Select Max(tblNtlog.TimeGenerated) As 'max',
tblNtlog.AssetID As assetid
From tblNtlog
Where tblNtlog.Eventcode = '10016'
Group By tblNtlog.AssetID) q1 On q1.assetid = tblAssets.AssetID
Where tblNtlog.Eventcode = '10016'

To use the report above, do the following:
• Open the report builder under Reports/Create New Report.
• Paste the SQL code we provided at the bottom of the page.
• Left-click somewhere in the upper section of the page so the code applies.
• Give the report a Title and hit the Save & Run button to save it. Export options are listed on the left.