Failed login attempts grouped by computer

[philb]

Hey there,

I have found this report for failed login attempts and have it working. But what I would like to do is group it by computer and have a count on how many attempts per machine.
Ultimately what I am trying to do is create an alert when we have so many invalid logon attempts to any particular machine is a certain time frame. That’s why I need the report. I would like it to be Lansweeper based. Thanks for any help you can give. Please ask me questions if this doesn’t make sense. Here is the current report


Select Top 1000000 tblAssetCustom.State As [Active PC's],
tblAssets.AssetName,
tblNtlog.Eventcode,
tblNtlog.TimeGenerated As [When]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Where tblAssetCustom.State = 1 And tblNtlog.Eventcode = 4625
Order By [When] Desc

[philb]

Awesome, this is great. Thanks!!

SQL always kicks my butt.

[Esben_D]

I would use something like the query below. It shows the amount of events scanned with the specified event ID in the last 7 days.

Select Top 1000000 tblAssets.AssetName,
  Count(tblNtlog.EventlogID) As NumberOfEvents,
  tblNtlog.Eventcode
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
  Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
  Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
    tblNtlog.SourcenameID
Where tblAssets.Lastseen > GetDate() - 7 And tblAssetCustom.State = 1
Group By tblAssets.AssetName,
  tblNtlog.Eventcode
Having tblNtlog.Eventcode = 4625