→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
ponyisa
Engaged Sweeper II
Is there a way to run a report that will tell me who is locked out because of too many invalid logon attempts,or monitor failed logon events (Event ID 531) on a server?

Thanks
1 ACCEPTED SOLUTION
joyboy11111 wrote:
I have been trying to use the AD report lock out code from above and it is saying unkown object name: "tblntlogevent". I am using version 5.1.0.31. Would any of the tables tblntlog, tblntlogfile, tblntlogmessage, tblntlogsource, or tblntloguser be able to pull the needed information in place of "tblntlogevent'?

Use the report below for Lansweeper 5.X.
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblNtlog.Eventcode,
Case tblNtlog.Eventtype When 1 Then 'Error' When 2 Then 'Warning'
When 3 Then 'Information' When 4 Then 'Success Audit'
When 5 Then 'Failure Audit' End As Eventtype,
tblNtlogFile.Logfile,
tblNtlogMessage.Message,
tblNtlogSource.Sourcename,
tblNtlogUser.Loguser,
tblNtlog.TimeGenerated
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogUser On tblNtlogUser.LoguserID = tblNtlog.LoguserID
Where tblNtlog.Eventcode = 531 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblNtlog.TimeGenerated Desc

View solution in original post

9 REPLIES 9
ohadbadihi
Engaged Sweeper
I have the same problem i looking for this reports, we have windows2008r2 active directory , and he didnt fount any result .

do i need to put values ?

best regard ohad
Hemoco
Lansweeper Alumni
ohadbadihi wrote:
I have the same problem i looking for this reports, we have windows2008r2 active directory , and he didnt fount any result .

do i need to put values ?

best regard ohad

You need to enable logon event auditing on your machines so these events are actually generated, and rescan your machines afterwards.
joyboy11111
Engaged Sweeper
I have been trying to use the AD report lock out code from above and it is saying unkown object name: "tblntlogevent". I am using version 5.1.0.31. Would any of the tables tblntlog, tblntlogfile, tblntlogmessage, tblntlogsource, or tblntloguser be able to pull the needed information in place of "tblntlogevent'?
joyboy11111 wrote:
I have been trying to use the AD report lock out code from above and it is saying unkown object name: "tblntlogevent". I am using version 5.1.0.31. Would any of the tables tblntlog, tblntlogfile, tblntlogmessage, tblntlogsource, or tblntloguser be able to pull the needed information in place of "tblntlogevent'?

Use the report below for Lansweeper 5.X.
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblNtlog.Eventcode,
Case tblNtlog.Eventtype When 1 Then 'Error' When 2 Then 'Warning'
When 3 Then 'Information' When 4 Then 'Success Audit'
When 5 Then 'Failure Audit' End As Eventtype,
tblNtlogFile.Logfile,
tblNtlogMessage.Message,
tblNtlogSource.Sourcename,
tblNtlogUser.Loguser,
tblNtlog.TimeGenerated
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogUser On tblNtlogUser.LoguserID = tblNtlog.LoguserID
Where tblNtlog.Eventcode = 531 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblNtlog.TimeGenerated Desc
dr_sah
Engaged Sweeper II
i need Active directory report with all info about users, which i can get:

domain, username, first and second name, in which group they are, last log in, create date..... all info about users.
Hemoco
Lansweeper Alumni
dr.sah wrote:
in which group they are, create date

This information is not currently scanned. Available AD user fields can be found in tblADusers within the Lansweeper database. Logon information can be found in tblCPlogoninfo. A basic report would be:
Select Top 1000000 tblADusers.Username, tblADusers.Userdomain, tblADusers.Firstname, tblADusers.Lastname, tblADusers.Name, tblADusers.Displayname, tblADusers.Description, tblADusers.Office, tblADusers.Telephone, tblADusers.Fax, tblADusers.Mobile, tblADusers.Street, tblADusers.City, tblADusers.C, tblADusers.Zip, tblADusers.Country, tblADusers.Countrycode, tblADusers.UPN, tblADusers.Title, tblADusers.Department, tblADusers.Company, tblADusers.email, tblADusers.OU, tblADusers.Lastchanged, Max(tblCPlogoninfo.logontime) As lastlogon From tblADusers Left Join tblCPlogoninfo On tblCPlogoninfo.Username = tblADusers.Username And tblCPlogoninfo.Domain = tblADusers.Userdomain Group By tblADusers.Username, tblADusers.Userdomain, tblADusers.Firstname, tblADusers.Lastname, tblADusers.Name, tblADusers.Displayname, tblADusers.Description, tblADusers.Office, tblADusers.Telephone, tblADusers.Fax, tblADusers.Mobile, tblADusers.Street, tblADusers.City, tblADusers.C, tblADusers.Zip, tblADusers.Country, tblADusers.Countrycode, tblADusers.UPN, tblADusers.Title, tblADusers.Department, tblADusers.Company, tblADusers.email, tblADusers.OU, tblADusers.Lastchanged Order By tblADusers.Userdomain, tblADusers.Username
ponyisa
Engaged Sweeper II
Got it. I set up auditing on the DC Server, but not in Lansweeper Config. IT work great. Thanks
ponyisa
Engaged Sweeper II
Not sure why i don't have any any records when i run the report. Upon further investigation i found out i needed to monitor for Event ID 675, so I made the adjustment in the sql code. I then logged on to a server and verified that this Event ID does exist in the Security Logs, however when i run the report I get 0 items found. Any suggestions?


Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique, tblComputers.Domain, tblntlogevent.Eventcode, tblntlogevent.Eventtype, tblntlogevent.Logfile, tblntlogevent.Message, tblntlogevent.Sourcename, tblntlogevent.TimeGenerated, tblntlogevent.[User] From tblComputers Inner Join tblntlogevent On tblComputers.Computername = tblntlogevent.Computername Where tblntlogevent.Eventcode = 675 Order By tblComputers.ComputerUnique, tblntlogevent.TimeGenerated Desc

Hemoco
Lansweeper Alumni
We recommend enabling logon event auditing and using a report similar to the one below.

Select tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, tblntlogevent.Eventcode, tblntlogevent.Eventtype,
tblntlogevent.Logfile, tblntlogevent.Message, tblntlogevent.Sourcename,
tblntlogevent.TimeGenerated, tblntlogevent.[User]
From tblComputers Inner Join
tblntlogevent On tblComputers.Computername = tblntlogevent.Computername
Where tblntlogevent.Eventcode = 531
Order By tblComputers.ComputerUnique, tblntlogevent.TimeGenerated Desc

To use the specified report, do the following:
• Open the Lansweeper configuration console and select Reports & Alerts\Report Builder. Hit the “New” button.
• Copy the SQL code provided and paste it at the bottom of the newly created report, replacing the default SQL code.
• Click somewhere near tblComputers so the new code applies.
• Give the report a “View name” and a “Report name” and hit the “Save” button.
• Double-click on the report in the report list to see its results and export options.