14.06.2017
Adventnet Servicedesk
Nach dem Aufbau einer Testumgebung aus einem Backup der Produktion müssen Parameter angepasst werden.
-- Mailserver auf local setzen -- SELECT * FROM IncomingMailServer ; UPDATE IncomingMailServer SET HOSTNAME = '127.0.0.1' ; -- SELECT * FROM ApprovalDetails ; UPDATE ApprovalDetails SET EMAIL = 'ignaz.goebel@hauck-aufhaeuser.com' ; -- SELECT * FROM Drafts ; UPDATE Drafts SET TOADDRESS = 'ignaz.goebel@hauck-aufhaeuser.com', CC = 'ignaz.goebel@hauck-aufhaeuser.com' ; -- SELECT * FROM WorkOrderStates ; UPDATE WorkOrderStates SET EMAIL_CC = 'ignaz.goebel@hauck-aufhaeuser.com' ; -- SELECT * FROM AaaContactInfo ; UPDATE AaaContactInfo SET EMAILID = 'ignaz.goebel@hauck-aufhaeuser.com' ; -- SELECT * FROM Notification_Recipients ; UPDATE Notification_Recipients SET RECIPIENT_EMAIL = 'ignaz.goebel@hauck-aufhaeuser.com' ; -- SELECT * FROM OutgoingMailAliases UPDATE OutgoingMailAliases SET FROMEMAILID = 'ignaz.goebel@hauck-aufhaeuser.com', SENDERNAME = 'TEST HelpDesk' ; -- SELECT * FROM OutgoingMailServer UPDATE OutgoingMailServer SET HOSTNAME = '127.0.0.1' -- SELECT * FROM Queue_Email ; TRUNCATE TABLE Queue_Email ; -- SELECT * FROM GlobalConfig ; UPDATE GlobalConfig SET PARAMVALUE = 'vmlwi-hdtt01.ads.lan' WHERE PARAMETER = 'SERVERNAME' AND PARAMVALUE = 'VMAPPS01' AND GLOBALCONFIGID = 8406 ; UPDATE GlobalConfig SET PARAMVALUE = 'vmlwi-hdtt01.ads.lan:444' WHERE PARAMETER = 'WEB_URL' AND PARAMVALUE = 'serviceportal.ads.lan:444' AND GLOBALCONFIGID = 332 ; UPDATE GlobalConfig SET PARAMVALUE = 'STOPPED' WHERE PARAMETER = 'THREAD_STATE' AND GLOBALCONFIGID = 324 AND CATEGORY = 'MailFetching' ;
Erstellen einer View um Bedarfe darzustellen
DROP VIEW [dbo].[sdp_bedarfe] GO CREATE VIEW [dbo].[sdp_bedarfe] as SELECT TOP 100 PERCENT WO.WORKORDERID AS ANFORDERUNGSID , AAU.FIRST_NAME AS ANFORDERER , AAL.NAME , PD.PRIORITYNAME AS PRIORITAET , DPT.DEPTNAME AS ABTEILUNG , WO.DUEBYTIME , DATEADD(MINUTE,CONVERT(bigint, WO.DUEBYTIME/60000), '1970-01-01') AS FAELLIGKEITSDATUM , DATEADD(MINUTE,CONVERT(bigint, WO.CREATEDTIME/60000), '1970-01-01') AS ERSTELLUNGSZEIT , DATEADD(MINUTE,CONVERT(bigint, WOS.LAST_TECH_UPDATE/60000), '1970-01-01') AS LETZTER_AKTUALISIERUNGSZEITPUNKT , LVD.LEVELID , LVD.LEVELNAME AS EBENE , QD.QUEUENAME AS GRUPPE , WO.TITLE AS BETREFF , STD.STATUSNAME AS STATUS , WO.DESCRIPTION AS BESCHREIBUNG FROM dbo.WorkOrder WO LEFT JOIN WorkOrderStates WOS ON WO.WORKORDERID = WOS.WORKORDERID LEFT JOIN LevelDefinition LVD ON WOS.LEVELID = LVD.LEVELID LEFT JOIN StatusDefinition STD ON WOS.STATUSID = STD.STATUSID LEFT JOIN WorkOrder_Queue WOQ ON WO.WORKORDERID = WOQ.WORKORDERID LEFT JOIN SDUser SDU ON WO.REQUESTERID = SDU.USERID LEFT JOIN AaaUser AAU ON SDU.USERID = AAU.USER_ID LEFT JOIN AaaLogin AAL ON WO.REQUESTERID = AAL.USER_ID LEFT JOIN DepartmentDefinition DPT ON WO.DEPTID = DPT.DEPTID LEFT JOIN QueueDefinition QD ON WOQ.QUEUEID = QD.QUEUEID LEFT JOIN PriorityDefinition PD ON WOS.PRIORITYID = PD.PRIORITYID WHERE LVD.LEVELID IN ( 1201 -- 'B1 - Bedarf' , 1801 -- 'B2 - Service Request' , 1501 -- 'C1 - Projekt' ) --AND WO.WORKORDERID IN (102253) ORDER BY WO.WORKORDERID GO