CA Service Management

Hello Team

how can i fetch the following records for CR ticket from  sql.. can anyone provide script

1. RFC #

2..Requester

3.Category

4.Status

5.Priority

6.Assigned To

7.Group

8.CAB

9.Impact

10.Summary

11.Description

12.Open Date

13.Close Date

there are some custom fields also which i will add in script once i get for these fields.

Hi Aamir,

There is a OOTB provided view, namely: View_Change, you can use it as the basic starting point as it returns most of the info you need.

You will then need to join relevant tables for reference fields to get correct values for the rest or for fields not already covered.

===

Kind Regards,

Brian

Hi Brian

Can you pls guide me on view_change

This should get you most of the way there:

use mdb;
go
select
 CO.chg_ref_num as rfc_no
,CN.last_name as requestor_lastname
,CN.first_name as requestor_firstname
,CC.sym as category
,CS.sym as status
,PR.sym as priority
,CN2.last_name as assignee_lastname
,CN2.first_name as assignee_firstname
,CN3.last_name as groupname
,CN4.last_name as cabname
--
-- '08:00' added to dates for my time zone UTC+8, use what's appropriate for your TZ
--
,DATEADD(s,CO.open_date, '01-Jan-1970 08:00') as date_opened
,DATEADD(s,CO.close_date, '01-Jan-1970 08:00') as date_closed
,IM.sym as change_impact
,CO.summary
,CO.description
from chg CO
left outer join ca_contact CN on CO.requestor = CN.contact_uuid
left outer join chgcat CC on CO.category = CC.code
left outer join chgstat CS on CO.status = CS.code
left outer join pri PR on CO.priority = PR.enum
left outer join ca_contact CN2 on CO.assignee = CN2.contact_uuid
left outer join ca_contact CN3 on CO.group_id = CN3.contact_uuid
left outer join ca_contact CN4 on CO.cab = CN4.contact_uuid
left outer join impact IM on CO.impact=IM.enum
;
go

Regards,

James

Hi James ...Many thanks for this code...this will fulfil my current requirement. but if u can add custom field records also...would be great...here are information below for custom tables.

La-Qa,

Please understand that we are not here to do your work. We are here to explain you and help you grow in your field of expertise.

As a SDM administrator, SQL knowledge is mandatory. Accessing custom fields in related table is propably the most simplest SQL operation. You NEED to take a SQL course like this one : https://www.udemy.com/microsoft-sql-for-beginners/ 

Getting your related tables information will be done exactly like James showed you for ca_contact, chgcat, chgstat, pri, impact etc...

Please take 1 hour to understand what he did and apply it to your custom fields. This way, you will be able to do it all by yourself next time.

So in the end, what James gave you is highly sufficient to achieve what you want without further help.

We will gladly review some SQL notion with you if you need to but we will not type the query for you.

As a conclusion : Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime

hi

i totally agree with you...actually this is something which was really

urgent .but yes i will do this during the weekend.

thanks

On 16-Mar-2018 4:56 pm, "pier-olivier.tremblay" <

And do not take that one personnally, it's for your own good

If this is urgent, i can understand and give you the query, but for the next one you will try to learn and do it by yourself.

Definitely...Thankyou... 

Since i'm not able to test this may not work right ahead but anyway

Add this in the select portion :

CO.zAreas,
zAreas.name,
CO.zModules,
zMod.zcr_module_name,
co.zSubModules,
zSmod.zcr_submodule_name,
CO.zVendors,

Add this in the join portion :

left join zcr_module zMod
on CO.zcrModule = zMod.id
left join zcr_submodule zSmod
on CO.zcrSubModule = zSmod.id
left join zcrAreas zAreas
on CO.zcrArea = zAreas.id
left join zVendors
on CO.zvendorName = zVendors.id

And you type the next one by yourself

Many Thanks Pier