Report of Logins Between Dates and Times

This report is very useful, since I'm learning reporting under Altiris would you mind sharing the steps you took to create such a report?

Thanks

Smiz Thanks for the post. I Imported the report but It is only turning in one month of data. I am not a SQL guru and could use some help getting this to work as designed. What info do I need to give you so you may help me or point me in the write direction. I have not done anything with the Altiris Tables or Schema.

First,

The dates and times need to be entered in the exact format as they are in the example.  Time in 00:00 24 hour format and date in mm/dd/yyyy format.

Try running this query and see what you get.  Also, have you verified there is data for the date range you are looking for?

SELECT    DISTINCT T1.[Guid] AS 'Guid',
        T1.[Name] AS 'Name',
        T0.[Event] AS 'Event',
        T0.[User] AS 'User',
        T0.[Domain] AS 'Domain',
        T0.[Time] AS 'Time'
FROM [vResourceEx] T1
INNER JOIN [Evt_AeX_Client_LogOn] T0 ON T1.[Guid] = T0.[_ResourceGuid]
WHERE T1.[ResourceTypeGuid] = '493435F7-3B17-4C4C-B07F-C23E7AB7781F'
  AND T1.[IsManaged] = 1
  AND CONVERT(CHAR(10),T0.[Time],101) BETWEEN '09/01/2009' AND '09/10/2009'
  AND CONVERT(CHAR(10),T0.[Time],114) BETWEEN '00:00' AND '23:59'
ORDER BY T0.[Time] ASC

thanks

I seemed to not be getting any results from any recent months, what triggers entry to the [Evt_AeX_Client_LogOn] table? ie which module in CMS.

We used the Carbon Copy log on tables for a bit, but those were unreliable as it tended to give false readings when the service was started/stopped.

PS the report would be much better with two extra filters; by 'user' and by 'computer'...gives it a much better "who has been where" feel. I'm sorry, i'm rubbish at SQL so ours arent worth uploading.

Has anyone converted this report to a NS7 report when I imported it I got complaints that it needed to be upgraded.  The SQL posted above works just fine in my db.

I have built a version 7 report a couple of things need improvement but one thing to note I don't know how to attached the xml export to this forum post.

Minor improvements could be made:

1. Report Parameters - For some reason even though I have changed the "Display/Eval Order" it does not affect the order of display of these to my user.  They are alphabetically ordered and I can't figure out how to change this.  Not a big deal but the order is:

Computer Name:

End Date:

Start Date:

User Name:

Would be nice to put switch the End and Start dates.

2. Add in security scoping

3. Currently it allows me to define date and time but I would like the default end time to always be 23:59 not sure how to set this to default this way.