34 lines
1.4 KiB
Transact-SQL
34 lines
1.4 KiB
Transact-SQL
-- select * from dbo.logN where Level = 'error'
|
|
--[dbo].[GetLogs]
|
|
--[dbo].[GetLogs] '2016-04-13 09:36:18.887'
|
|
CREATE procedure [dbo].[GetLogs]
|
|
@LogsSince DateTime = NULL
|
|
AS
|
|
BEGIN
|
|
print 'a'
|
|
;with raw_data as
|
|
(
|
|
select logged, ltrim(rtrim(replace(replace(replace(replace(message,'constructing', ''),'of',''), ' does not finished due to the error', ''),'finished',''))) par ,
|
|
case when message like '%finished' then 'finished' when message like '%not finished%' then 'error' else 'started' end as process_status, message
|
|
from [dbo].[LogN]
|
|
where
|
|
message like '%constructing%' and
|
|
((@LogsSince IS NOT NULL and logged >= @LogsSince) or (@LogsSince IS NULL))
|
|
), r1 as
|
|
(
|
|
select *, substring(par, 0, charindex(':', par)) as head_1, substring(par, charindex(':', par)+1,1000) as tail_1
|
|
from raw_data
|
|
), r2 as
|
|
( select [message],logged, process_status, head_1, substring(tail_1, 0, charindex(':', tail_1)) as head_2, substring(tail_1, charindex(':', tail_1)+1,1000) as tail_2
|
|
from r1
|
|
), r3 as
|
|
(
|
|
select [message],logged, process_status, head_1, head_2, substring(tail_2, 0, charindex(':', tail_2)) as head_3, substring(tail_2, charindex(':', tail_2)+1,1000) as tail_3
|
|
from r2
|
|
), r4 as
|
|
(
|
|
select [message],logged, process_status, head_1 as CodiceFiscale, head_2 as Rete, head_3 as TipoContratto, substring(tail_3, 2, len(tail_3)) as ReportType
|
|
from r3
|
|
)
|
|
select * from r4 where process_status ='error' order by logged desc
|
|
END |