Client Management Suite

I'm using the following sql query to attempt to pull a report of our User logon and logoff times.

For the life of me i cant figure out why the 'login' column ONLY displays the 'logoff' times... Cant seem to find the login times anywhere...

Anyone care to look at this query for me? Or if anyone has a better one?

Select
  Evt_AeX_Client_LogOn.[User],
  Evt_AeX_Client_LogOn.[Login Time],
  Evt_AeX_Client_LogOn.Event,
  Inv_AeX_AC_Identification.Name
From
  Evt_AeX_Client_LogOn Inner Join
  Inv_AeX_AC_Identification On Evt_AeX_Client_LogOn._ResourceGuid = Inv_AeX_AC_Identification._ResourceGuid
Where
  Evt_AeX_Client_LogOn.[Login Time] Between GetDate() - 7 And GetDate()
Group By
  Evt_AeX_Client_LogOn.[User], Evt_AeX_Client_LogOn.[Login Time], Evt_AeX_Client_LogOn.Event, Inv_AeX_AC_Identification.Name
Order By
  Evt_AeX_Client_LogOn.[User], Evt_AeX_Client_LogOn.[Login Time]

Login Time only exists for Logoff events.  By using a WHERE clause that only returns data for Login Time between 7 days ago and today, you're excluding all Logon events.  So ask it to also return any results where Login Time is a null value.

I'm not a SQL person, but give that a run.

Select
  Evt_AeX_Client_LogOn.[User],
  Evt_AeX_Client_LogOn.[Login Time],
  Evt_AeX_Client_LogOn.Event,
  Inv_AeX_AC_Identification.Name
From
  Evt_AeX_Client_LogOn Inner Join
  Inv_AeX_AC_Identification On Evt_AeX_Client_LogOn._ResourceGuid = Inv_AeX_AC_Identification._ResourceGuid
Where
  Evt_AeX_Client_LogOn.[Login Time] Between GetDate() - 7 And GetDate() OR Evt_AeX_Client_LogOn.[Login Time] IS NULL
Group By
  Evt_AeX_Client_LogOn.[User], Evt_AeX_Client_LogOn.[Login Time], Evt_AeX_Client_LogOn.Event, Inv_AeX_AC_Identification.Name
Order By
  Evt_AeX_Client_LogOn.[User], Evt_AeX_Client_LogOn.[Login Time]

I re-wrote this, because even after "fixing" it for you I'm not sure it gave you the data you wanted.  Is this what you're looking for?

Select

Evt_AeX_Client_LogOn.[User],

Evt_AeX_Client_LogOn.Event,

Evt_AeX_Client_LogOn._eventTime,

Inv_AeX_AC_Identification.Name

From

Evt_AeX_Client_LogOn Inner Join

Inv_AeX_AC_Identification On Evt_AeX_Client_LogOn._ResourceGuid = Inv_AeX_AC_Identification._ResourceGuid

Where

Evt_AeX_Client_LogOn._eventTime Between GetDate() - 30 And GetDate()

Group By

Evt_AeX_Client_LogOn.[User], Evt_AeX_Client_LogOn._eventTime, Evt_AeX_Client_LogOn.Event, Inv_AeX_AC_Identification.Name

Order By

Evt_AeX_Client_LogOn.[User], Evt_AeX_Client_LogOn._eventTime

I can most definately work with this!

Thanks for your time :-)