→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
nlee
Engaged Sweeper II
I'm looking to get a daily report of Event Log errors for one of my domains, for workstations only. I found an older SQL query for this but the tables have changed so much in version 5.0 it's not working. Any help on making one of these for 5.0?
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
Please try the report below for the information you are after. Replace YourDomain with the name of the domain you would like to report on.
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
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.Domain = 'YourDomain' And 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 = 'error' And tblNtlog.TimeGenerated > GetDate() - 1 And
tblComputersystem.Domainrole < 2
Order By tblNtlog.TimeGenerated Desc

View solution in original post

3 REPLIES 3
Hemoco
Lansweeper Alumni
The report in this thread is already limited to errors that occurred in the last day. Note the following criterion:
tblNtlog.TimeGenerated > GetDate() - 1
dbucktrsd
Engaged Sweeper
I was able to use this report to view all event log error entries on workstations but unfortunately, that meant thousands and thousands of multiple entries.
Is there a way to add a date criterion so that I can search based on a certain period?
For instance, show error entries after n date?

Thanks a bunch.
Hemoco
Lansweeper Alumni
Please try the report below for the information you are after. Replace YourDomain with the name of the domain you would like to report on.
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
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.Domain = 'YourDomain' And 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 = 'error' And tblNtlog.TimeGenerated > GetDate() - 1 And
tblComputersystem.Domainrole < 2
Order By tblNtlog.TimeGenerated Desc