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!
