→ 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: 
Juggyluggs
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
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

View solution in original post

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