cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
manderson
Engaged Sweeper II
I'm using Lansweeper to pull my windows event logs, and I'm getting some old data that I'd like to avoid. I'd also like to filter some events out of the finished report as well.

My report code is:
Select Top 1000000 tblNtlog.TimeGenerated,
tsysAssetTypes.AssetTypename,
tblAssets.IPAddress,
tblAssets.FQDN,
tblNtlog.EventlogID,
tblNtlog.Eventcode,
tblNtlog.Eventtype,
tblNtlog.LogfileID,
tblNtlogMessage.Message,
tblNtlog.SourcenameID
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Where tblNtlog.Eventtype = 1 And tblAssetGroups.AssetGroupID = 33

How would I filter events out older than 2 weeks? Also, how would I filter eventcode 1812 and other unwanted events?

Thanks!

Edit: looks like I can filter events older than two weeks by adding Where tblNtlog.TimeGenerated > GetDate() - 14 to Where tblNtlog.Eventtype = 1. Still looking to filter out unwanted events.
edit2: and this one for filtering out events, http://www.lansweeper.com/Forum/yaf_postst9495_Exclude-specific-event-from-event-log-error-report.aspx#post37300 maybe this will help others who forget to search 🙂

Last edit I hope. One of the eventcodes that I want to have in my report is listed on the same server thousands of times. Is there a way to limit that to a certain number?
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
You can use the Max function to only list the last instance of each event code on the computer. I've included a sample report below. Keep in mind that this is standard SQL syntax, not specific to Lansweeper. If you are interested in building or modifying reports, we recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  • Making use of our database dictionary, more information on which can be found here. The dictionary explains in great detail what each table and field stores.

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 Type,
tblNtlogFile.Logfile,
tblNtlogSource.Sourcename,
Max(tblNtlog.TimeGenerated) As Max
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
Group By tsysOS.Image,
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,
tblNtlogFile.Logfile,
tblNtlogSource.Sourcename
Order By tblAssets.Domain,
tblAssets.AssetName,
Max Desc

View solution in original post

1 REPLY 1
Susan_A
Lansweeper Alumni
You can use the Max function to only list the last instance of each event code on the computer. I've included a sample report below. Keep in mind that this is standard SQL syntax, not specific to Lansweeper. If you are interested in building or modifying reports, we recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  • Making use of our database dictionary, more information on which can be found here. The dictionary explains in great detail what each table and field stores.

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 Type,
tblNtlogFile.Logfile,
tblNtlogSource.Sourcename,
Max(tblNtlog.TimeGenerated) As Max
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
Group By tsysOS.Image,
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,
tblNtlogFile.Logfile,
tblNtlogSource.Sourcename
Order By tblAssets.Domain,
tblAssets.AssetName,
Max Desc

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now