Altiris User Logon/Logoff Report

[fmora]

I'm hoping someone can please help me with this, I'm pretty new to Altiris.  Is there a way to run/create a report to display which user's logged on/off on a specific date and between certain hours?  I need to find out who logged on/off on three specific dates; but I only need to know which users logged on/off between 5PM and 5AM.  Is this possible?  I wasn't able to locate any canned reports on NS 6.5 that will return these results, I'm assuming a custom report will need to be created.  Also, is it possible to run this report against a collection? I just need the computer name, username, logon time and logoff time.  I want to know if someone logged on after 5PM and/or logged off before 5AM.  Has anyone else created such a report or know if one is available on NS?  Any help would be greatly appreciated.

Thanks!

[Migration User]

It can be done and I know Altiris is recording that information, but it'll take some time to build. I'll see if I can whip it up and post it as a download for you.

[Migration User]

fmora,

You could use raw SQL to either create a report for a given day or date range OR you could use Query Analyzer/SQL Server Management Studio to query the data directly.

Sometimes I prefer to query the data directly.  It allows for the quick and easy changing of variables and can be a bit cleaner.

Here is a query you can run and change the params to fit what you need.  You can change the date range to whatever you like and can change the time to whatever you like as well.  The following example will return any log on/logoff events that occurred on July 15th between 2:00 PM and 4:00 PM.  If you are looking for a specific computer, you can uncomment the first commented line and add a computer name.  If you want a specific event, you can un-comment the second line and use Logon or Logoff depending on which type of event you like.

SELECT c.Name, cl.Event, cl.[User], cl.Domain, cl.Time
FROM vComputer c
INNER JOIN Evt_AeX_Client_LogOn cl ON cl._ResourceGuid = c.Guid
WHERE CONVERT(CHAR(10),cl.Time,101) BETWEEN '07/14/2009' AND '07/14/2009'
AND CONVERT(CHAR(10),cl.Time,114) BETWEEN '14:00' AND '16:00'
-- AND c.Name = 'ComputerName'
-- AND cl.Event = 'Logon'
ORDER BY cl.Time

Also, I created a report that asks for the start and end dates and the start and end time, but don't see where I can attach it.  I will either post it as a download or try to PM it to you.

Let me know if you have any questions!

RS

[Migration User]

Dang it Smiz -- You beat me by minutes! :D

I took Smiz's SQL and modified it just a little.  The two notable changes I made are the removal of the CONVERT() functions. 

SELECT c.Name, cl.Event, cl.[User], cl.Domain, cl.Time
FROM vComputer c
INNER JOIN Evt_AeX_Client_LogOn cl ON cl._ResourceGuid = c.Guid
WHERE cl.Time >= '07/14/2009' AND cl.Time < '07/15/2009'
AND DATEPART(HOUR, cl.Time) BETWEEN 14 AND 16
ORDER BY cl.Time

-- Mitch

[Migration User]

 When I run the SQL that you posted I get the following error: 

Msg 208, Level 16, State 1, Line 1
Invalid object name 'vComputer'.

Am I missing something?

[Migration User]

How are you executing the SQL?  We're both using Microsoft SQL Server Management Studio (SSMS).  Make sure that you have set your Altiris database as the "current" db.  If you know the name of the database, you can prefix the SQL above so it'd look like this.

USE dbname
GO

SELECT c.Name, cl.Event, cl.[User], cl.Domain, cl.Time
FROM vComputer c
INNER JOIN Evt_AeX_Client_LogOn cl ON cl._ResourceGuid = c.Guid
WHERE cl.Time >= '07/14/2009' AND cl.Time < '07/15/2009'
AND DATEPART(HOUR, cl.Time) BETWEEN 14 AND 16
ORDER BY cl.Time


I just ran this against both NS 6.x and 7.x databases.

-- Mitch

[Migration User]

That first bit is what I was missing...or this part:
USE dbname
GO

I swapped out "dbname" with "Altiris" which I am pretty sure is the default name.

Thanks for the help...

[Migration User]

Yes, I believe Altiris was the default name for a NS 6.x installation.  For NS 7.x, the default name is now Symantec_CMDB, the one I have highlighted in my graphic.

-- Mitch

[fmora]

This is what I was looking for, thank you all for your help!

[fmora]

Is there a way to run this against a collection?  I was trying to use "Filter this report against defined collections" but I don't have any options to select the field that corresponds to the resource identifier. Is it possible to do this?

Thanks!

[Migration User]

I created the report and made it available in the downloads section as well.

So, if you want to download and import into the Altiris Console, you can do so.  This would allow you to schedule reports and all that good stuff!

www.symantec.com/connect/downloads/report-logins-between-dates-and-times

RS

[Migration User]

Sure!

If you are using SQL you could do something like this:

SELECT c.Name, cl.Event, cl.[User], cl.Domain, cl.Time
FROM vComputer c
INNER JOIN Evt_AeX_Client_LogOn cl ON cl._ResourceGuid = c.Guid
WHERE CONVERT(CHAR(10),cl.Time,101) BETWEEN '07/14/2009' AND '07/14/2009'
AND CONVERT(CHAR(10),cl.Time,114) BETWEEN '14:00' AND '16:00'
AND c.Guid IN (
SELECT c.ResourceGuid
FROM CollectionMembership c
INNER JOIN [vItem] i ON c.CollectionGuid = i.Guid
WHERE i.Name IN ('CollectionName', 'SecondCollectionName')
)
-- AND c.Name = 'ComputerName'
-- AND cl.Event = 'Logon'
ORDER BY cl.Time

Just replace the "CollectionName" above with the collection you are wanting to filter against.  If you want to filter against multiple collections, continue adding collections in the format provided in the example above.

Note: if you are using MitchInOmaha's WHERE clauses for the date and time ranges, replace them as well.

RS

[Migration User]

I am using the Solution posted by The smiz. It is only returning one month of data. Am I doing something wrong?