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. [Screenshots]   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. Don’t save them in the Default MP ;-) 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…   Have fun creating Custom OpsMgr Reports!

Today I created a video demonstrating monitoring your Unix and Linux servers with OpsMgr 2007 R2 for the Dutch LinuxWorld expo next week. I created this video so I can also have a moment to look around at the LinuxWorld Expo ;-) It’s part of a rolling presentation so I didn’t added any sound to it, but I thought it would be nice to upload the video anyway. In the video you can see that I’m monitoring two Linux servers, one Redhat and one Suse Novell machine in my demo environment. To demo the health of the Suse server I stopped the powersaved service on the machine and with the help of a geographical map created with the latest version of Savision Live Maps you can see that the Suse server is going from healthy to unhealthy and to healthy again after restarting the service.   See you at the LinuxWorld expo next week!

I just downloaded and installed the latest version of Savision Live Maps in my demo environment and started creating a new List View for my Linux servers.

The new release of Live Maps 4.1 for Operations Manager R2 contains, besides some fixes and performance improvements, a number of new features that have been added based on customer feedback. Some of the new features are highlighted in this article, a complete overview can be found in the release notes. You can read more about the new features on their weblog. The new release can be downloaded here. You may ask why are you creating a List View for your Linux Servers? I’m preparing a demo for next week’s Dutch LinuxWorld event in Utrecht. You can visit us at booth D072 of our partner Ictivity. You can also take a look at the website http://www.metopenvizier.nl/. [Dutch]