CA Service Management

Expand all | Collapse all

How to fetch All analyst with their respectives groups from sql

  • 1.  How to fetch All analyst with their respectives groups from sql

    Posted Oct 18, 2016 12:51 PM

    Hello SD Team

    i have 2 request for the sql queries

     

    1. i need to pull All the Analysts with their respective groups/departments. Please provide query to fulfill my need.

     

    2.i need to know the last time each of the Analysts login into servicedesk. i know the records are maintain in session_log table. Please provide query for this.

     

    Thanks



  • 2.  Re: How to fetch All analyst with their respectives groups from sql

    Posted Oct 18, 2016 02:10 PM

    Hi Aamir,

     

    I'm not logged in to a lab at the moment, but from the top of my head I would suggest the following:

     

    1. Groups and members there of are stored in the grp_mem table. You would need to join to the ca_contact table to decode the actual group name and member name.

     

    2. The session_log date has a login time stamp and logout time stamp, I would use the maximum of the login time stamp, something like:

     

    SELECT DISTINCT login_userID, max (login time stamp) FROM session_log.

     

    Hope this to steer you in the right direction.

     

    P.S. The field names I mentioned are just fictional. You would need to check the table for the correct field names ;-)

     

    I can possibly give you the actual SQL queries, later or tomorrow when I'm my lab

     

    Kind Regards,

    Brian



  • 3.  Re: How to fetch All analyst with their respectives groups from sql

    Posted Oct 18, 2016 03:22 PM

    Hello Brian

     

    Yes you understood right.

     

    Can u please write query as an example to join ca_contact and grpmem table

    to decode the actual group name and member name.

     

    Please write query for session log also

     

    Many thanks

     

    On 18-Oct-2016 11:11 pm, "Brian_Mathato" <



  • 4.  Re: How to fetch All analyst with their respectives groups from sql

    Posted Oct 18, 2016 03:26 PM

    okay Brian, just saw your full message now. yes pls do let me know when u are in lab.

     

    Thankyou



  • 5.  Re: How to fetch All analyst with their respectives groups from sql

    Posted Oct 19, 2016 05:42 AM

    Hi Aamir,

     

    Here's the SQL query to get a group and its members:

     

    SELECT a.last_name AS GroupName,
    (b.last_name + ', ' + b.first_name + ' (' + b.userid + ')') As GroupMember
    FROM grpmem
    INNER JOIN ca_contact a ON a.contact_uuid = grpmem.group_id
    INNER JOIN ca_contact b ON b.contact_uuid = grpmem.member


  • 6.  Re: How to fetch All analyst with their respectives groups from sql

    Posted Oct 19, 2016 05:58 AM

    Hello Brian,

    Thanks for the query. It ran without any errors. but i have members present the group but it not shoqing. see below.. PLease suggest.

    Also can you please provide query for the session log request.? it had some errors the one you provided yesterday

     

    thanks

     

     



  • 7.  Re: How to fetch All analyst with their respectives groups from sql

    Posted Oct 19, 2016 06:26 AM

    Hello Brian 

     

    They query worked now by editing .Now its pulling correct information

     

    SELECT a.last_name AS GroupName,
    (b.last_name) As GroupMember
    FROM grpmem
    INNER JOIN ca_contact a ON a.contact_uuid = grpmem.group_id
    INNER JOIN ca_contact b ON b.contact_uuid = grpmem.member

    Now can you please provide session log query. Pull max no.of login into sdm by analyst.

     

    Many thanks



  • 8.  Re: How to fetch All analyst with their respectives groups from sql

    Posted Oct 19, 2016 08:29 AM

    Here's the first one, with the complete name instead of just the last_name like you did.

     

    Brian's example returns NULL for the user if one of the field is NULL. This is what happens in SQL Server when you concatenate a null value.

     

    So you can use COALESCE.

    select
    coalesce(cnt.last_name,'') + ', ' + coalesce(cnt.first_name,'') + ' (' + coalesce(cnt.userid,'') + ')' as 'User',
    grp.last_name as 'Is member of'
    from ca_contact cnt
    inner join grpmem mem
    on mem.member=cnt.contact_uuid
    inner join ca_contact grp
    on grp.contact_uuid=mem.group_id
    where grp.contact_type=2308 and cnt.contact_type <> 2308
    order by 1

     

    And the second one :

     

    select
    coalesce(cnt.last_name,'') + ', ' + coalesce(cnt.first_name,'') + coalesce(cnt.userid,'') as 'User',
    (select max(sess.login_time) from session_log sess where contact=cnt.contact_uuid) as 'Last loging time'
    from ca_contact cnt

     

    Hope this helps

     

     

    And by the way, if you administer SDM, you should consider taking SQL courses, it will facilitate your life 



  • 9.  Re: How to fetch All analyst with their respectives groups from sql

    Posted Oct 19, 2016 08:44 AM

    Many Thanks pier-olivier.tremblay

     

    this has fulfilled my need. Just the last thing, How can i get last login time in another easier format which can be understand easily. i want the last time login by analyst. is it possible to show in hh:mm format?

     



  • 10.  Re: How to fetch All analyst with their respectives groups from sql

    Posted Oct 19, 2016 08:52 AM

    this is the example of conversion time stamp field to date

     

    SELECT DATEADD(second,1364572980-(DATEDIFF(second,GETDATE(),GETUTCDATE())), CAST('1970-01-01 00:00:00' AS datetime))

     

    just add this in your query



  • 11.  Re: How to fetch All analyst with their respectives groups from sql

    Posted Oct 19, 2016 08:54 AM

    Aleksandar's answer will work out for almost every cases, but if your country is subject to Daylight saving time, you need a lot more logic to get the right "time".



  • 12.  Re: How to fetch All analyst with their respectives groups from sql

    Posted Oct 19, 2016 09:00 AM

    Thanks Aleksandar & Possq...really appreacite. this from west africa



  • 13.  Re: How to fetch All analyst with their respectives groups from sql

    Posted Oct 19, 2016 04:18 PM

    Dear P0ssq

     

    i noticed that the first query fetching members with their group name contain some inactive contacts in my sdm also showing in the output of the query. i believe we need to add inactive column of ca_contact table and set it to 0. So the query only fetch active contacts with their groups name. Can you pls add in your code and paste it here. i tried but its giving error. you can edit in Brain code also (.any)

     

    Secondly, as Alexsandar stated, where the exactly code should i paste in your query (2nd query) to get accurate result.? im getting output like below.pls add that . 

     

    Many thanks

     



  • 14.  Re: How to fetch All analyst with their respectives groups from sql

    Posted Oct 20, 2016 03:00 AM

    Dear pier-olivier.tremblay

     

    i have added inactive column on your query and it worked.

    where grp.contact_type=2308 and cnt.inactive=0 and cnt.contact_type <> 2308

     

    Please let me know where to put exactly the following code for conversion of time stamp in your code for the second query you provided.

     

    SELECT DATEADD(second,1364572980-(DATEDIFF(second,GETDATE(),GETUTCDATE())), CAST('1970-01-01 00:00:00' AS datetime))

     

    Thanks



  • 15.  Re: How to fetch All analyst with their respectives groups from sql

    Posted Oct 20, 2016 03:05 AM

    select
    coalesce(cnt.last_name,'') + ', ' + coalesce(cnt.first_name,'') + ' (' + coalesce(cnt.userid,'') + ')' as 'User',
    grp.last_name as 'Is member of',
    (SELECT DATEADD(second,(select max(sess.login_time) from session_log sess where contact=cnt.contact_uuid)-(DATEDIFF(second,GETDATE(),GETUTCDATE())), CAST('1970-01-01 00:00:00' AS datetime))) as 'Last loging time'
    from ca_contact cnt
    inner join grpmem mem
    on mem.member=cnt.contact_uuid
    inner join ca_contact grp
    on grp.contact_uuid=mem.group_id
    where grp.contact_type=2308 and cnt.contact_type <> 2308
    order by 1

     

     

     

    or second query

     

     

    select
    coalesce(cnt.last_name,'') + ', ' + coalesce(cnt.first_name,'') + coalesce(cnt.userid,'') as 'User',
    (SELECT DATEADD(second,(select max(sess.login_time) from session_log sess where contact=cnt.contact_uuid)-(DATEDIFF(second,GETDATE(),GETUTCDATE())), CAST('1970-01-01 00:00:00' AS datetime))) as 'Last loging time'
    from ca_contact cnt
    where cnt.inactive=0



  • 16.  Re: How to fetch All analyst with their respectives groups from sql

    Posted Oct 20, 2016 03:28 AM

    Many Thanks ALeksander and the entire team (Y)