Skip to content

Audit

Przykłady zapytań

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
  select top 10 audit.audit_created_at,
    audit_old.value('(/row//imsprofil_stawka_auto/node())[1]', 'nvarchar(max)') stawka_auto,
  audit_old.value('(/row//imsprofil_stawka_post_influencer/node())[1]', 'nvarchar(max)') stawka_post_influencer_bylo,
  audit_new.value('(/row//imsprofil_stawka_post_influencer/node())[1]', 'nvarchar(max)') stawka_post_influencer_jest,
    audit_old.value('(/row//imsprofil_stawka_post/node())[1]', 'nvarchar(max)') stawka_post_bylo,
    audit_new.value('(/row//imsprofil_stawka_post/node())[1]', 'nvarchar(max)') stawka_post_jest,
    audit_old,
audit_new
  from  [app].[Audit] 
  where 
 audit_old.value('(/row//imsprofil_email/node())[1]', 'nvarchar(max)') ='veni83@wp.pl'
  order by audit_created_at desc

Archiwizacja audytów

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
DECLARE @counter INT;
SET @counter = 0;

WHILE @counter < 12
BEGIN
    DECLARE @year VARCHAR(4);
    DECLARE @week VARCHAR(2);
    DECLARE @sqlCommand VARCHAR(2000);
    DECLARE @sqlCommand2 VARCHAR(2000);
    DECLARE @logdb VARCHAR(100) = 'logs2'; --<<<<<<<<<<<<<<<<<<<<<<

    -- Begin transaction
    BEGIN TRANSACTION;

    BEGIN TRY
        -- Fetch year and week
        SELECT TOP 1 
            @year = YEAR([audit_created_at]),
            @week = DATEPART(MONTH, [audit_created_at]) 
        FROM app.[Audit]
        WHERE [audit_created_at] < GETUTCDATE() - 360
        ORDER BY [audit_created_at];

        PRINT @year;
        PRINT @week;

        -- Prepare SQL commands
        SET @sqlCommand = 
            'SELECT * INTO ' + @logdb + '.app.[audit' + @year + RIGHT('0' + @week, 2) + '] ' +
            'FROM app.[audit] WHERE YEAR([audit_created_at]) = ' + @year + 
            ' AND DATEPART(MONTH, [audit_created_at]) = ' + @week;

        SET @sqlCommand2 = 
            'DELETE FROM app.[audit] WHERE YEAR([audit_created_at]) = ' + @year + 
            ' AND DATEPART(MONTH, [audit_created_at]) = ' + @week;

        -- Execute SQL commands
        EXEC (@sqlCommand);
        EXEC (@sqlCommand2);

        -- Commit transaction
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Rollback transaction on error
        ROLLBACK TRANSACTION;
        PRINT 'An error occurred: ' + ERROR_MESSAGE();
    END CATCH;

    SET @counter = @counter + 1;
END;

Zmiana formy współpracy

Przykład zestawienie wyciągające zmiany formy współpracy z audytu

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
select
    cast( audit.audit_created_at as date) data,
    audit_old.value('(/row//imsprofil_social/node())[1]',   'varchar(max)') social,
    audit_old.value('(/row//imsprofil_account/node())[1]',  'varchar(max)') konto,
    audit_old.value('(/row//imsprofil_rozliczenia_rodzaj/node())[1]',   'varchar(max)') bylo,
    audit_new.value('(/row//imsprofil_rozliczenia_rodzaj/node())[1]','varchar(max)') jest,
    e.expense_code ,
    dt.documentAgreementType  ,
    e.expense_status,
    e.expense_agreement_signed_at 
from
    [app].[Audit] 
    left join [dbo].[expense] e on e.account_uuid = audit_remote_id
  and e.expense_type = 'umowa ramowa'
  and e.expense_status > -1
  and e.expense_valid_to is null
 left join [ims].[documentType] dt on dt.documentType = e.documentType
where
    audit_old.value('(/row//imsprofil_rozliczenia_rodzaj/node())[1]',
    'nvarchar(max)') != audit_new.value('(/row//imsprofil_rozliczenia_rodzaj/node())[1]',
    'nvarchar(max)')
order by
    audit_created_at desc

Analiza audytu

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
SELECT TOP (1000) 
    --[audit_uuid]
    convert(varchar,[audit_created_at],120) UTCDATE
    ,convert(varchar,[audit_created_by],120) [USER]
    --,[audit_old]
    --,[audit_new]
    ,'.'+isnull([audit_old].value('(/row//imsprofil_rozliczenia_konto_rachunek/node())[1]', 'nvarchar(max)'),'') 'NR KONTAPRZED' 
    ,'.'+isnull([audit_new].value('(/row//imsprofil_rozliczenia_konto_rachunek/node())[1]', 'nvarchar(max)'),'') 'NR KONTA PO'
    -- ,isnull([audit_old].value('(/row//imsprofil_rozliczenia_konto_bank/node())[1]', 'nvarchar(max)'),'') 'NAZWA PRZED' 
    -- ,     isnull([audit_new].value('(/row//imsprofil_rozliczenia_konto_bank/node())[1]', 'nvarchar(max)'),'') 'NAZWA PO'
FROM 
    [app].[Audit]
where 
    -- audit_remote_id  ='ECA839BD-89E8-EB11-AA8A-069674EE0F8A' --profil_uuid
    isnull([audit_new].value('(/row//imsprofil_rozliczenia_konto_rachunek/node())[1]', 'nvarchar(max)'),'') = '.53109000049987001000952807'
    --and  cast([audit_old] as nvarchar(max)) != cast([audit_new] as nvarchar(max))
    --and isnull([audit_old].value('(/row//imsprofil_rozliczenia_konto_rachunek/node())[1]', 'nvarchar(max)'),'')
    --!= isnull([audit_new].value('(/row//imsprofil_rozliczenia_konto_rachunek/node())[1]', 'nvarchar(max)'),'')
order by audit_id desc