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