LogDb

CREATE PROCEDURE [dbo].[BackupDatabase]
    @dbname VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    if  (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
        Begin
            DECLARE @path VARCHAR(256); -- path for backup files
            DECLARE @fileName VARCHAR(256); -- filename for backup
            Declare @currentDate varchar(50); -- suffisso data corrente per il file
            SET @path = 'D:\Backup\SqlServer\';
            set @currentDate=REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),CURRENT_TIMESTAMP, 120), '-', ''), ' ', ''), ':', '');
            SET @fileName = @path + @dbname + '_' + @currentDate + '.BAK';
            Print 'Backup file name ' + @fileName;
            BACKUP DATABASE @dbname TO DISK = @fileName;
        end
    else
        PRINT 'database ' + @dbname + ' not found';
END
GO

CREATE PROCEDURE [dbo].[Archivia]
    @applicazione varchar(50)
AS
BEGIN
    update [GiamminLogDb].[dbo].[Log]
    set Status = 'Archived'
    where Application= @applicazione and Status <> 'Archived'
END
GO

CREATE PROCEDURE [dbo].[ArchiviaVecchi]
    @giorni int = 10
AS
BEGIN
    update [GiamminLogDb].[dbo].[Log]
    set Status = 'Archived'
    where DATE < DATEADD(day,-@giorni,getdate()) and Status <> 'Archived'
END
GO

CREATE PROCEDURE [dbo].[DeleteUselessLogs]
AS
BEGIN
    SET NOCOUNT ON;

    delete from Log where logger = '/dummy.context';
END
GO

Create PROCEDURE [dbo].[GetLogCountForApplication]
AS
BEGIN
    SET NOCOUNT ON;
SELECT     Application, [Level], COUNT(Id) AS Count
FROM         dbo.[Log]
GROUP BY Application,  [Level]
order by Application,[level]
END
GO

CREATE PROCEDURE [dbo].[GetLogStats]

AS
BEGIN
set nocount on
SELECT     Application, [Level], Logger, COUNT(Id) AS Count
FROM         dbo.[Log]
WHERE     (Status = 'New')
GROUP BY Application, [Level], Logger
order by COUNT(Id) desc
END
GO

CREATE PROCEDURE [dbo].[GetNewErrors]
    @application varchar(50) = null
AS
BEGIN
    SET NOCOUNT ON;
SELECT     Id, Date, Status, Application, [Level], Logger, Message, Exception, Stacktrace
FROM         dbo.[Log]
WHERE     (Status = 'New') AND ([Level] <> 'Info') and(@application=application OR @application is null)
order by id desc
END
GO

CREATE PROCEDURE [dbo].[GetTopErrorLogger]
AS
BEGIN
    SET NOCOUNT ON;
SELECT     Application, [Level], Logger, COUNT(Id) AS Count
FROM         dbo.[Log]
WHERE     (Status = 'New') and Level <> 'info'
GROUP BY Application, [Level], Logger
order by COUNT(Id) desc
END
GO

CREATE PROCEDURE [dbo].[GetTopErrorMessage]
AS
BEGIN
SELECT     Application, [Level], Logger,SUBSTRING(message,0,100), COUNT(Id) AS Count
FROM         dbo.[Log]
WHERE     (Status = 'New') AND Level <> 'info'
GROUP BY Application, [Level], Logger,SUBSTRING(message,0,100)
  order by count desc
END
GO

CREATE TABLE [dbo].[Log](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Status] [nchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
    [Application] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    [Level] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    [Logger] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
    [Message] [varchar](max) COLLATE Latin1_General_CI_AS NOT NULL,
    [Exception] [varchar](max) COLLATE Latin1_General_CI_AS NULL,
    [Stacktrace] [varchar](max) COLLATE Latin1_General_CI_AS NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Log] ADD  CONSTRAINT [DF_Log_Status]  DEFAULT (N'New') FOR [Status]
GO