Windows Login Report for after hours

[kevinrpjones]

Hello!

Is it possible to have a report that list Windows user's logins between certain hours, like after business hours (18:00 - 07:00)?

Thanks!

[endyk]

This query should do it.

declare @beginofbusinesshour as time = '07:00:00'
declare @endofbusinesshour as time = '18:00:00'

select
*
,convert(time,logontime) as logon_timeonly
from tblCPlogoninfo
where convert(time,logontime) > @endofbusinesshour
or convert(time,logontime) < @beginofbusinesshour

Let me know if you need more help.

Endy


Please note:

Per the database documentation (pasted below), tblCPlogoninfo holds the logon events but only detects currently logged on users when lansweeper scans a windows computer.
To capture all logon events and to have the logontime value reflect when the user actually logged into the computer, deploy the LsPush scanning agent in a logon script/group policy.

tblCPlogoninfo
This table stores the user logon events that occurred on your Windows computers. Whenever Lansweeper scans a Windows computer, it detects the currently logged on user and creates a logon event for this user. The logontime value reflects when Lansweeper scanned the computer and detected the user; it does not necessarily reflect when the user actually logged into the computer. Users that do not log into a computer during a computer scan are not detected. To capture all logon events and to have the logontime value reflect when the user actually logged into the computer, deploy the LsPush scanning agent in a logon script/group policy.

[kevinrpjones]

Okay... this must be a stumper... how about this... this report (Windows Login Failure Report) list incorrect Window logins with time and date... Is there a way to modify this report to list incorrect logins from 5pm to 8am?


Select Top 1000000 tblNtlog.TimeGenerated,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblNtlog.Eventcode,
tblNtlogFile.Logfile,
SubString(tblNtlogMessage.Message, CharIndex('Account Name:',
tblNtlogMessage.Message, CharIndex('Account Name:', tblNtlogMessage.Message,
1) + 1) + 14, CharIndex('Account Domain:', tblNtlogMessage.Message,
CharIndex('Account Domain:', tblNtlogMessage.Message, 1) + 1) -
CharIndex('Account Name:', tblNtlogMessage.Message, CharIndex('Account Name:',
tblNtlogMessage.Message, 1) + 1) - 14) As [User Name],
SubString(tblNtlogMessage.Message, CharIndex('Failure Reason:',
tblNtlogMessage.Message, 1) + 16, CharIndex('Status:',
tblNtlogMessage.Message, 1) - CharIndex('Failure Reason:',
tblNtlogMessage.Message, 1) - 16) As Reason
From tblNtlog
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
Inner Join tblAssets On tblAssets.AssetID = tblNtlog.AssetID
Where (tblNtlog.Eventcode = 4723 Or tblNtlog.Eventcode = 4625) And
SubString(tblNtlogMessage.Message, CharIndex('Failure Reason:',
tblNtlogMessage.Message, 1) + 16, CharIndex('Status:',
tblNtlogMessage.Message, 1) - CharIndex('Failure Reason:',
tblNtlogMessage.Message, 1) - 16) Not Like '%ERROR%' And
CharIndex('Account Name:', tblNtlogMessage.Message, CharIndex('Account Name:',
tblNtlogMessage.Message, 1) + 1) > 1 And CharIndex('Account Domain:',
tblNtlogMessage.Message, CharIndex('Account Domain:', tblNtlogMessage.Message,
1) + 1) > 1 And CharIndex('Failure Reason:', tblNtlogMessage.Message, 1) > 1
And CharIndex('Status:', tblNtlogMessage.Message, 1) > 1
Order By tblNtlog.TimeGenerated Desc