.: Şaban Karaaslan :.: Şubat 2011

A customer was asking for some information about the time it took for users to logon on their Terminal Server hosted Windows Desktops. Luckily there were two eventID’s created during the logon process. And if you calculate the time between those eventID’s then you have some insight in the Logon time for a user.

When a user logs on, Security Event 528 is created. Event 528 is logged whenever an account logs on to the local computer, except for in the event of network logons (see event 540). Event 528 is logged whether the account used for logon is a local SAM account or a domain account. On a Windows 7 client Security event 4624 is created.

Example Screenshot:

And then they used Event ID 865 — Software Restriction Policy Notification for the last Log on event. So the Log on Time could be calculated the time difference between those eventID’s. You should look for your own eventID if you want to create a simular report.

So how can these Events be used to create a Logon Time Report?

High Level steps:

Create two NT Event Collection Rules for each EventID (528 and 865) in OpsMgr.
Create an SQL Query which calculates the Time difference between the two EventID’s collected by the Event Collection Rules for each user.
Create a Custom Logon Time OpsMgr Report with Visual Studio.

Let’s start with a more detailed overview of the steps.

For testing purposes I created a batch script, which you can use to create two dummy eventID’s in the Application Event Log. You can run this script using different credentials (users) with the SysInternals tool ShellRunAs.

@echo off
REM First EventID 528 LastEventID 865
Eventcreate /L Application /D "First EventID created" /T INFORMATION /ID 528
REM Create Random number between 1 -30 secs.
Set max=30
Set min=1
:: Generate number between Min and Max
Set /A rand=%random% %% (max - min + 1)+ min
REM Wait for 1-30 secs to generate second EventID
sleep %rand%
Eventcreate /L Application /D "Last EventID created" /T INFORMATION /ID 865

The above script uses the sleep.exe command so you need to install it from the Reskit if you haven’t installed that yet. As you can see the time between the two events is a random number between 1 – 30 secs.

Save above code to LogOndemo.cmd and run from command prompt.

Create two NT Event Collection Rules for each EventID (528 and 865) in OpsMgr

Ok now we have the two dummy events are created we can create the Event Collection Rules. Make sure you created a new MP to store your Event Collection Rules.

Disable Rule we enable the rules later for the servers we want to Collection Rules to be running on.

Keep in mind these example Collection Rules are created for the dummy eventID’s. You should select your own EventID’s.

Enable Rules for server(s) you need to have to Collection Rule running on.

For EventID 865 follow the same steps as shown above. Don’t forget to enable the Rule with an Override!

Create an SQL Query which calculates the Time difference between the two EventID’s collected by the Event Collection Rules for each user.

And now the most difficult part of creating a Custom Report, creating the right Database SQL queries. I got some help from some SQL guru’s at the office, so you may want to talk to your local dba for some pointers about creating the right SQL queries.

First we need to determine what we want to show in our Reports. Right? The customer wanted to see the Minimum, Maximum and Average LogOn time per user on a server. Something like this:

But first some steps I took to create the SQL queries. First I wanted to collect all the 528 and 865 events collected by the two Collection Rules (FirstLogOn Event 528 and LastLogOn Event 865). And I used the next SQL query from Anders Bengtsson for that and changed that a little. It collects all the Collected Events 528 and 865 within the selected period ('2009-12-01 00:00:00.000' and '2009-12-12 00:00:00.000')

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '2009-12-01 00:00:00.000'
SET @EndDate = '2009-12-12 00:00:00.000';

USE OPERATIONSMANAGERDW

SELECT
    vEvent.DateTime,
    vEventPublisher.EventPublisherName as 'EventSource',
    vEventLoggingComputer.ComputerName as 'Computer',
    vEventLevel.EventLevelTitle as 'Type',
    vEvent.EventDisplayNumber as 'EventID',
    vEventChannel.EventChannelTitle,
    vEventUserName.UserName,
    vEventDetail.RenderedDescription as 'EventDescription'
FROM Event.vEvent LEFT OUTER JOIN
    vEventUserName ON vEvent.UserNameRowId =
    vEventUserName.EventUserNameRowId LEFT OUTER JOIN
    vEventCategory ON vEvent.EventCategoryRowId =
    vEventCategory.EventCategoryRowId LEFT OUTER JOIN
    vEventPublisher ON vEvent.EventPublisherRowId =
    vEventPublisher.EventPublisherRowId LEFT OUTER JOIN
    vEventLoggingComputer ON vEvent.LoggingComputerRowId =
    vEventLoggingComputer.EventLoggingComputerRowId LEFT OUTER JOIN
    vEventLevel ON vEvent.EventLevelId = vEventLevel.EventLevelId LEFT OUTER JOIN
    vEventChannel ON vEvent.EventChannelRowId =
    vEventChannel.EventChannelRowId LEFT OUTER JOIN
    Event.vEventDetail ON vEvent.EventOriginId = vEventDetail.EventOriginId
WHERE vEventLevel.EventLevelTitle = 'Information'
AND vEvent.Datetime between @StartDate and @EndDate
AND (vEvent.EventDisplayNumber = 528
OR vEvent.EventDisplayNumber = 865)

Result Screenshot:

It’s a start but not completely what we want ;-)

As you can see to LogOn time for user OpsMgrDemo\OM_Admin is 22 seconds.

So which query do we need to create to calculate the time difference between those two events per UserName?

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '2009-12-01 00:00:00.000'
SET @EndDate = '2009-12-12 00:00:00.000';

WITH CTE AS
    (SELECT
    ROW_NUMBER() OVER(ORDER BY vEvent.DateTime) AS RowNum,
    vEvent.DateTime,
    vEventPublisher.EventPublisherName as 'EventSource',
    vEventLoggingComputer.ComputerName as 'Computer',
    vEventLevel.EventLevelTitle as 'Type',
    vEvent.EventDisplayNumber as 'EventID',
    vEventChannel.EventChannelTitle,
    vEventUserName.UserName,
    vEventDetail.RenderedDescription as 'EventDescription'
    FROM
    Event.vEvent LEFT OUTER JOIN
    vEventUserName ON vEvent.UserNameRowId =
    vEventUserName.EventUserNameRowId LEFT OUTER JOIN
    vEventCategory ON vEvent.EventCategoryRowId =
    vEventCategory.EventCategoryRowId LEFT OUTER JOIN
    vEventPublisher ON vEvent.EventPublisherRowId =
    vEventPublisher.EventPublisherRowId LEFT OUTER JOIN
    vEventLoggingComputer ON vEvent.LoggingComputerRowId =
    vEventLoggingComputer.EventLoggingComputerRowId LEFT OUTER JOIN
    vEventLevel ON vEvent.EventLevelId = vEventLevel.EventLevelId LEFT OUTER JOIN
    vEventChannel ON vEvent.EventChannelRowId =
    vEventChannel.EventChannelRowId LEFT OUTER JOIN
    Event.vEventDetail ON vEvent.EventOriginId = vEventDetail.EventOriginId
    WHERE vEventLevel.EventLevelTitle = 'Information'
    AND vEvent.Datetime between @StartDate and @EndDate
    AND (vEvent.EventDisplayNumber = 528
    OR vEvent.EventDisplayNumber = 865)
)

SELECT *
, (SELECT T2.DateTime
    FROM CTE AS T2
    WHERE (CTE.RowNum + 1)= T2.RowNum) AS LogOffTime
, DATEDIFF(s, CTE.DateTime, (SELECT T2.DateTime
    FROM CTE AS T2
    WHERE (CTE.RowNum + 1)= T2.RowNum) ) AS LogTime
FROM CTE

WHERE RowNum%2 = 1

Result Screenshot:

As you can see, per UserName is calculated what the LogOn Time is. Again you see that it took 22 seconds for the LogOn for User OM_Admin.

You could already start using this SQL query as Database SQL Query in your Custom OpsMgr Report in Visual Studio. You could for instance, Group the data on Computer or User to generate a Report on LogOn time for users on a specific Computer or User.

Example Screenshot:

But we wanted to create a Report with MIN, MAX and AVG LogOn time per user, right?

For that we need the next SQL query.

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '2009-12-01 00:00:00.000'
SET @EndDate = '2009-12-12 00:00:00.000';

-- Create TEMP table
--(RowNum, DateTime, EventSource, Computer,Type,EventID, EventChannelTitle, UserName, EventDescription, LoggOffTime, LogTime)
DECLARE @temp TABLE(
    RowNum INTEGER,
    DateTime DATETIME,
    EventSource NVARCHAR(250),
    Computer NVARCHAR(250),
    Type NVARCHAR(250),
    EventID INTEGER,
    EventChannelTitle NVARCHAR(250),
    UserName NVARCHAR(250),
    EventDescription NVARCHAR(250),
    LogOffTime DATETIME,
    LogTime INTEGER );

INSERT INTO @temp(RowNum, DateTime, EventSource, Computer,Type,EventID, EventChannelTitle, UserName, EventDescription, LogOffTime, LogTime)

SELECT
    Computer,
    UserName,
    COUNT(t1.LogTime) AS [NUMBEROFLOGINS],
    MAX(t1.LogTime) AS [MAXLOGTIME],
    MIN(t1.LogTime) AS [MINLOGTIME],
    AVG(t1.LogTime) AS [AVGLOGTIME]
    FROM @temp t1
    GROUP BY Computer,UserName

Screenshot Result:

Yes! This is what we were looking for. Now we have the right Data SQL query, we can Open Visual Studio to create our Custom User LogOn Time Report.

Create a Custom Logon Time OpsMgr Report with Visual Studio

Let’s open SQL Server Business Intelligence Development Studio.

Create a New Project and select Report Server Project Wizard and give your project a name.

Select Next in the Welcome to the Report Wizard Screen

Create a Data Source

Click Edit button to enter the SQL Server information.

And Test Connection if you want.

Click Next in the Report Wizard Screen.

Enter the previously created SQL query with some changes (remove the Declare statements).

DECLARE @temp TABLE(
    RowNum INTEGER,
    DateTime DATETIME,
    EventSource NVARCHAR(250),
    Computer NVARCHAR(250),
    Type NVARCHAR(250),
    EventID INTEGER,
    EventChannelTitle NVARCHAR(250),
    UserName NVARCHAR(250),
    EventDescription NVARCHAR(250),
    LogOffTime DATETIME,
    LogTime INTEGER );

INSERT INTO @temp(RowNum, DateTime, EventSource, Computer,Type,EventID, EventChannelTitle, UserName, EventDescription, LogOffTime, LogTime)

Copy and paste above (or your own query) to the Query Builder window and click on Next.

Select your Report Type and click on Next

Select how you want to group your report. (I just kept the default settings)

Select the Table Style of the Report and click on Next

Enter Report server and Deployment folder info and click on Next.

Give your Report a name and select Preview Report and click on Finish

Change Report Parameters from Text to Date/Time Date Type.

You need to change this for both parameters!

Let’s check the Report. Go to Preview, select the Start Date and End Date and click on View Report.

Ok it’s not exactly the way we would like it to be, but the results are there!

Let’s Pimp this Report a little.

So this could be the end result. And you can pimp it even more if you want…
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------

528: Successful Logon

On this page

Description of this event
Field level details
Examples
Discuss this event

Event 528 is logged whenever an account logs on to the local computer, except for in the event of network logons (see event 540). Event 528 is logged whether the account used for logon is a local SAM account or a domain account.

Logon types possible:

Logon Type	Description
2	Interactive (logon at keyboard and screen of system) Windows 2000 records Terminal Services logon as this type rather than Type 10.
3	Network (i.e. connection to shared folder on this computer from elsewhere on network or IIS logon - Never logged by 528 on W2k and forward. See event540)
4	Batch (i.e. scheduled task)
5	Service (Service startup)
7	Unlock (i.e. unnattended workstation with password protected screen saver)
8	NetworkCleartext (Logon with credentials sent in the clear text. Most often indicates a logon to IIS with "basic authentication") See this article for more information.
9	NewCredentials
10	RemoteInteractive (Terminal Services, Remote Desktop or Remote Assistance)
11	CachedInteractive (logon with cached domain credentials such as when logging on to a laptop when away from the network)

For an explanation of the Logon Process field, see event 515. For an explanation of the Authentication Package field, see event 514.
Logon GUID is not documented. It is unclear what purpose the Caller User Name, Caller Process ID, and Transited Services fields serve.

Source Network Address corresponds to the IP address of the Workstation Name. Source Port is the TCP port of the workstation and has dubious value.

Logon ID is useful for correlating to many other events that occurr during this logon session.

Free Security Log Quick Reference Chart

Description Fields in 528

User Name:
Domain:
Logon ID: useful for correlating to many other events that occurr during this logon session
Logon Type: %4
Logon Process: %5
Authentication Package: %6
Workstation Name: %7

The following field is not logged in Window 2000:

Logon GUID

The following fields are not logged in Windows 2000 or XP:

Caller User Name:
Caller Domain:
Caller Logon ID:
Caller Process ID:
Transited Services:
Source Network Address:
Source Port:

Top 10 Events to Monitor

Examples of 528

Successful Logon:

User Name:administrator

Domain:ELM
Logon ID:(0x0,0x558DD)
Logon Type:2
Logon Process:User32
Authentication Package:Negotiate
Workstation Name:W2MS
Windows XP and Windows Server 2003 add:
Logon GUID:{d39697e4-34a9-b3e0-f30a-d2ba517eb4a2}
Windows Server 2003 adds these fields:
Caller User Name:-
Caller Domain:-
Caller Logon ID:-
Caller Process ID: -
Transited Services: -
Source Network Address:10.42.42.170
Source Port:3165

----------------------------------------------------------------

540: Successful Network Logon

On this page

Description of this event
Field level details
Examples
Discuss this event

Event 540 gets logged when a user elsewhere on the network connects to a resource (e.g. shared folder) provided by the Server service on this computer. The Logon Type will always be 3 or 8, both of which indicate a network logon.

Logon type 3 is what you normally see. Logon Type 8 means network logon with clear text authentication. The only scenario where we've observed logon type 8 is with logons to IIS web-sites via Basic Authentication. Don't immediately sound the alarms if you see logon type 8 since most Basic Authentication is wrapped up inside an SSL session via https.

For all other logon types see event 528.

Event 540 gets logged whether the account used for logon is a local SAM account or a domain account. For all other types of logons this event is logged including

For an explanation of logon processes see event 515. For an explanation of authentication package see event 514.

Logon GUID is not documented. It is not clear what the caller user, caller process ID, transited services are about.

Source Network Address corresponds to the IP address of the Workstation Name. Source Port is the TCP port of the workstation and has dubious value.

Free Security Log Quick Reference Chart

Description Fields in 540

User Name: %1
Domain: %2
Logon ID: %3
Logon Type: %4
Logon Process: %5
Authentication Package: %6
Workstation Name: %7

The following field is not logged in Window 2000:

Logon GUID

The following fields are not logged in Windows 2000 or XP:

Caller User Name:
Caller Domain:
Caller Logon ID:
Caller Process ID:
Transited Services:
Source Network Address:
Source Port:

Top 10 Events to Monitor

Examples of 540

Successful Network Logon

User Name: %1
Domain: %2
Logon ID: %3
Logon Type: %4
Logon Process: %5
Authentication Package: %6
Workstation Name: %7

Windows XP and Windows Server 2003 add:

Logon GUID:{d39697e4-34a9-b3e0-f30a-d2ba517eb4a2}
Windows Server 2003 adds these fields:
Caller User Name:-
Caller Domain:-
Caller Logon ID:-
Caller Process ID: -
Transited Services: -
Source Network Address:10.42.42.170
Source Port:3165

----------------------------------------------------------------

4624: An account was successfully logged on

On this page

Description of this event
Field level details
Examples
Discuss this event

This is a highly valuable event since it documents each and every successful attempt to logon to the local computer regardless of logon type, location of the user or type of account.

Free Security Log Quick Reference Chart

Description Fields in 4624

Subject:

Identifies the account that requested the logon - NOT the user who just logged on. Subject is usually Null or one of the Service principals and not usually useful information. See New Logon for who just logged on to the sytem.

Logon Type:

This is a valuable piece of information as it tells you HOW the user just logged on:

Logon Type	Description
2	Interactive (logon at keyboard and screen of system)
3	Network (i.e. connection to shared folder on this computer from elsewhere on network)
4	Batch (i.e. scheduled task)
5	Service (Service startup)
7	Unlock (i.e. unnattended workstation with password protected screen saver)
8	NetworkCleartext (Logon with credentials sent in the clear text. Most often indicates a logon to IIS with "basic authentication") See this article for more information.
9	NewCredentials such as with RunAs or mapping a network drive with alternate credentials. This logon type does not seem to show up in any events. If you want to track users attempting to logon with alternate credentials see 4648.
10	RemoteInteractive (Terminal Services, Remote Desktop or Remote Assistance)
11	CachedInteractive (logon with cached domain credentials such as when logging on to a laptop when away from the network)

New Logon:

The user who just logged on is identified by the Account Name and Account Domain. You can determine whether the account is local or domain by comparing the Account Domain to the computer name. If they match, the account is a local account on that system, otherwise a domain account. Security ID: the SID of the accountAccount Name: Logon name of the accountAccoutn Domain: Domain name of the account (pre-Win2k domain name)Logon ID is a semi-unique (unique between reboots) number that identifies the logon session just initiated. Any events logged subsequently during this logon session will report the same Logon ID through to the logoff event 4647 or 4634.Logon GUID: Supposedly you should be able to correlate logon events on this computer with corresonding authentication events on the domain controller using this GUID. Such as linking 4624 on the member computer to 4769 on the DC. But the GUIDs do not match between logon events on member computers and the authentication events on the domain controller.

Process Information:

The Process Name identifies the program executable that processed the logon. This is one of the trusted logon processes identified by 4611. Process ID is the process ID specified when the executable started as logged in 4688.

Network Information:

This section identifies WHERE the user was when he logged on. Of course if logon is initiated from the same computer this information will either be blank or reflect the same local computers. Workstation Name: is the computer name of the computer where the user is physically present in most cases unless this logon was intitiated by a server application acting on behalf of the user. Workstation may also not be filled in for some Kerberos logons since the Kerberos protocol doesn't really care about the computer account in the case of user logons and therefore lacks any field for carrying workstation name in the ticket request message.Source Network Address is the IP address of the computer where the user is physically present in most cases unless this logon was intitiated by a server application acting on behalf of the user. If this logon is initiated locally the IP address will sometimes be 127.0.0.1 instead of the local computer's actual IP address. This field is also blank sometimes because Microsoft says "Not every code path in Windows Server 2003 is instrumented for IP address, so it's not always filled out."Source Port identifies the source TCP port of the logon request which seems useless since with most protocols source ports are random.

Detailed Authentication Information:

Logon Process (see 4611)
CredPro indicates a logon initiated by User Account Control
Authentication Package (see 4610 or 4622)
Transited Services - This has to do with server applications that need to accept some other type of authentication from the client and then transition to Kerberos for accessing other resources on behalf of the client. Seehttp://msdn.microsoft.com/msdnmag/issues/03/04/SecurityBriefs/
Package name - If this logon was authenticated via the NTLM protocol (instead of Kerberos for instance) this field tells you which version of NTLM was used. See security option "Network security: LAN Manager authentication level"
Key Length - Length of key protecting the "secure channel". See security option "Domain Member: Require strong (Windows 2000 or later) session key". If value is 0 this would indicate security option "Domain Member: Digitally encrypt secure channel data (when possible)" failed.

Top 10 Events to Monitor

Examples of 4624

An account was successfully logged on.

Subject:
   Security ID: SYSTEM
   Account Name: WIN-R9H529RIO4Y$
Account Domain: WORKGROUP
Logon ID: 0x3e7
Logon Type:10
New Logon:
      Security ID: WIN-R9H529RIO4Y\Administrator
Account Name: Administrator
Account Domain: WIN-R9H529RIO4Y
Logon ID: 0x19f4c
Logon GUID: {00000000-0000-0000-0000-000000000000}
Process Information:   Process ID: 0x4c0
Process Name: C:\Windows\System32\winlogon.exe
Network Information:     Workstation Name: WIN-R9H529RIO4Y
   Source Network Address: 10.42.42.211
   Source Port: 1181Detailed Authentication Information:     Logon Process: User32
   Authentication Package: Negotiate
Transited Services: -
Package Name (NTLM only): -
Key Length: 0

This event is generated when a logon session is created. It is generated on the computer that was accessed.

The subject fields indicate the account on the local system which requested the logon. This is most commonly a service such as the Server service, or a local process such as Winlogon.exe or Services.exe.

The logon type field indicates the kind of logon that occurred. The most common types are 2 (interactive) and 3 (network).

The New Logon fields indicate the account for whom the new logon was created, i.e. the account that was logged on.

The network fields indicate where a remote logon request originated. Workstation name is not always available and may be left blank in some cases.

The authentication information fields provide detailed information about this specific logon request.

Logon GUID is a unique identifier that can be used to correlate this event with a KDC event.
Transited services indicate which intermediate services have participated in this logon request.
Package name indicates which sub-protocol was used among the NTLM protocols.
Key length indicates the length of the generated session key. This will be 0 if no session key was requested.

.: Şaban Karaaslan :.

19 Şubat 2011 Cumartesi

Updated version for Schedule Maintenance Mode Management Pack Guide on OpsManJam

User Logon Time Report for Operations Manager 2007

Subject:

Logon Type:

New Logon:

Process Information:

Network Information:

Detailed Authentication Information:

OpsMgr Custom Reporting Links