Event ID Report

[WhatTheFrenchTo]

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 = '64' And tblNtlogSource.Sourcename Like
'%Microsoft-Windows-CertificateServicesClient-AutoEnrollment%' And
tblNtlog.TimeGenerated > GetDate() - 7
Order By tblNtlog.TimeGenerated Desc

This is what I have from a previous post here on the forums. I changed the ID and source name but nothing is pulling. Am I running this report properly? Was hoping to pull event id 64 if found in the last 7 days on a server. Thanks in advance for the help.

[Daniel_B · Accepted solution]

If you would like to list all event log entries, just remove the filters which you find in the SQL code under the Where clause (or in the report editor in the Criteria column). The following report lists all event log entries from Windows server machines (Domainrole > 1). Please note that you might need to enable Warning and Information log scanning under Configuration\Server options, section Eventlog Scanning, and that this will cause a larger load on your database.

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
  Left Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
  Left Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
  Left Join tblNtlogSource On tblNtlogSource.SourcenameID =
    tblNtlog.SourcenameID
  Left Join tblNtlogUser On tblNtlogUser.LoguserID = tblNtlog.LoguserID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblNtlog.TimeGenerated > GetDate() - 7 And tblComputersystem.Domainrole >
  1
Order By tblNtlog.TimeGenerated Desc

[WhatTheFrenchTo]

Hey Dan,

I pulled out the tblNtlogSource.Sourcename and stuff is now pulling. My question is, is there a template that you have that pulls all Event Logs? Should this one be pulling all the server logs? ie. System, Application, etc.

Thanks for your help thus far!

[Daniel_B]

tblNTlog.Eventcode is a number. If you remove the quotes and just search for 64, you might receive more results. In the Database Dictionary you can see the data type of each column. This is relevant if you are using filters on your reports.

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
  Left Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
  Left Join tblNtlogSource On tblNtlogSource.SourcenameID =
    tblNtlog.SourcenameID
  Left Join tblNtlogUser On tblNtlogUser.LoguserID = tblNtlog.LoguserID
Where tblNtlog.Eventcode = 64 And
  tblNtlogSource.Sourcename Like
  '%Microsoft-Windows-CertificateServicesClient-AutoEnrollment%' And
  tblNtlog.TimeGenerated > GetDate() - 7
Order By tblNtlog.TimeGenerated Desc