Active Directory Report

[ponyisa]

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

[ohadbadihi]

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]

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]

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'?

[Hemoco · 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

[dr_sah]

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]

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]

Got it. I set up auditing on the DC Server, but not in Lansweeper Config. IT work great. Thanks

[ponyisa]

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]

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.