Finally, I have come up with the below query for my requirement.
To get the Authentication Type - I am referring to ADS_OBJECT_TYPE_STR.Object_Type_Name IN (‘Windows AD’, ‘Enterprise’), but I dnt see any data in my dataset. Need to figure out if the query I have build is incorrect by picking up ADS_OBJECT_TYPE_STR.Object_Type_Name or anything with my dataset. Please correct me if am wrong in picking up the column here to get the Auth Type.
To gethe Logout Method - I am referring to the table ADS_EVENT_TYPE_STR.Event_Type_ID IN (1015). I am also reefrring to the columns
ADS_STATUS_STR.Status_ID,
ADS_STATUS_STR.Status_Name to find out how the user was logged ou.But, I see only 2 statuses there Logout Succeeded and Logout Failed(This need to figure out how was the user logged out - is it really beacuse of the user inactivity). Any idea on this?
And also I need the EST Timings for filtering the data.Any idea on this would also help me…
Please refer to the below query and correct me if am wrong.
SELECT distinct
ADS_EVENT.User_Name,
MIN(ADS_EVENT.Start_Time) as Session_Start_Time,
MAX(ADS_EVENT.Start_Time) as Session_End_Time,
Convert(Float,DATEDIFF(second, MIN(ADS_EVENT.Start_Time),MAX(ADS_EVENT.Start_Time)))/1000 as Session_Duration_Sec,
ADS_SERVER_NAME_STR.Server_Name,
ADS_SERVICE_TYPE_STR.Service_Type_Name,
ADS_EVENT_TYPE_STR.Event_Type_ID,
ADS_EVENT_TYPE_STR.Event_Type_Name,
ADS_STATUS_STR.Status_ID,
ADS_STATUS_STR.Status_Name
–ADS_OBJECT_TYPE_STR.Object_Type_Name
FROM
ADS_SERVER_NAME_STR INNER JOIN ADS_AUDITEE
ON (ADS_SERVER_NAME_STR.Cluster_ID=ADS_AUDITEE.Cluster_ID
and ADS_SERVER_NAME_STR.Server_ID=ADS_AUDITEE.Server_ID AND ADS_SERVER_NAME_STR.Language = ‘EN’)
INNER JOIN ADS_SERVICE_TYPE_STR
ON (ADS_SERVICE_TYPE_STR.Service_Type_ID=ADS_AUDITEE.Service_Type_ID AND ADS_SERVICE_TYPE_STR.Language = ‘EN’)
INNER JOIN ADS_EVENT
ON (ADS_AUDITEE.Cluster_ID=ADS_EVENT.Cluster_ID and ADS_AUDITEE.Server_ID=ADS_EVENT.Server_ID
and ADS_AUDITEE.Service_Type_ID=ADS_EVENT.Service_Type_ID)
INNER JOIN ADS_EVENT_TYPE_str
ON (ADS_EVENT.Event_Type_ID=ADS_EVENT_TYPE_STR.Event_Type_ID AND ADS_EVENT_TYPE_STR.Language=‘en’)
INNER JOIN ADS_STATUS_STR
ON (ADS_STATUS_STR.Event_Type_ID=ADS_EVENT_TYPE_STR.Event_Type_ID AND ADS_STATUS_STR.Language=‘EN’)
/*INNER JOIN ADS_OBJECT_TYPE_STR
ON (ADS_EVENT.Object_Type_ID=ADS_OBJECT_TYPE_STR.Object_Type_ID AND ADS_SERVICE_TYPE_STR.Language = ‘EN’)*/
where
–ADS_OBJECT_TYPE_STR.Object_Type_Name IN (‘Windows AD’, ‘Enterprise’)
ADS_EVENT_TYPE_STR.Event_Type_ID IN (1015)
AND ADS_EVENT.Start_Time BETWEEN ‘1-NOV-2013’ and ‘18-NOV-2013’
GROUP BY
ADS_EVENT.User_Name,
ADS_SERVER_NAME_STR.Server_Name,
ADS_SERVICE_TYPE_STR.Service_Type_Name,
ADS_EVENT_TYPE_STR.Event_Type_ID,
ADS_EVENT_TYPE_STR.Event_Type_Name,
ADS_STATUS_STR.Status_ID,
ADS_STATUS_STR.Status_Name
–ADS_OBJECT_TYPE_STR.Object_Type_Name
praven_329 (BOB member since 2011-01-28)