→ 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: 
yashno
Engaged Sweeper
How to create report to show windows event ID 4740 ,include below information?

Thank you

=====================================================================================

Subject:

Security ID: SYSTEM
Account Name: WIN-R9H529RIO4Y$
Account Domain: ABC
Logon ID: 0x3e7

Account That Was Locked Out:

Security ID: ABC\John
Account Name: John

Additional Information:

Caller Computer Name: PC01
5 REPLIES 5
Hendrik_VE
Champion Sweeper III
I have created below report, which shows failed logins (event 4625). Maybe you can try to tweak this report?
Note that this probably only works under SQL Server.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
SubString(tblNtlogMessage.Message, CharIndex('Account Name:',
tblNtlogMessage.Message, CharIndex('Account Domain', tblNtlogMessage.Message))
+ 14, CharIndex('Account Domain:', tblNtlogMessage.Message,
CharIndex('Logon Type:', tblNtlogMessage.Message)) -
CharIndex('Account Name:', tblNtlogMessage.Message,
CharIndex('Account Domain', tblNtlogMessage.Message)) - 14) As Account,
SubString(tblNtlogMessage.Message, CharIndex('Account Domain:',
tblNtlogMessage.Message, CharIndex('Logon Type:', tblNtlogMessage.Message)) +
16, CharIndex('Failure Information:', tblNtlogMessage.Message) -
CharIndex('Account Domain:', tblNtlogMessage.Message, CharIndex('Logon Type:',
tblNtlogMessage.Message)) - 16) As 'Account Domain',
SubString(tblNtlogMessage.Message, CharIndex('Failure Reason:',
tblNtlogMessage.Message) + 16, CharIndex('Status', tblNtlogMessage.Message) -
CharIndex('Failure Reason:', tblNtlogMessage.Message) - 16) As Reason,
tblNtlog.TimeGenerated
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 tblNtlog.TimeGenerated > GetDate() - 14 And tblNtlog.Eventcode = 4625 And
tblAssetCustom.State = 1
Order By tblNtlog.TimeGenerated Desc
AZHockeyNut
Champion Sweeper III
Hendrik.VE wrote:
I have created below report, which shows failed logins (event 4625). Maybe you can try to tweak this report?
Note that this probably only works under SQL Server.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
SubString(tblNtlogMessage.Message, CharIndex('Account Name:',
tblNtlogMessage.Message, CharIndex('Account Domain', tblNtlogMessage.Message))
+ 14, CharIndex('Account Domain:', tblNtlogMessage.Message,
CharIndex('Logon Type:', tblNtlogMessage.Message)) -
CharIndex('Account Name:', tblNtlogMessage.Message,
CharIndex('Account Domain', tblNtlogMessage.Message)) - 14) As Account,
SubString(tblNtlogMessage.Message, CharIndex('Account Domain:',
tblNtlogMessage.Message, CharIndex('Logon Type:', tblNtlogMessage.Message)) +
16, CharIndex('Failure Information:', tblNtlogMessage.Message) -
CharIndex('Account Domain:', tblNtlogMessage.Message, CharIndex('Logon Type:',
tblNtlogMessage.Message)) - 16) As 'Account Domain',
SubString(tblNtlogMessage.Message, CharIndex('Failure Reason:',
tblNtlogMessage.Message) + 16, CharIndex('Status', tblNtlogMessage.Message) -
CharIndex('Failure Reason:', tblNtlogMessage.Message) - 16) As Reason,
tblNtlog.TimeGenerated
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 tblNtlog.TimeGenerated > GetDate() - 14 And tblNtlog.Eventcode = 4625 And
tblAssetCustom.State = 1
Order By tblNtlog.TimeGenerated Desc


AWESOME report. Takes quite a while to run for me but helped a ton! thanks
Hendrik_VE
Champion Sweeper III
AZHockeyNut wrote:


AWESOME report. Takes quite a while to run for me but helped a ton! thanks


Thanks 🙂
Took me a while to get it right, but might be useful to create similar eventlog reports.
I combine it on my dashboard with the following chart report, so I get notified when there are a lot of failed logins:

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.Eventcode = 4625 And tblNtlog.TimeGenerated > GetDate() - 14
Group By Convert(nVARCHAR(10),tblNtlog.TimeGenerated,102)
Order By Thedate
CyberCitizen
Honored Sweeper
Have you checked the report Windows: Error events generated in last 7 days
CyberCitizen
Honored Sweeper
Not sure what you are looking to gain from said report etc, however I have used this before.

https://www.netwrix.com/account_lockout_examiner.html?itm_source=blog&itm_medium=context&itm_campaign=free-tools&itm_content=none

Which has assisted with account lock outs etc.