cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Failed Logins Report/Chart

Esben_D
Lansweeper Employee
Lansweeper Employee
Thanks to Hendrik.VE, here are two reports about failed logon events. One chart report and one normal report.

Do note that in order to use these reports you will need to enable the scanning of failure events.

To use the Chart report in a widget, make sure you save the report with the "Chart:" prefix (without quotes).

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


Report:
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
5 REPLIES 5

Hendrik_VE
Champion Sweeper II
The error message is quite obvious... the 'length' parameter in one of the substring functions is apparently negative (probably due to some missing fields in the eventlog message).

I made a small adaptation to the original report query, in such a way that when the 'length' parameter is negative, it will be replaced (using a case function) with a fixed length of 30. It's not the prettiest solution as it will probably show a couple of unreadable/incomplete lines, but that way you'll also discover which event message was causing issues.

Please let me know if it works 🙂

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
SubString(tblNtlogMessage.Message, CharIndex('Account Name:',
tblNtlogMessage.Message, CharIndex('Account Domain', tblNtlogMessage.Message))
+ 14, Case
When (CharIndex('Account Domain:', tblNtlogMessage.Message,
CharIndex('Logon Type:', tblNtlogMessage.Message)) -
CharIndex('Account Name:', tblNtlogMessage.Message,
CharIndex('Account Domain', tblNtlogMessage.Message)) - 14) < 0 Then 30
Else CharIndex('Account Domain:', tblNtlogMessage.Message,
CharIndex('Logon Type:', tblNtlogMessage.Message)) -
CharIndex('Account Name:', tblNtlogMessage.Message,
CharIndex('Account Domain', tblNtlogMessage.Message)) - 14
End) As Account,
SubString(tblNtlogMessage.Message, CharIndex('Account Domain:',
tblNtlogMessage.Message, CharIndex('Logon Type:', tblNtlogMessage.Message)) +
16, Case
When (CharIndex('Failure Information:', tblNtlogMessage.Message) -
CharIndex('Account Domain:', tblNtlogMessage.Message,
CharIndex('Logon Type:', tblNtlogMessage.Message)) - 16) < 0 Then 30
Else CharIndex('Failure Information:', tblNtlogMessage.Message) -
CharIndex('Account Domain:', tblNtlogMessage.Message,
CharIndex('Logon Type:', tblNtlogMessage.Message)) - 16
End) As 'Account Domain',
SubString(tblNtlogMessage.Message, CharIndex('Failure Reason:',
tblNtlogMessage.Message) + 16, Case
When (CharIndex('Status', tblNtlogMessage.Message) -
CharIndex('Failure Reason:', tblNtlogMessage.Message) - 16) < 0 Then 30
Else CharIndex('Status', tblNtlogMessage.Message) -
CharIndex('Failure Reason:', tblNtlogMessage.Message) - 16
End) 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

taext
Engaged Sweeper
Any updates on this? We'd really like to use the report of this one. The chart is working.

JOP4089
Engaged Sweeper
The report worked great...at first..but then started getting this error:

Error: Invalid length parameter passed to the LEFT or SUBSTRING function.

I am using full SQL version and it was working with initial data and then the error started the next day. I am looking at the query myself but any direction is appreciated.

KDavis
Engaged Sweeper II
I am also getting the same error. The Chart works great, but not the actual report itself.


JOP4089 wrote:
The report worked great...at first..but then started getting this error:

Error: Invalid length parameter passed to the LEFT or SUBSTRING function.

I am using full SQL version and it was working with initial data and then the error started the next day. I am looking at the query myself but any direction is appreciated.


Obi_1_Cinobi
Lansweeper Tech Support
Lansweeper Tech Support
Please note that the "normal" report uses advanced SQL methods which are not supported in SQL Compact. If you try to run the report on SQL Compact, you will get the following error message in your report builder:

There was an error parsing the query. [ Token line number = 1,Token line offset = 785,Token in error = Account Domain ]

So do make sure that your Lansweeper database is hosted on SQL Server when using this report.

Of course, you can always migrate your SQL Compact database to SQL Server if required: https://www.lansweeper.com/knowledgebase/moving-your-database-from-sql-compact-to-sql-server/