CA Service Management

for sure! Nice job

Sharing is the foundation of many success 

May I?

Function to get the descendent relation tree for a CI, looking for a specific relation type.

CREATE FUNCTION [dbo].[getCIHierarchyDownWithRelType]
(
@rootCIUUID binary(16),
@relTypeID int
)
RETURNS TABLE
AS
RETURN
(
--create a CTE to use with the recursivity

With ciHierarchy(childUUID,relType,parentUUID)
as
(
--select in the relationship table
Select BMHIER.hier_child, BMHIER.ci_rel_type, BMHIER.hier_parent
from mdb.dbo.busmgt BMHIER
where BMHIER.hier_parent = @rootCIUUID and BMHIER.ci_rel_type= @relTypeID
UNION ALL
--union with another select in the relationship table but with a join on the CTE
Select BMHIER.hier_child, BMHIER.ci_rel_type, BMHIER.hier_parent
from mdb.dbo.busmgt BMHIER
inner join ciHierarchy BMHIERTEMP -- join on the CTE. Unleash the recursivity!
on BMHIERTEMP.childUUID=BMHIER.hier_parent and BMHIER.ci_rel_type= @relTypeID
)

select * from ciHierarchy
)

GO

Adding another from one of my previous post so we centralize  

Retrieve analysts that are not members of any groups

SELECT        ca_contact.userid, ca_contact.first_name, ca_contact.last_name, acctyp_v2.licensed, acctyp_v2.access_level, acctyp_v2.sym AS access_type

FROM            usp_contact INNER JOIN

                         ca_contact ON usp_contact.contact_uuid = ca_contact.contact_uuid LEFT OUTER JOIN

                         acctyp_v2 ON usp_contact.c_acctyp_id = acctyp_v2.id

WHERE        (acctyp_v2.access_level >= 20) AND (acctyp_v2.licensed = 1) AND (ca_contact.inactive <> 1) AND

                             ((SELECT        COUNT(*) AS Expr1

                                 FROM            grpmem

                                 WHERE        (member = usp_contact.contact_uuid)) = 0)

/J

Sound good!

/J

yep in fact there is quite few there

Not all are directly useful as they are mostly used to simplify SDM layer but always good to have a quick look

/J

HI Jerome Mayer,

Thank you for colaboration and sharing! 

I built a document and linked here

Regards

Thank you for sharing! I  didn't know about the functions CA build into the MDB.  I've always used dbo.hex to convert UUID strings...

same way may you want to convert UUID to readable format just use another CA function

USE mdb
SELECT contact_uuid, dbo.uuid_to_char(contact_uuid) AS char_contact_uuid FROM ca_contact

Result:

use dbo.uuid_from_char() to to the reverse

/J

another SQL tip (simple but useful)

you can use the secs2date (CA build function in the mdb ) to quickly convert Unix epoch time to date in your result.

i.e.:

USE mdb
SELECT ref_num, dbo.secs2date(open_date) as open_date FROM call_req

result:

my 2 cents

/J

adding my small contribution here:

VERIFY inactive analyst for more that 90 days
******

USE mdb

SELECT ca_contact.contact_type AS contact_type, ca_contact.inactive AS inactive, ca_contact.userid, acctyp_v2.sym AS access_type, acctyp_v2.licensed
FROM ca_contact LEFT OUTER JOIN usp_contact ON ca_contact.contact_uuid = usp_contact.contact_uuid LEFT OUTER JOIN acctyp_v2 ON usp_contact.c_acctyp_id = acctyp_v2.id
WHERE ca_contact.contact_uuid NOT IN (select contact from session_log where login_time > (DATEDIFF(s, '1970/01/01 00:00:00', GETDATE())-7776000))
AND ca_contact.inactive=0 and acctyp_v2.licensed=1
AND (ca_contact.contact_type > 2305 AND ca_contact.contact_type != 2308)
AND (acctyp_v2.id != 2402 AND acctyp_v2.id != 10002)

Set Analyst to employee if inactive more that 90 days

***********

USE mdb
UPDATE usp_contact SET c_acctyp_id=2405 where contact_uuid IN
(SELECT ca_contact.contact_uuid AS contact
FROM ca_contact
LEFT OUTER JOIN usp_contact ON ca_contact.contact_uuid = usp_contact.contact_uuid
LEFT OUTER JOIN acctyp_v2 ON usp_contact.c_acctyp_id = acctyp_v2.id
WHERE ca_contact.contact_uuid NOT IN
(select contact from session_log where login_time > (DATEDIFF(s, '1970/01/01 00:00:00', GETDATE())-7776000))
AND ca_contact.inactive=0
AND acctyp_v2.licensed=1
AND (ca_contact.contact_type > 2305 AND ca_contact.contact_type != 2308)
AND (acctyp_v2.id != 2402 AND acctyp_v2.id != 10002)
)