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

View solution in original post

3 REPLIES 3
Daniel_B
Lansweeper Alumni
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
Engaged Sweeper III
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
Lansweeper Alumni
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