Failed Login Report - with source machine IP address

[simon_wilks]

Morning,

there's a few Failed Login Reports templates on here. Ive played with all and "Failed Login Report - 3 day" has proved very valuable to ue.

Im looking to get a bit more info from event viewer.
In event viewer it sometimes shows

Network Information:
Workstation Name: -
Source Network Address: -
Source Port: -

So far im stuck to work out myself. Any pointers please how i can get some of this text into a column in hte report.

End goal this may be used to block IP addresses on firewalls

Thanks in advance

[Hendrik_VE]

This is the report I use (I added the source IP):

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,
  SubString(tblNtlogMessage.Message, CharIndex('Source Network Address:',
  tblNtlogMessage.Message) + 24, CharIndex('Source Port:',
  tblNtlogMessage.Message) - CharIndex('Source Network Address:',
  tblNtlogMessage.Message) - 24) As 'Source IP',
  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

  And this is my 'failed logins chart':

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

If there are any anomalies, I know I have to dig into the report.

[Hendrik_VE]

If you're referring to this report I once created, I think I can help you:

You need to add a couple of Substring/Charindex combo's to add the Source IP:

SubString(tblNtlogMessage.Message, 
CharIndex('Source Network Address:',tblNtlogMessage.Message) + 24, 
CharIndex('Source Port:', tblNtlogMessage.Message) - CharIndex('Source Network Address:', tblNtlogMessage.Message) - 24) As 'Source IP'

Kudo's if you like it 🙂

[pryan67]

Actually I found it 🙂  Thank you VERY much.  Have a glorious day

[pryan67]

Thank you very much Hendrik.   Do you have the original report?   I can't seem to find it.  That would be perfect for my needs

[pryan67]

This would be an amazingly useful report for me as well.