Clarity Service Management

Summary CA SDM  MS SQL Querys - TSQL 

Statistics
0 Favorited
0 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Comments

02-16-2017 01:05 PM

for sure! Nice job

Sharing is the foundation of many success 

02-16-2017 12:56 PM

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

02-16-2017 12:31 PM

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

02-16-2017 11:40 AM

Sound good!

/J

02-16-2017 11:39 AM

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

02-16-2017 10:48 AM

HI Jerome Mayer,

 

Thank you for colaboration and sharing! 

 

I built a document and linked here

 

Regards

02-16-2017 10:15 AM

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...

02-16-2017 09:42 AM

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

02-16-2017 09:30 AM

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

02-16-2017 09:25 AM

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)
)

Related Entries and Links

No Related Resource entered.