Limit eventcode by date, additional filtering

[manderson]

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?

[Susan_A · Accepted solution]

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