· 4 years ago · Aug 16, 2021, 08:50 AM
1USE [SDAR_AutoRouting]
2
3DECLARE @Start DATETIME = GETDATE()
4DECLARE @From DATETIME
5DECLARE @To DATETIME
6DECLARE @Batch INT
7SET @Batch = IsNull(@Batch, (SELECT IsNull(Max(Batch), 0) + 1 FROM SDAR_Incidents))
8SET @To = IsNull(@To, GetDate())
9--SET @From = DateAdd(Month, -1, DateAdd(Month, DateDiff(Month, 0, @To), 0))
10
11-- *******************************************************************************************
12-- ___________________________________________________________________________________________
13-- CHANGE THE NUMBER HERE TO AMOUNT OF DAYS IN TICKETS HISTORY YOU WANT TO GO BACK AND EXTRACT
14SET @From = DateAdd(DAY, -90, getdate())
15-- ___________________________________________________________________________________________
16-- *******************************************************************************************
17
18
19PRINT 'Filling SDAR_Incident'
20PRINT Concat('~ From: ', @From)
21PRINT Concat('~ To: ', @To)
22PRINT Concat('~ Batch: ', @Batch)
23
24
25-- ## attribute list table
26DECLARE @infoTable TABLE (Partition_Key nvarchar(80), Partition_Label nvarchar(20))
27INSERT INTO @infoTable VALUES
28 ('incident@short_description', 'ShortDescription')
29 ,('incident@work_notes', 'InternalWorkNotes')
30 ,('incident@comments', 'ExternalWorkNotes')
31 ,('incident@u_service', 'Service')
32 ,('incident@cmdb_ci', 'ConfigurationItem')
33 ,('incident@contact_type', 'ContactType')
34 ,('incident@description', 'Description')
35 ,('incident@caller_id', 'Caller')
36 ,('incident@impact', 'Impact')
37 ,('incident@incident_state', 'State')
38
39
40-- ## temp table - used multiple times in script
41IF OBJECT_ID('tempdb..#infoTable') IS NOT NULL DROP TABLE #infoTable
42
43SELECT *
44INTO #infoTable
45FROM @infoTable
46--> 0:00
47
48
49-- ## USER table
50IF OBJECT_ID('tempdb..#USER') IS NOT NULL DROP TABLE #USER
51
52SELECT
53 CAST(DM.[GSN_User_ID] AS nvarchar(32)) AS [GSN_User_ID],
54 DM.[Login],
55 DM.[High_Level_CREST_Code],
56 DM.[GSN_Home_SD_Name],
57 WGR.[Searchcode] AS [GSN_Home_SD_Searchcode],
58 DM.[Company_Search_Code],
59 DM.[Country_Code],
60 DM.[Location_Name],
61 DM.[Reporting_From],
62 DM.[Reporting_To]
63INTO #USER
64FROM DM.[dbo].[Dim_User] DM WITH (NOLOCK)
65LEFT JOIN [DM].[dbo].[Dim_Group] WGR WITH (NOLOCK)
66 ON WGR.[GSN_Group_ID] = DM.[GSN_Home_SD_ID]
67WHERE DM.Reporting_To >= @From AND WGR.Current_Row_Indicator = 1
68--> 0:11 (1.195.532)
69
70
71-- ## INC - Incident restriction managing table
72IF OBJECT_ID('tempdb..#INC') IS NOT NULL DROP TABLE #INC
73
74SELECT CAST(Number as nvarchar(32)) as Number,Created_Date,Created_Time,GUID,Assigned_Group,Incident_Type_List,Customer_Company,Impacted_Business_Units,Impacted_Area_List,External_System,Reopened_Count
75INTO #INC
76FROM [DM].[dbo].[Fact_Incident] WITH (NOLOCK)
77WHERE Incident_Status_List IN (
78 SELECT OID
79 FROM [DM].[dbo].[Dim_Choice_List_English] ITS WITH (NOLOCK)
80 WHERE [Source_Table] = 'incident'
81 AND [Source_Column] = 'incident_state'
82 AND [Display_Value] IN ('Resolved','Informed','Closed')
83)
84--> 1:21 (15.413.151)
85
86
87-- ## Performance indexes
88CREATE CLUSTERED INDEX CI_INC ON #INC (GUID) WITH FILLFACTOR = 100
89--> 0:30
90
91CREATE CLUSTERED INDEX CI_USER ON #USER ([GSN_User_ID],[Reporting_From],[Reporting_To]) WITH FILLFACTOR = 100
92--> 0:03
93
94
95-- ## audit table - only needed audit records
96IF OBJECT_ID('tempdb..#audit') IS NOT NULL DROP TABLE #audit
97
98SELECT
99 AUD.Updated_Item_ID
100 ,AUD.Created
101 ,AUD.New_Value
102 ,AUD.Old_Value
103 ,AUD.Created AS [Updated]
104 ,AUD.Created_By AS [UpdatedBy]
105INTO #audit
106FROM [DM].[dbo].[Fact_GSN_Audit] AUD WITH (NOLOCK)
107JOIN [SDAR_AutoRouting].[dbo].[TicketRoutingGSDGroups_new] GSD WITH (NOLOCK)
108 ON GSD.GroupID = AUD.[New_Value]
109
110WHERE [Partition_Key] = N'incident@assignment_group'
111 AND [Created] >= @From
112 AND [Created] < @To
113--> 0:19 (208.604)
114
115
116-- ## Performance index
117CREATE NONCLUSTERED INDEX NCI_audit ON [dbo].[#audit] ([Updated_Item_ID],[Created])
118--> 0:01
119
120
121-- ## infos table - only needed partitions/attributes from GSN audit table (depending on audit records and time period)
122IF OBJECT_ID('tempdb..#infos') IS NOT NULL DROP TABLE #infos
123
124SELECT
125 s.Updated_Item_ID
126 ,s.Partition_Key
127 ,s.Created
128 ,s.New_Value
129 ,s.Created_By
130INTO #infos
131FROM [DM].[dbo].[Fact_GSN_Audit] s WITH (NOLOCK)
132JOIN #infoTable it
133 ON it.Partition_Key = s.Partition_Key
134JOIN (
135 -- ## restrictions: required records at a given time period
136 SELECT
137 Updated_Item_ID
138 ,Max(Created) AS Created
139 FROM #audit
140 GROUP BY Updated_Item_ID
141) a
142 ON s.Updated_Item_ID = a.Updated_Item_ID
143 AND s.Created <= a.Created
144--> 7:00 (1.368.559)
145
146
147-- ## performance index
148CREATE NONCLUSTERED INDEX NCI_infos ON [dbo].[#infos] ([Updated_Item_ID],[Partition_Key],[Created]) INCLUDE ([New_Value],[Created_By])
149--> 0:12
150
151
152-- ## info table - available partition keys attributes (with values) for each change of audit assignment group
153IF OBJECT_ID('tempdb..#info') IS NOT NULL DROP TABLE #info
154
155SELECT
156 atr.Updated_Item_ID
157 ,atr.Created
158 ,atr.Partition_Key
159 ,inf.New_Value
160 ,atr.AttributeCreated
161 ,inf.Created_By
162INTO #info
163FROM (
164 -- ## time slices - available partition keys for each change of audit assignment group
165 SELECT
166 a.Updated_Item_ID
167 ,a.Created
168 ,i.Partition_Key
169 ,Max(i.Created) AS AttributeCreated
170 FROM #audit a
171
172 LEFT JOIN #infos i
173 ON i.Updated_Item_ID = a.Updated_Item_ID
174 AND i.Created <= a.Created
175
176 GROUP BY
177 a.Updated_Item_ID
178 ,a.Created
179 ,i.Partition_Key
180) atr
181LEFT JOIN #infos inf
182 ON inf.Updated_Item_ID = atr.Updated_Item_ID
183 AND inf.Partition_Key = atr.Partition_Key
184 AND inf.Created = atr.AttributeCreated
185--> 0:27 (1.451.234)
186
187
188/*
189-- ## testing query for audit attribute duplicity (in specific time period)
190SELECT Updated_Item_ID, Created, Partition_Key, count(distinct New_Value) as attributeCount
191FROM #info
192--WHERE NOT (New_Value LIKE 'E2E%')-- OR Created_By = 'system')
193GROUP BY Updated_Item_ID, Created, Partition_Key
194HAVING Count(DISTINCT New_Value) > 1
195ORDER BY attributeCount DESC
196--> 0:14 (2.907)
197*/
198
199
200-- ## audit atribute table - prepared for final JOIN statements
201IF OBJECT_ID('tempdb..#audit_attr') IS NOT NULL DROP TABLE #audit_attr
202
203SELECT
204 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ID,
205 @Batch AS Batch,
206 Convert(nvarchar(4000), SHO.[New_Value]) AS ShortDescription,
207 Convert(nvarchar(4000), DSC.[New_Value]) AS Description,
208 Convert(nvarchar(4000), IWN.[New_Value]) AS InternalWorkNotes,
209 Convert(nvarchar(4000), EWN.[New_Value]) AS ExternalWorkNotes,
210 AUD.[New_Value],
211 AUD.[Old_Value],
212 Convert(varchar(10), IMP.[New_Value]) AS Impact,
213 Convert(varchar(32), CON.[New_Value]) AS ContactType,
214 Convert(datetime2, AUD.[Created]) AS Created,
215 AUD.Updated AS Updated,
216 AUD.UpdatedBy AS UpdatedBy,
217 Convert(varchar(32), AUD.[Updated_Item_ID]) AS Updated_Item_ID,
218 Convert(varchar(32), CAL.[New_Value]) AS Caller,
219 Convert(varchar(32), SRV.[New_Value]) AS Service,
220 Convert(varchar(32), CI.[New_Value]) AS ConfigurationItem
221INTO #audit_attr
222FROM #audit AUD
223
224-- ## Short Description
225LEFT JOIN #info SHO
226 ON SHO.Updated_Item_ID = AUD.Updated_Item_ID
227 AND SHO.Partition_Key = 'incident@short_description'
228 AND SHO.Created = AUD.Created
229
230-- ## Internal WorkNotes
231LEFT JOIN #info IWN
232 ON IWN.Updated_Item_ID = AUD.Updated_Item_ID
233 AND IWN.Created = AUD.Created
234 AND IWN.Partition_Key = 'incident@work_notes'
235
236-- ## ExternalWorkNotes
237LEFT JOIN #info EWN
238 ON EWN.Updated_Item_ID = AUD.Updated_Item_ID
239 AND EWN.Created = AUD.Created
240 AND EWN.Partition_Key = 'incident@comments'
241
242-- ## Service
243LEFT JOIN #info SRV
244 ON SRV.Updated_Item_ID = AUD.Updated_Item_ID
245 AND SRV.Created = AUD.Created
246 AND SRV.Partition_Key = 'incident@u_service'
247
248-- ## cmdb_ci
249LEFT JOIN #info CI
250 ON CI.Updated_Item_ID = AUD.Updated_Item_ID
251 AND CI.Created = AUD.Created
252 AND CI.Partition_Key = 'incident@cmdb_ci'
253
254-- ## contact_type
255LEFT JOIN #info CON
256 ON CON.Updated_Item_ID = AUD.Updated_Item_ID
257 AND CON.Created = AUD.Created
258 AND CON.Partition_Key = 'incident@contact_type'
259
260-- ## description
261LEFT JOIN #info DSC
262 ON DSC.Updated_Item_ID = AUD.Updated_Item_ID
263 AND DSC.Created = AUD.Created
264 AND DSC.Partition_Key = 'incident@description'
265
266-- ## caller_id
267LEFT JOIN #info CAL
268 ON CAL.Updated_Item_ID = AUD.Updated_Item_ID
269 AND CAL.Created = AUD.Created
270 AND CAL.Partition_Key = 'incident@caller_id'
271
272-- ## impact
273LEFT JOIN #info IMP
274 ON IMP.Updated_Item_ID = AUD.Updated_Item_ID
275 AND IMP.Created = AUD.Created
276 AND IMP.Partition_Key = 'incident@impact'
277--> 0:50 (211.623)
278
279
280-- ## final result table
281--TRUNCATE TABLE SDAR_Incidents --we want to get data into local SQL mgmt. window
282
283--INSERT INTO SDAR_Incidents --we want to get data into local SQL mgmt. window
284
285/*
286DECLARE @Start DATETIME = GETDATE()
287DECLARE @From DATETIME
288DECLARE @To DATETIME
289DECLARE @Batch INT
290SET @Batch = IsNull(@Batch, (SELECT IsNull(Max(Batch), 0) + 1 FROM SDAR_Incidents))
291SET @To = IsNull(@To, GetDate())
292SET @From = DateAdd(Month, -6, DateAdd(Month, DateDiff(Month, 0, @To), 0))
293
294*/
295
296PRINT 'Filling SDAR_Incident'
297PRINT Concat('~ From: ', @From)
298PRINT Concat('~ To: ', @To)
299PRINT Concat('~ Batch: ', @Batch)
300
301
302USE [tempdb]
303IF (EXISTS (SELECT *
304 FROM INFORMATION_SCHEMA.TABLES
305 WHERE TABLE_NAME = '##SDAR'))
306BEGIN
307 DROP TABLE ##SDAR
308END
309
310
311SELECT
312 AUD.ID,
313 AUD.Batch,
314 INC.Number,
315 REPLACE(AUD.ShortDescription,',',' ') as ShortDescription,
316 REPLACE(AUD.Description,',',' ') as Description,
317 REPLACE(AUD.InternalWorkNotes,',',' ') as InternalWorkNotes,
318 REPLACE(AUD.ExternalWorkNotes,',',' ') as ExternalWorkNotes,
319 WGR.Searchcode AS Workgroup,
320 PRE.Searchcode AS PreviousWorkgroup,
321 SER.Name AS Service,
322 IMS.Display_Value AS Impact,
323 CNS.Display_Value AS ContactType,
324 USR.Login AS CallerLDAPID,
325 USR.High_Level_CREST_Code AS CallerBusinessUnit,
326 USR.GSN_Home_SD_Searchcode AS CallerHomeSD,
327 USR.Company_Search_Code AS CallerOrganization,
328 USR.Country_Code AS CallerCountry,
329 Location_Name AS CallerLocation,
330 ITS.Display_Value AS IncidentType,
331 CMDB.Searchcode AS ConfigurationItem,
332 --CUSTOMER_COMPANY.Searchcode AS CustomerCompany,
333 INC.Impacted_Business_Units AS ImpactedBusinessUnit,
334 IAS.Display_Value AS ImpactedArea,
335 --INC.External_System AS ExternalSystem,
336 INC.Reopened_Count AS ReopenCount,
337 CONVERT(datetime,SUBSTRING(LTRIM(STR(INC.Created_Date)),1,4)+'-'+SUBSTRING(LTRIM(STR(INC.Created_Date)),5,2)+'-'+SUBSTRING(LTRIM(STR(INC.Created_Date)),7,2)+' '+CONVERT(varchar,INC.Created_Time),120) AS _Created,
338 AUD.Updated AS _Updated,
339 AUD.UpdatedBy AS _UpdatedBy,
340 ASW.Searchcode AS ResolvingWG,
341 ROW_NUMBER() OVER (PARTITION BY INC.Number ORDER BY AUD.Updated) as RowNumber
342INTO ##SDAR
343FROM #audit_attr AUD
344
345-- ## Incident - secondary joins (Caller, Groups, etc.)
346JOIN #INC INC WITH (NOLOCK)
347 ON INC.GUID = AUD.[Updated_Item_ID]
348
349-- ## Workgroup
350LEFT JOIN [DM].[dbo].[Dim_Group] WGR WITH (NOLOCK)
351 ON WGR.[GSN_Group_ID] = AUD.[New_Value] -- implicit conversion !!! varchar -> nvarchar !!!
352 AND AUD.[Created] BETWEEN WGR.Reporting_From AND WGR.Reporting_To
353
354-- ## Previous Workgroup
355LEFT JOIN [DM].[dbo].[Dim_Group] PRE WITH (NOLOCK)
356 ON PRE.[GSN_Group_ID] = AUD.[Old_Value] -- implicit conversion !!! varchar -> nvarchar !!!
357 AND AUD.[Created] BETWEEN PRE.Reporting_From AND PRE.Reporting_To
358
359-- ## Resolvign Workgroup
360LEFT JOIN [DM].[dbo].[Dim_Group] ASW WITH (NOLOCK)
361 ON ASW.[OID] = INC.[Assigned_Group]
362
363-- ## Caller
364LEFT JOIN #USER USR WITH (NOLOCK)
365 ON USR.[GSN_User_ID] = AUD.[Caller] -- implicit conversion !!! varchar -> nvarchar !!!
366 AND AUD.[Created] BETWEEN USR.Reporting_From AND USR.Reporting_To
367
368-- ## Contract Type
369LEFT JOIN [DM].[dbo].[Dim_Choice_List_English] CNS WITH (NOLOCK)
370 ON CNS.[Value] = AUD.[ContactType]
371 AND AUD.[Created] BETWEEN CNS.Reporting_From AND CNS.Reporting_To
372 AND CNS.[Source_Table] = 'incident'
373 AND CNS.[Source_Column] = 'contact_type'
374
375-- ## Indcident Type
376LEFT JOIN [DM].[dbo].[Dim_Choice_List_English] ITS WITH (NOLOCK)
377 ON ITS.[OID] = INC.Incident_Type_List
378 AND ITS.[Source_Table] = 'incident'
379 AND ITS.[Source_Column] = 'u_incident_type'
380
381-- ## Impact
382LEFT JOIN [DM].[dbo].[Dim_Choice_List_English] IMS WITH (NOLOCK)
383 ON IMS.[Value] = AUD.[Impact]
384 AND AUD.[Created] BETWEEN IMS.Reporting_From AND IMS.Reporting_To
385 AND IMS.[Source_Table] = 'task'
386 AND IMS.[Source_Column] = 'impact'
387
388-- ## Impacted Area
389LEFT JOIN [DM].[dbo].[Dim_Choice_List_English] IAS WITH (NOLOCK)
390 ON IAS.[OID] = INC.Impacted_Area_List
391 AND IAS.[Source_Table] = 'incident'
392 AND IAS.[Source_Column] = 'u_impacted_area'
393
394-- ## Service
395LEFT JOIN [DM].[dbo].[Dim_Service] SER WITH (NOLOCK)
396 ON SER.[GSN_Service_ID] = AUD.[Service] -- implicit conversion !!! varchar -> nvarchar !!!
397 AND AUD.[Created] BETWEEN SER.Reporting_From AND SER.Reporting_To
398
399-- ## Configuration Item
400LEFT JOIN [DM].[dbo].[Dim_CMDB] CMDB WITH (NOLOCK)
401 ON CMDB.[GSN_Configuration_Item_ID] = AUD.[ConfigurationItem] -- implicit conversion !!! varchar -> nvarchar !!!
402 AND AUD.[Created] BETWEEN CMDB.Reporting_From AND CMDB.Reporting_To
403
404-- ## Customer Company
405LEFT JOIN [DM].[dbo].[Dim_Company] CUSTOMER_COMPANY WITH (NOLOCK)
406 ON CUSTOMER_COMPANY.OID = INC.Customer_Company -- implicit conversion !!! varchar -> nvarchar !!!
407--> 0:57 (211.623)
408
409
410PRINT Concat('* Finished in ', Convert(CHAR(8), GetDate() - @Start, 108))
411
412select *
413from ##SDAR
414where RowNumber = 1
415and NOT
416(
417 (
418 IncidentType in ('Complaint','Security Incident')
419 )
420 OR ISNULL(Service,'empty') like ('%APEM-DCE%')
421 OR
422 (
423 IncidentType = 'Complaint'
424 AND lower(ShortDescription) like '%user satisfaction 5-star rating follow-up%'
425 )
426-- OR
427-- CallerCountry = 'DE'
428)