CA Service Management

I need a list of all contacts which have logged in  from 15th June 2015 to 31th March 2016 .Please help . How can I achieve this?

Hi SILADITYA,

You can run Select query against Session_log table in Database, it will list the contact IDs which has logged  between  given dates. And refer ca_contact table for contact ID mapped with user.

You may to create select join query to get the list.

Below is query:

select session_log.login_time,ca_contact.first_name,ca_contact.last_name

From Session_log INNER JOIN ca_contact ON session_log.contact=ca_contact.contact_UUID where login_time BETWEEN '1434326400' AND '1459382400'

You can create more refine query to get the data in Human readable Date format.

Let us know if it helps.

Regards,

Anil

I am trying the below query

select ca_contact.userid,session_log.* from session_log FULL JOIN ca_contactonsession_log.contact=ca_contact.contact_uuid where (date condition )

 but unable to write date condition as time is mentioned as numbers in session_log table .So please explain the mechanism for time calculation.

In your query you have also mentioned date as numbers .Please explain date conversion mechanism .

there is a function in MDB database to convert it. It's called secs2date(number)

select ca_contact.userid,session_log.* from session_log FULL JOIN ca_contact on session_log.contact=ca_contact.contact_uuid where login_time BETWEEN '1483209000' AND '1485714600'

Trying this query but it is  taking to much  time(showing Dubbing) but after 45 mins I have cancelled it as it is not displaying anything .

select ca_contact.userid,session_log.* from session_log FULL JOIN ca_contact on session_log.contact=ca_contact.contact_uuid where login_time BETWEEN '1483209000' AND '1485714600'

Trying this query but it is  taking to much  time(showing Dubbing) but after 45 mins I have cancelled it as it is not displaying anything.Please suggest.

HI Shiladitiya,

Please refer below link which will assist you in converting the epoch time in XLs. Only thing you have copy the output from DB to XLs and then using below link you can get desired output.

Excel 2013 Quick Tip: Convert Unix (Epoch) time in Excel - YouTube 

Regards,

Anil

Thank you  Anil for your support.

Hi Sila,

Could you confirm if the solution provided by Anil helped you resolve the issue?.

regards,

Maheshwar

Yes it helps me a lot.

And since SQL Server 2014 (or maybe 2008, don'T remember), there is a built-in way to apply timezone to an epoch.

datetimeoffset (Transact-SQL) | Microsoft Docs 

Here something that might work...

-- change -18000 to the appropriate time zone

select
c.last_name, c.first_name, DateAdd(ss,(log.login_time) -18000,'19700101') as [Last Login]
from ca_contact c
join session_log log on log.contact = c.contact_uuid
where DateAdd(ss,(log.login_time) -18000,'19700101') >= '2015-06-15'
and
DateAdd(ss,(log.login_time) -18000,'19700101') <= '2016-03-31'
order by
DateAdd(ss,(log.login_time) -18000,'19700101')

Now also facing same issue.