Query to get Asset contract and its details

Query to get Asset contract and its details

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 ADManager Plus?

        New to ADSelfService Plus?

        Start your free trial

          Resources

              • Related Articles

              • Contract and Service Plans details - Query Report

                PGSQL & MSSQL: select ac.contractno "Contract Number", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type", sp.timeperiod "Bill Cycle",fixedmonthlycharges "Fixed Base Charge",sp.fixedmonthlyunits ...

              • Query to get the asset details , mac address , last scanned, status alone with user details

                Query to get the asset details , mac address , last scanned, status alone with user details SELECT Max(workstation.workstationname) "Asset Name",        Max(workstation.servicetag)      "Service Tag",        Max(resource.assettag)      "Asset Tag",   ...

              • Query to get Requesters details for each account

                PGSQL & MSSQL: Execute the query under Reports->New Query Report and export it to the desired format. select au.user_id "User Id", au.first_name "First Name", au.last_name "Last Name", sdu.isvipuser "VIP User", sdu.employeeid "Employee ...

              • Query to show asset monitor details

                SELECT si.workstationname "Asset Name", si.workstationname  "Asset Name", mi.monitortype "Monitor Type", mi.serialnumber "Monitor Serial Number" from systeminfo si LEFT JOIN monitorinfo mi ON si.workstationid=mi.workstationid ORDER BY 1

              • Query to show contract billing details with requests

                PGSQL: ​ select ad.org_name "Account", wo.workorderid "Request ID", wo.workorderid "Request ID", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name", sp.timeperiod "Bill Cycle", cast((sum(ct.TIMESPENT)/1000 * interval '1 second') ...

                Related Products