User Logon Time Report for Operations Manager 2007

User Logon Time Report for Operations Manager 2007

  • Comments 1
  • Likes

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:

image

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:

  1. Create two NT Event Collection Rules for each EventID (528 and 865) in OpsMgr.
  2. Create an SQL Query which calculates the Time difference between the two EventID’s collected by the Event Collection Rules for each user.
  3. 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]

image image image

 

image

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 ;-)

image

image

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

image

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:

image 

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:

image

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:

image

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:

image

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:

image

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.

image

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

image

Select Next in the Welcome to the Report Wizard Screen

image

Create a Data Source

image

Click Edit button to enter the SQL Server information.

image

And Test Connection if you want.

Click Next in the Report Wizard Screen.

image 

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.

image

Select your Report Type and click on Next

image

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

image

Select the Table Style of the Report and click on Next

image

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

image

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

image

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

image

image

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.

image

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.

image

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

 

Have fun creating Custom OpsMgr Reports!

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Can i do this on SCOM 2012 RC ?