Query to get Asset contract and its details (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306)
SELECT mcdt.Contractid "URL Contract ID",
mcdt.customcontractid "Custom contractid in details page",
mcdt.CONTRACTNAME "Contract Name",
aao.NAME "Maintenance Vendor Name",
LONGTODATE(mcdt.FROMDATE) "From Date",
LONGTODATE(mcdt.TODATE) "To Date",
mcdt.TOTALPRICE "Total Price",
aao.NAME "Maintenance Vendor Name",
cst.STATUSNAME "Contract Status",
LONGTODATE(mcdt.CREATEDDATE) "Created Time",
cbyaau.FIRST_NAME "Contract Requester",
cadf.udf_char1 "Addition 1",
cadf.udf_long1 "Addition numeric",
ad.ORG_NAME AS "Account Name",
sda.Attachmentname "Attachmentname"
FROM MaintenanceContract mcdt
LEFT JOIN VendorDefinition vdn ON mcdt.MAINTENANCEVENDOR=vdn.VENDORID
LEFT JOIN SDOrganization aao ON vdn.VENDORID=aao.ORG_ID
LEFT JOIN SDOrgContactInfo ON aao.ORG_ID=SDOrgContactInfo.ORG_ID
LEFT JOIN SDOrgContactUser ON aao.ORG_ID=SDOrgContactUser.ORG_ID
LEFT JOIN AaaUser ON SDOrgContactUser.USER_ID=AaaUser.USER_ID
LEFT JOIN AaaContactInfo ON SDOrgContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT JOIN aaapostaladdress ON aao.ORG_ID=aaapostaladdress.POSTALADDR_ID
LEFT JOIN SDUser cby ON mcdt.CREATEDBY=cby.USERID
LEFT JOIN AaaUser cbyaau ON cby.USERID=cbyaau.USER_ID
LEFT JOIN ContractStatus cst ON mcdt.STATUSID=cst.STATUSID
LEFT JOIN ContractDetails ON mcdt.CONTRACTID=ContractDetails.CONTRACTID
LEFT JOIN Contract_Fields cadf ON mcdt.CONTRACTID=cadf.CONTRACTID
LEFT JOIN Resources ON ContractDetails.RESOURCEID=Resources.RESOURCEID
LEFT JOIN contractcategory ON mcdt.categoryid=contractcategory.categoryid
LEFT JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
LEFT JOIN contractattachment ca ON mcdt.contractid=ca.contractid
LEFT JOIN sdeskattachment sda ON ca.attachmentid=sda.attachmentid
LEFT JOIN contractnotificationsettings cns ON mcdt.contractid=cns.contractid
LEFT JOIN contractnotificationmailids cnm ON mcdt.contractid=cnm.contractid
left JOIN EscalateToMediator ON mcdt.ESCALATETOID=EscalateToMediator.ESCALATETOID
LEFT JOIN EscalateToN ON EscalateToMediator.ESCALATETOID=EscalateToN.ESCALATETOID
LEFT JOIN AaaUser aaa1 ON EscalateToN.USERID=aaa1.USER_ID
INNER JOIN ContractAccountmapping camp ON mcdt.contractid=camp.contractid
INNER JOIN AccountDefinition ad ON camp.accountid=ad.org_id
INNER JOIN ContractAccountMapping ON mcdt.CONTRACTID=ContractAccountMapping.CONTRACTID
ORDER BY 1
Note - cadf.udf_char1 "Addition 1", and cadf.udf_long1 "Addition numeric", are the syntax to get contract additional fields. You need to pass your contract additional fields UDF values to it. change udf_char1 and udf_long1 as per your table value. Addition 1 and Addition numeric are the naming convention , you can change it according to your need.
You can get the UDF values using the query -> select * from Contract_Fields
New to ADSelfService Plus?
Related Articles
Contract and Service Plans details - Query Report (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: select ac.contractno "Contract Number", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type", sp.timeperiod "Bill ...Query to get the login failed attempt details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports tab > New Query Report, use the below query to get the login failed attempts details, select principal "User Name", LONGTODATE(timestamp), HOSTNAME, OPERATIONNAME from AuditRecord ar LEFT ...Contract Details
SELECT mcdt.Contractid "Contract ID", mcdt.CONTRACTNAME "Contract Name", mcdt.comments "Description", contractcategory.Categoryname "Contract Type", LONGTODATE(mcdt.CREATEDDATE) "Created Time", LONGTODATE(mcdt.FROMDATE) "From Date", ...How to auto-create request for contract expiry.
This post describes the use of a python script to auto-create requests for contract expiry using Custom Schedules. Use case: If the contract going to expire in next 7 days, we can create a ticket with the details for the contract in ServiceDesk Plus, ...Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...