Report to find all event logs

[Juggyluggs]

Hello,

I'm not the best with SQL or at all

But I'm looking to create a report that will pull all information from Event Log for all servers

I have this that looks for errors over 14 days


Select Top 1000000 Convert(nVARCHAR(10),tblNtlog.TimeGenerated,102) As Thedate,
Count(tblAssets.AssetID) As Total
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1 And
tblNtlog.Eventtype = 1 And tblNtlog.TimeGenerated > GetDate() - 14
Group By Convert(nVARCHAR(10),tblNtlog.TimeGenerated,102)
Order By Thedate Desc


But I want to expand on this and include all event logs

I've found the below which gives me the information i want but i need it to be in an ordered style so by PC

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetGroups.AssetGroup,
tblNtlog.Eventcode,
tblNtlog.Eventtype,
tblNtlogMessage.Message,
tblNtlogSource.Sourcename,
tblNtlogUser.Loguser
From tblAssets
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
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogUser On tblNtlogUser.LoguserID = tblNtlog.LoguserID
Where tblAssetGroups.AssetGroup = 'name group'
Order By tblAssets.AssetName


Thanks

[Susan_A]

To limit a date field to entries of the last 7 days, you'll need to add a filter like this to the field: > GetDate() - 7. To add this to the report I previously posted, use the SQL query below. Keep in mind that this is all standard SQL syntax, not specific to Lansweeper.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Count(tblNtlog.EventlogID) As ErrorCount
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1 And
  tblNtlog.Eventtype = 1 And tblNtlog.TimeGenerated > GetDate() - 7
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypeIcon10,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
Order By tblAssets.Domain,
  tblAssets.AssetName

[Juggyluggs]

Ooo that's looking like more what i'm after thank you for the help.

On another note how would I find out all events through lansweeper for eventviewer for like say the last seven days?

[Susan_A · Accepted solution]

It's not clear to me what you're trying to do to be honest. Your first report counts events, while your second one lists them. I'm not sure what you mean by "in an ordered style so by PC". I assume you are trying to count the number of errors that occurred on each server, in which case you can use the report below.

I do recommend reading up on SQL and making use of our database dictionary, as this will make building and modifying reports a lot easier. The Lansweeper report builder is a standard SQL editor.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Count(tblNtlog.EventlogID) As ErrorCount
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1 And
  tblNtlog.Eventtype = 1
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypeIcon10,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
Order By tblAssets.Domain,
  tblAssets.AssetName