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 ); 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) ) INSERT INTO @temp(RowNum, DateTime, EventSource, Computer,Type,EventID, EventChannelTitle, UserName, EventDescription, LogOffTime, LogTime) 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 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 ); 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) ) INSERT INTO @temp(RowNum, DateTime, EventSource, Computer,Type,EventID, EventChannelTitle, UserName, EventDescription, LogOffTime, LogTime) 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 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 |
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 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.
The following field is not logged in Window 2000:
The following fields are not logged in Windows 2000 or XP:
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
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.
---------------------------------------------------------------- ---------------------------------------------------------------- 4624: An account was successfully logged on On this page
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.
|
Hiç yorum yok:
Yorum Gönder