
create table [dbo].[PROCINFO] (
    [ComputerName] [nvarchar](128) null,
    [ProcessId] [bigint] not null ,
    [ProcessName] [nvarchar](1024) ,
    [ParentProcessId] [bigint] ,
    [Domain] [nvarchar](64) ,
    [UserName] [nvarchar](64) ,
    [PolicyName] [nvarchar](128) ,
    [PolicySetTime] [bigint] ,
    [ExecutablePath] [nvarchar](256) ,
    [CreationTime] [bigint] ,
    [EndTime] [bigint] ,
    [SessionId] [bigint] ,
    [ProcessMatchingCriteria] [nvarchar](128) ,
    [CommandLine] [nvarchar](1024) ,
    [gid] [bigint] NOT NULL identity(1, 1) primary key,
)
GO

CREATE INDEX IDX_CREATIONTIME on [PROCINFO] (CREATIONTIME)
GO

CREATE INDEX IDX_ENDTIME on [PROCINFO] (ENDTIME)
GO

CREATE INDEX IDX_COMPUTERNAME on [PROCINFO] (COMPUTERNAME)
GO

CREATE INDEX IDX_POLICYNAME on [PROCINFO] (POLICYNAME)
GO


create table [dbo].[DYNAINFO] (
    [Timestamp]  [bigint] ,
    [ThreadCount]  [bigint] ,
    [TotalCPUTime]  [bigint] ,
    [ElapsedTime]  [bigint] ,
    [KernelModeTime]  [bigint] ,
    [UserModeTime]  [bigint] ,
    [PageFileUsage]  [bigint] ,
    [PeakPageFileUsage]  [bigint] ,
    [PageFaultCount]  [bigint] ,
    [VirtualSize]  [bigint] ,
    [PeakVirtualSize]  [bigint] ,
    [WorkingSetSize]  [bigint] ,
    [PeakWorkingSetSize]  [bigint] ,
    [PrivatePageCount]  [bigint] ,
    [QuotaPagedPoolUsage]  [bigint] ,
    [QuotaPeakPagedPoolUsage]  [bigint] ,
    [QuotaNonPagedPoolUsage]  [bigint] ,
    [QuotaPeakNonPagedPoolUsage]  [bigint] ,
    [ReadOperationCount]  [bigint] ,
    [ReadTransferCount]  [bigint] ,
    [WriteOperationCount]  [bigint] ,
    [WriteTransferCount]  [bigint] ,
    [OtherOperationCount]  [bigint] ,
    [OtherTransferCount]  [bigint] ,
    [procGid]  [bigint] not null, -- foreign key to procinfo.gid
    [gid] int NOT NULL identity(1, 1) PRIMARY KEY,
)
go

alter table [DYNAINFO] add constraint fkey_procgid
  foreign key (procGid)
  references PROCINFO (gid)
go

CREATE INDEX IDX_TIMESTAMP on [DYNAINFO] (TIMESTAMP)
GO


create view [dbo].[AccountingInfoView] as SELECT
    [ComputerName] as 'ComputerName',
    [ProcessId] as 'ProcessId',
    [CreationTime] as 'CreationTime',
    [PolicySetTime]    as 'PolicySetTime',
    Max([TimeStamp]) as 'TimeStamp',
    Max([ProcessName]) as 'ProcessName',
    [ProcessMatchingCriteria] as 'ProcessMatchingCriteria',
    [PolicyName] as 'PolicyName',
    Max([ExecutablePath]) as 'ExecutablePath',
    Max([UserName]) as 'UserName',
    Max([Domain]) as 'Domain',
    Max([CommandLine]) as 'CommandLine',
    (Max([ElapsedTime]) - Min ([ElapsedTime])) as 'ElapsedTime',
    (Max([KernelModeTime]) - Min([KernelModeTime])) as 'KernelModeTime',
    (Max([UserModeTime]) - Min([UserModeTime])) as 'UserModeTime',
    (Max([TotalCPUTime]) - Min([TotalCPUTime])) as 'TotalCPUTime',
    Avg([ThreadCount]) as 'ThreadCount',
    [SessionId] as 'SessionId',
    Max([PeakVirtualSize]) as 'PeakVirtualSize',
    Avg([VirtualSize]) as 'VirtualSize',
    (Max([PageFaultCount]) - Min([PageFaultCount])) as 'PageFaultCount',
    Avg([PrivatePageCount]) as 'PrivatePageCount',
    Max([PeakWorkingSetSize]) as 'PeakWorkingSetSize',
    Avg([WorkingSetSize]) as 'WorkingSetSize',
    Avg([PageFileUsage]) as 'PageFileUsage',
    Max([PeakPageFileUsage]) as 'PeakPageFileUsage',
    (Max([ReadOperationCount]) - Min([ReadOperationCount])) as 'ReadOperationCount',
    (Max([ReadTransferCount])  - Min([ReadTransferCount])) as 'ReadTransferCount',
    (Max([WriteOperationCount])- Min([WriteOperationCount])) as 'WriteOperationCount',
    (Max([WriteTransferCount]) - Min([WriteTransferCount])) as 'WriteTransferCount',
    (Max([OtherOperationCount])- Min([OtherOperationCount])) as 'OtherOperationCount',
    (Max([OtherTransferCount]) - Min([OtherTransferCount])) as 'OtherTransferCount',
    Avg([QuotaNonPagedPoolUsage]) as 'QuotaNonPagedPoolUsage',
    Avg([QuotaPagedPoolUsage]) as 'QuotaPagedPoolUsage',
    Max([QuotaPeakNonPagedPoolUsage]) as 'QuotaPeakNonPagedPoolUsage',
    Max([QuotaPeakPagedPoolUsage]) as 'QuotaPeakPagedPoolUsage',
    Max([EndTime]) as 'EndTime'
from
    PROCINFO P, DYNAINFO D
WHERE
    P.gid = D.procgid AND
    [CreationTime] is not NULL
GROUP BY [ComputerName], [CreationTime], [ProcessId], [SessionId], [PolicyName], [PolicySetTime], [ProcessMatchingCriteria]
Go


--- The procedures start from here
create procedure [dbo].[getIdentity] as
begin
    select @@identity
end
go

create function [dbo].[getIdentityF]() returns bigint as
begin
    return @@identity
end
go

create procedure [dbo].[getTotalCount] (@computerName nvarchar(128)) as
begin
    if (@computerName = N'' or @computerName is NULL)
        select count(1) from [dbo].[AccountingInfoView]
    else
        select count(1) from [dbo].[AccountingInfoView]
          where [ComputerName] = @computerName
end
go

create procedure [dbo].[getLiveProcCount](@computerName nvarchar(128)) as
begin
    if (@computerName = N'' or @computerName is NULL)
        select count(1) from [dbo].[PROCINFO]
          where [ENDTIME] = 0
    else
        select count(1) from [dbo].[PROCINFO]
          where [ComputerName] = @computerName
            and [ENDTIME] = 0
end
go

create procedure [dbo].[InsertStatRow](@computerName nvarchar(128),
                    @processId bigint,
                    @processName nvarchar(1024),
                    @parentProcessId bigint,
                    @domain nvarchar(64),
                    @userName nvarchar(64),
                    @policyName nvarchar(128),
                    @policySetTime bigint,
                    @executablePath nvarchar(256),
                    @creationTime bigint,
                    @endTime bigint,
                    @sessionId bigint,
                    @pmc nvarchar(128),
                    @cmdLine nvarchar(1024),
                    @gid bigint out) as
begin
    set @gid = 0

    if ( @domain is null or @domain = N'' )
        set @domain = N'N/A'

    if ( @userName is null or @userName = N'' )
        set @userName = N'N/A'

    insert into [dbo].[PROCINFO] (
                        [ComputerName],
                        [ProcessId],
                        [ProcessName],
                        [Domain],
                        [UserName],
                        [PolicyName],
                        [ProcessMatchingCriteria],
                        [CommandLine],
                        [ParentProcessId],
                        [PolicySetTime],
                        [ExecutablePath],
                        [CreationTime],
                        [SessionId],
                        [EndTime]
                    )
        values(
                @computerName,
                @processId,
                @processName,
                @domain,
                @userName,
                @policyName,
                @pmc,
                @cmdLine,
                @parentProcessId,
                @policySetTime,
                @executablePath,
                @creationTime,
                @sessionId,
                @endTime
                )

    if (@@error = 0)
        set @gid = @@identity
    else
        set @gid = 0 - @@error

    select @gid
end
go

create procedure [dbo].[InsertDynaRow](
                    @Timestamp bigint,
                    @ThreadCount bigint,
                    @TotalCPUTime bigint,
                    @ElapsedTime bigint,
                    @KernelModeTime bigint,
                    @UserModeTime bigint,
                    @PageFileUsage bigint,
                    @PeakPageFileUsage bigint,
                    @PageFaultCount bigint,
                    @VirtualSize bigint,
                    @PeakVirtualSize bigint,
                    @WorkingSetSize bigint,
                    @PeakWorkingSetSize bigint,
                    @PrivatePageCount bigint,
                    @QuotaPagedPoolUsage bigint,
                    @QuotaPeakPagedPoolUsage bigint,
                    @QuotaNonPagedPoolUsage bigint,
                    @QuotaPeakNonPagedPoolUsage bigint,
                    @ReadOperationCount bigint,
                    @ReadTransferCount bigint,
                    @WriteOperationCount bigint,
                    @WriteTransferCount bigint,
                    @OtherOperationCount bigint,
                    @OtherTransferCount bigint,
                    @procgid bigint,
                    @gid bigint out) as
begin
    set @gid = 0
    insert into [dbo].[DYNAINFO] (
                        [PROCGID],
                        [Timestamp],
                        [ThreadCount],
                        [TotalCPUTime],
                        [ElapsedTime],
                        [KernelModeTime],
                        [UserModeTime],
                        [PageFileUsage],
                        [PeakPageFileUsage],
                        [PageFaultCount],
                        [VirtualSize],
                        [PeakVirtualSize],
                        [WorkingSetSize],
                        [PeakWorkingSetSize],
                        [PrivatePageCount],
                        [QuotaPagedPoolUsage],
                        [QuotaPeakPagedPoolUsage],
                        [QuotaNonPagedPoolUsage],
                        [QuotaPeakNonPagedPoolUsage],
                        [ReadOperationCount],
                        [ReadTransferCount],
                        [WriteOperationCount],
                        [WriteTransferCount],
                        [OtherOperationCount],
                        [OtherTransferCount]
                    )
        values(     @procgid,
                    @Timestamp,
                    @ThreadCount,
                    @TotalCPUTime,
                    @ElapsedTime,
                    @KernelModeTime,
                    @UserModeTime,
                    @PageFileUsage,
                    @PeakPageFileUsage,
                    @PageFaultCount,
                    @VirtualSize,
                    @PeakVirtualSize,
                    @WorkingSetSize,
                    @PeakWorkingSetSize,
                    @PrivatePageCount,
                    @QuotaPagedPoolUsage,
                    @QuotaPeakPagedPoolUsage,
                    @QuotaNonPagedPoolUsage,
                    @QuotaPeakNonPagedPoolUsage,
                    @ReadOperationCount,
                    @ReadTransferCount,
                    @WriteOperationCount,
                    @WriteTransferCount,
                    @OtherOperationCount,
                    @OtherTransferCount
                )

    -- only if the insertion was successful read the gid value
    -- 0 would mean the insertion failed
    if @@error = 0
        set @gid = @@identity
    else
        set @gid = 0 - @@error

    select @gid
end
go

create procedure [dbo].[checkAndInsertDynaRow] (@computerName nvarchar(128),
                    @processId bigint,
                    @processName nvarchar(1024),
                    @parentProcessId bigint,
                    @domain nvarchar(64),
                    @userName nvarchar(64),
                    @policyName nvarchar(128),
                    @policySetTime bigint,
                    @executablePath nvarchar(256),
                    @creationTime bigint,
                    @endTime bigint,
                    @sessionId bigint,
                    @pmc nvarchar(128),
                    @cmdLine nvarchar(1024),
                    @Timestamp bigint,
                    @ThreadCount bigint,
                    @TotalCPUTime bigint,
                    @ElapsedTime bigint,
                    @KernelModeTime bigint,
                    @UserModeTime bigint,
                    @PageFileUsage bigint,
                    @PeakPageFileUsage bigint,
                    @PageFaultCount bigint,
                    @VirtualSize bigint,
                    @PeakVirtualSize bigint,
                    @WorkingSetSize bigint,
                    @PeakWorkingSetSize bigint,
                    @PrivatePageCount bigint,
                    @QuotaPagedPoolUsage bigint,
                    @QuotaPeakPagedPoolUsage bigint,
                    @QuotaNonPagedPoolUsage bigint,
                    @QuotaPeakNonPagedPoolUsage bigint,
                    @ReadOperationCount bigint,
                    @ReadTransferCount bigint,
                    @WriteOperationCount bigint,
                    @WriteTransferCount bigint,
                    @OtherOperationCount bigint,
                    @OtherTransferCount bigint,
                    @procgid bigint out) as
begin
    select @procgid = 0

    select @procgid = [GID]
        from [dbo].[PROCINFO]
        where [COMPUTERNAME] = @computerName
            and [PROCESSID] = @processId

    if ( @procgid is NULL or @procgid = 0 or @@ROWCOUNT = 0 )
    begin

        if ( @domain is null or @domain = N'' )
            set @domain = N'N/A'

        if ( @userName is null or @userName = N'' )
            set @userName = N'N/A'

        insert into [dbo].[PROCINFO] (
                        [ComputerName],
                        [ProcessId],
                        [ProcessName],
                        [Domain],
                        [UserName],
                        [PolicyName],
                        [ProcessMatchingCriteria],
                        [CommandLine],
                        [ParentProcessId],
                        [PolicySetTime],
                        [ExecutablePath],
                        [CreationTime],
                        [SessionId],
                        [EndTime]
                        )
            values(@computerName,
                @processId,
                @processName,
                @domain,
                @userName,
                @policyName,
                @pmc,
                @cmdLine,
                @parentProcessId,
                @policySetTime,
                @executablePath,
                @creationTime,
                @sessionId,
                @endTime
                )

        if @@error = 0
            set @procgid = @@identity
        else
            set @procgid = 0 - @@error
    end

    if ( @procgid > 0 )
    begin
        insert into [dbo].[DYNAINFO] (
                        [PROCGID],
                        [Timestamp],
                        [ThreadCount],
                        [TotalCPUTime],
                        [ElapsedTime],
                        [KernelModeTime],
                        [UserModeTime],
                        [PageFileUsage],
                        [PeakPageFileUsage],
                        [PageFaultCount],
                        [VirtualSize],
                        [PeakVirtualSize],
                        [WorkingSetSize],
                        [PeakWorkingSetSize],
                        [PrivatePageCount],
                        [QuotaPagedPoolUsage],
                        [QuotaPeakPagedPoolUsage],
                        [QuotaNonPagedPoolUsage],
                        [QuotaPeakNonPagedPoolUsage],
                        [ReadOperationCount],
                        [ReadTransferCount],
                        [WriteOperationCount],
                        [WriteTransferCount],
                        [OtherOperationCount],
                        [OtherTransferCount]
                    )
            values( @procgid,
                    @Timestamp,
                    @ThreadCount,
                    @TotalCPUTime,
                    @ElapsedTime,
                    @KernelModeTime,
                    @UserModeTime,
                    @PageFileUsage,
                    @PeakPageFileUsage,
                    @PageFaultCount,
                    @VirtualSize,
                    @PeakVirtualSize,
                    @WorkingSetSize,
                    @PeakWorkingSetSize,
                    @PrivatePageCount,
                    @QuotaPagedPoolUsage,
                    @QuotaPeakPagedPoolUsage,
                    @QuotaNonPagedPoolUsage,
                    @QuotaPeakNonPagedPoolUsage,
                    @ReadOperationCount,
                    @ReadTransferCount,
                    @WriteOperationCount,
                    @WriteTransferCount,
                    @OtherOperationCount,
                    @OtherTransferCount
                )

            if @@error <> 0
                set @procgid = 0 - @@error
    end

    -- select the procgid for the caller
    -- returns 0 if insertion failed
    select @procgid
end
go


-- pass processid = 0 if you want to do for all records of this computer
create procedure [dbo].[updateEndTime](@processid bigint, @computerName nvarchar(128), @endTime bigint)
as
begin
    if ( @processid = 0 )
        update [dbo].[procinfo] set [ENDTIME] = @endTime
            where [COMPUTERNAME] = @computerName
                and [ENDTIME] = 0
    else
        update [dbo].[procinfo] set [ENDTIME] = @endTime
            where [PROCESSID] = @processid
                and [COMPUTERNAME] = @computerName
                and [ENDTIME] = 0
end
go

create procedure [dbo].[updateEndTime2] (@gid bigint, @endTime bigint)
as
begin
    update [dbo].[PROCINFO] set [ENDTIME] = @endTime
        where [GID] = @gid
            and [ENDTIME] = 0
end
go


-- We select only those columns for which the IsArchiving flag is set on
-- Note that parentprocessid is not archivable
-- Note that we have added 2 extra columns in this version - endtime and procinfo.gid
create procedure [dbo].[getRawDataForArchiving] (@startTime bigint, @endTime bigint, @computername nvarchar(1024))
as
begin
    if (@startTime is not NULL and @endTime is not NULL and @startTime <> 0 and @endTime <> 0 )
    begin
        declare @command nvarchar(max)

        if (@computername is not NULL)
        begin
            set @command = N'select B.[TIMESTAMP], A.[ComputerName], A.[ProcessId], A.[ProcessName], A.[Domain],
                A.[UserName], A.[PolicyName], A.[PolicySetTime], A.[ProcessMatchingCriteria],
                A.[CreationTime], A.[EndTime], A.[ExecutablePath], A.[CommandLine], A.[SessionId],
                B.[ThreadCount], B.[TotalCPUTime], B.[ElapsedTime], B.[KernelModeTime], B.[UserModeTime],
                B.[PageFileUsage], B.[PeakPageFileUsage], B.[PageFaultCount], B.[VirtualSize], B.[PeakVirtualSize],
                B.[WorkingSetSize], B.[PeakWorkingSetSize], B.[PrivatePageCount],
                B.[QuotaPagedPoolUsage], B.[QuotaPeakPagedPoolUsage],
                B.[QuotaNonPagedPoolUsage], B.[QuotaPeakNonPagedPoolUsage],
                B.[ReadOperationCount], B.[ReadTransferCount], B.[WriteOperationCount],
                B.[WriteTransferCount], B.[OtherOperationCount], B.[OtherTransferCount], A.[gid]
            from [dbo].[PROCINFO] A, [dbo].[DYNAINFO] B
            where A.[gid] = B.[procgid]
              and B.[TIMESTAMP] between ' + cast(@startTime as nvarchar(20)) + N' and ' + cast(@endTime as nvarchar(20))
              + N' and A.[ComputerName] in (N''' + REPLACE(@computername,N'''',N'''''') + N''')'

            exec sp_executesql @command        
        end
        else
        begin
            select B.[TIMESTAMP], A.[ComputerName], A.[ProcessId], A.[ProcessName], A.[Domain],
                A.[UserName], A.[PolicyName], A.[PolicySetTime], A.[ProcessMatchingCriteria],
                A.[CreationTime], A.[EndTime], A.[ExecutablePath], A.[CommandLine], A.[SessionId],
                B.[ThreadCount], B.[TotalCPUTime], B.[ElapsedTime], B.[KernelModeTime], B.[UserModeTime],
                B.[PageFileUsage], B.[PeakPageFileUsage], B.[PageFaultCount], B.[VirtualSize], B.[PeakVirtualSize],
                B.[WorkingSetSize], B.[PeakWorkingSetSize], B.[PrivatePageCount],
                B.[QuotaPagedPoolUsage], B.[QuotaPeakPagedPoolUsage],
                B.[QuotaNonPagedPoolUsage], B.[QuotaPeakNonPagedPoolUsage],
                B.[ReadOperationCount], B.[ReadTransferCount], B.[WriteOperationCount],
                B.[WriteTransferCount], B.[OtherOperationCount], B.[OtherTransferCount], A.[gid]
            from [dbo].[PROCINFO] A, [dbo].[DYNAINFO] B
            where A.[gid] = B.[procgid]
              and B.[TIMESTAMP] between @startTime and @endTime
        end
    end
    else
    begin
        if (@computername is not NULL)
        begin
            set @command = N'select B.[TIMESTAMP], A.[ComputerName], A.[ProcessId], A.[ProcessName], A.[Domain],
                A.[UserName], A.[PolicyName], A.[PolicySetTime], A.[ProcessMatchingCriteria],
                A.[CreationTime], A.[EndTime], A.[ExecutablePath], A.[CommandLine], A.[SessionId],
                B.[ThreadCount], B.[TotalCPUTime], B.[ElapsedTime], B.[KernelModeTime], B.[UserModeTime],
                B.[PageFileUsage], B.[PeakPageFileUsage], B.[PageFaultCount], B.[VirtualSize], B.[PeakVirtualSize],
                B.[WorkingSetSize], B.[PeakWorkingSetSize], B.[PrivatePageCount],
                B.[QuotaPagedPoolUsage], B.[QuotaPeakPagedPoolUsage],
                B.[QuotaNonPagedPoolUsage], B.[QuotaPeakNonPagedPoolUsage],
                B.[ReadOperationCount], B.[ReadTransferCount], B.[WriteOperationCount],
                B.[WriteTransferCount], B.[OtherOperationCount], B.[OtherTransferCount], A.[gid]
            from [dbo].[PROCINFO] A, [dbo].[DYNAINFO] B
            where A.[gid] = B.[procgid]
              and A.[ComputerName] in (N''' + REPLACE(@computername,N'''',N'''''') + N''')'

            exec sp_executesql @command    
        end
        else
        begin
            select B.[TIMESTAMP], A.[ComputerName], A.[ProcessId], A.[ProcessName], A.[Domain],
                A.[UserName], A.[PolicyName], A.[PolicySetTime], A.[ProcessMatchingCriteria],
                A.[CreationTime], A.[EndTime], A.[ExecutablePath], A.[CommandLine], A.[SessionId],
                B.[ThreadCount], B.[TotalCPUTime], B.[ElapsedTime], B.[KernelModeTime], B.[UserModeTime],
                B.[PageFileUsage], B.[PeakPageFileUsage], B.[PageFaultCount], B.[VirtualSize], B.[PeakVirtualSize],
                B.[WorkingSetSize], B.[PeakWorkingSetSize], B.[PrivatePageCount],
                B.[QuotaPagedPoolUsage], B.[QuotaPeakPagedPoolUsage],
                B.[QuotaNonPagedPoolUsage], B.[QuotaPeakNonPagedPoolUsage],
                B.[ReadOperationCount], B.[ReadTransferCount], B.[WriteOperationCount],
                B.[WriteTransferCount], B.[OtherOperationCount], B.[OtherTransferCount], A.[gid]
            from [dbo].[PROCINFO] A, [dbo].[DYNAINFO] B
            where A.[gid] = B.[procgid]
        end
    end
end
go

create procedure [dbo].[getRunningProcessMap] (@computername nvarchar(64))
as
begin
    if ( @computerName = N'' or @computerName is NULL )
        select [GID], [PROCESSID], [COMPUTERNAME]
            from [dbo].[PROCINFO]
            where [ENDTIME] = 0 or [ENDTIME] = null
   else
        select [GID], [PROCESSID], [COMPUTERNAME]
            from [dbo].[PROCINFO]
            where ( [ENDTIME] = 0 or [ENDTIME] = null )
                and [COMPUTERNAME ] = @computerName
end
go

create procedure [dbo].[genericSelectQuery] (@selectCols nvarchar(2000), @where nvarchar(2000),
                    @orderby nvarchar(512), @groupby nvarchar(512), @having nvarchar(2000) )
as
begin
    -- this needs dynamic execution
    declare @query nvarchar(max)

    set @selectCols = replace (@selectCols, CHAR(13), ',')

    set @query = 'select ' + @selectCols + ' from [dbo].[AccountingInfoView] '

    if ( @where is not null and @where <> '' )
    begin
        -- we need to escape the single quotes
        set @where = replace (@where, '''', '''''')
        set @where = replace (@where, CHAR(10), '''')
        set @query = @query + ' where ' + @where
    end

    if ( @groupby is not null and @groupby <> '' )
    begin
        set @groupby = replace (@groupby, CHAR(13), ',')
        set @query = @query + ' group by ' + @groupby
    end

    if ( @having is not null and @having <> '' )
    begin
        -- we need to escape the single quotes
        set @having = replace (@having, '''', '''''')
        set @having = replace (@having, CHAR(10), '''')
        set @query = @query + ' having ' + @having
    end

    if ( @orderby is not null and @orderby <> '' )
    begin
        set @orderby = replace (@orderby, CHAR(13), ',')
        set @query = @query + ' order by ' + @orderby
    end

    exec sp_executesql @query
end
go

create procedure [dbo].[deleteDataOfMachine](@startTime bigint, @endTime bigint, @computername nvarchar(64))
as
begin
    if (@startTime is not NULL and @endTime is not NULL and @startTime >= 0 and @endTime <> 0 and @computername is not NULL)
    begin
        delete from [dbo].[DYNAINFO]
          where TIMESTAMP between @startTime and @endTime
        and PROCGID IN (select GID from [dbo].[PROCINFO] where [ComputerName] = @computername)

        delete from [dbo].[PROCINFO]
          where GID not in (select distinct PROCGID from [dbo].DYNAINFO)
            and ENDTIME <> 0
            and ENDTIME is not NULL
            and [ComputerName] = @computername
    end
end
go

create procedure [dbo].[deleteData](@startTime bigint, @endTime bigint)
as
begin
    if (@startTime is not NULL and @endTime is not NULL and @startTime >= 0 and @endTime <> 0 )
    begin
        delete from [dbo].[DYNAINFO]
          where TIMESTAMP between @startTime and @endTime

        delete from [dbo].[PROCINFO]
          where GID not in (select distinct PROCGID from [dbo].DYNAINFO)
            and ENDTIME <> 0
            and ENDTIME is not NULL
    end
end
go


create procedure [dbo].[AddMachineAccount] (@domain nvarchar(64), @machine nvarchar(960) ) as
begin
    if (CHARINDEX(@machine, N'$') = 0 )
    begin
        set @machine = @machine + N'$'
    end
    
    declare @fullname nvarchar(1028)
    set @fullname = @domain + N'\' + @machine 

    exec sp_grantlogin @fullname
    exec sp_defaultdb @fullname, N'wsrm'

    if ( not exists (select 1 from sysusers where name = @machine ) )
    begin
        exec sp_grantdbaccess @fullname, @machine
    end
    
    set @machine = replace(@machine,N'''',N'''''') 

    EXEC(N'GRANT  EXECUTE  ON [dbo].[AddMachineAccount] TO [N' + @machine  + N']')
    EXEC(N'GRANT  EXECUTE  ON [dbo].[CheckAndInsertDynaRow] TO [N' + @machine + N']')
    EXEC(N'GRANT  EXECUTE  ON [dbo].[InsertDynaRow] TO [N' + @machine + N']')
    EXEC(N'GRANT  EXECUTE  ON [dbo].[InsertStatRow] TO [N' + @machine + N']')
    EXEC(N'GRANT  EXECUTE  ON [dbo].[deleteData] TO [N' + @machine + N']')
    EXEC(N'GRANT  EXECUTE  ON [dbo].[deleteDataOfMachine] TO [N' + @machine + N']')
    EXEC(N'GRANT  EXECUTE  ON [dbo].[genericSelectQuery] TO [N' + @machine + N']')
    EXEC(N'GRANT  EXECUTE  ON [dbo].[getLiveProcCount] TO [N' + @machine + N']')
    EXEC(N'GRANT  EXECUTE  ON [dbo].[getRawDataForArchiving] TO [N' + @machine + N']')
    EXEC(N'GRANT  EXECUTE  ON [dbo].[getRunningProcessMap] TO [N' + @machine + N']')
    EXEC(N'GRANT  EXECUTE  ON [dbo].[getTotalCount] TO [N' + @machine + N']')
    EXEC(N'GRANT  EXECUTE  ON [dbo].[updateEndTime] TO [N' + @machine + N']')
    EXEC(N'GRANT  EXECUTE  ON [dbo].[updateEndTime2] TO [N' + @machine + N']')

    EXEC(N'GRANT  SELECT  ON [dbo].[AccountingInfoView] TO [N' + @machine + N']')
end
go
