Notification

Icon
Error

Failed Logins Report/Chart

Posted: Friday, May 17, 2019 11:03:40 AM(UTC)
Esben.D

Esben.D

Member Administration Original PosterPosts: 1,740
2
Like
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:
Code:
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:
Code:
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
Cindy.C
#1Cindy.C Member Administration Posts: 17  
posted: 5/24/2019 11:05:12 AM(UTC)
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.c...l-compact-to-sql-server/
JOP4089
#2JOP4089 Member Posts: 1  
posted: 5/24/2019 4:16:02 PM(UTC)
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
#3KDavis Member Posts: 6  
posted: 6/20/2019 1:17:05 AM(UTC)
I am also getting the same error. The Chart works great, but not the actual report itself.


Originally Posted by: JOP4089 Go to Quoted Post
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.


Active Discussions

Lansweeper Getting only the first Owner of an Asset
by  ToMonkey   Go to last post Go to first unread
Last post: Today at 3:45:30 PM(UTC)
Lansweeper Report of assets names behind switches/routers
by  dsimonspan  
Go to last post Go to first unread
Last post: Today at 3:05:09 PM(UTC)
Lansweeper Help Desk: Top 10 users submitting tickets
by  LGuth   Go to last post Go to first unread
Last post: Today at 1:38:08 PM(UTC)
Lansweeper non-active computer report
by  Apaulcolypse  
Go to last post Go to first unread
Last post: 7/19/2019 5:16:21 PM(UTC)
Lansweeper Report for showing CPUs below or above
by  GBA Craig   Go to last post Go to first unread
Last post: 7/18/2019 3:38:41 PM(UTC)
Lansweeper Hardware list
by  GBA Craig  
Go to last post Go to first unread
Last post: 7/18/2019 3:34:43 PM(UTC)
Lansweeper Top 10 users submitting tickets
by  LGuth   Go to last post Go to first unread
Last post: 7/17/2019 9:29:44 PM(UTC)
Lansweeper Microsoft Patch Tuesday Report - July 2019
by  Noobmode  
Go to last post Go to first unread
Last post: 7/17/2019 8:48:08 PM(UTC)