Skip to content

Instantly share code, notes, and snippets.

@byaussy

byaussy/audit_log.sql

Last active August 5, 2020 18:51
Show Gist options
  • Save byaussy/852ba39cb071439e4e994a8637537eb6 to your computer and use it in GitHub Desktop.
Save byaussy/852ba39cb071439e4e994a8637537eb6 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
audit_log.sql
--client audits
with client_audits as
(
select id as audit_log_id
, log_datetime_created
, user_name as modified_by_user_name
, response_body::jsonb -> 'id' as client_mdm_id
, response_body::jsonb -> 'code' as client_code
, response_body::jsonb -> 'name' as name
, response_body::jsonb -> 'isActive' as is_active
, response_body::jsonb -> 'hmxLegacyId' as hmx_legacy_id
, response_body::jsonb -> 'localizedName' as localized_name
, response_body::jsonb -> 'type' as type
, response_body::jsonb -> 'aorClientCode' as aor_client_code
, response_body::jsonb -> 'fiscalStartDate' as fiscal_start_date
, response_body::jsonb -> 'fiscalStartMonthDay' as fiscal_start_month_day
, response_body::jsonb -> 'creditStatusFlag' as credit_status_flag
, response_body::jsonb -> 'taxExemptStatusFlag' as tax_exempt_status_flag
, response_body::jsonb -> 'userDefinedFields' as user_defined_fields
, response_body::jsonb -> 'customer' -> 'id' as customer_mdm_id
, response_body::jsonb -> 'customer' -> 'agencyAccountingOffices' ->0 -> 'agencyAccountingOffice' -> 'agency' -> 'id' as agency_mdm_id
, response_body::jsonb -> 'customer' -> 'agencyAccountingOffices' ->0 -> 'agencyAccountingOffice' -> 'agency' -> 'hmxLegacyId' as agency_hmx_legacy_id
, response_body::jsonb -> 'customer' -> 'agencyAccountingOffices' ->0 -> 'agencyAccountingOffice' -> 'agency' -> 'code' as agency_code
, response_body::jsonb -> 'customer' -> 'agencyAccountingOffices' ->0 -> 'agencyAccountingOffice' -> 'agency' -> 'name' as agency_name
, response_body::jsonb -> 'masterClient' as master_client_id
, (response_body::jsonb -> 'clientGroups')::text as client_groups
, (response_body::jsonb -> 'clientCategories')::text as client_categories
, (select jsonb_agg(t -> 'name' order by t -> 'name') from jsonb_array_elements(response_body::jsonb -> 'mediaTypes') as x(t)) as media_types
, (response_body::jsonb -> 'addresses')::text as addresses
, (response_body::jsonb -> 'contacts')::text as contacts
from audit_api_log
where api_name in ('createClient', 'updateClient')
and status = 200
and holding_company_id = 1001
)
select *
from client_audits
order by agency_code, client_code, log_datetime_created desc;
--Client audit difference checks
with client_audits as
(
select id as audit_log_id
, log_datetime_created
, user_name as modified_by_user_name
, response_body::jsonb -> 'id' as client_mdm_id
, response_body::jsonb -> 'code' as client_code
, response_body::jsonb -> 'name' as name
, response_body::jsonb -> 'isActive' as is_active
, response_body::jsonb -> 'hmxLegacyId' as hmx_legacy_id
, response_body::jsonb -> 'localizedName' as localized_name
, response_body::jsonb -> 'type' as type
, response_body::jsonb -> 'aorClientCode' as aor_client_code
, response_body::jsonb -> 'fiscalStartDate' as fiscal_start_date
, response_body::jsonb -> 'fiscalStartMonthDay' as fiscal_start_month_day
, response_body::jsonb -> 'creditStatusFlag' as credit_status_flag
, response_body::jsonb -> 'taxExemptStatusFlag' as tax_exempt_status_flag
, response_body::jsonb -> 'userDefinedFields' as user_defined_fields
, response_body::jsonb -> 'customer' -> 'id' as customer_mdm_id
, response_body::jsonb -> 'customer' -> 'agencyAccountingOffices' ->0 -> 'agencyAccountingOffice' -> 'agency' -> 'id' as agency_mdm_id
, response_body::jsonb -> 'customer' -> 'agencyAccountingOffices' ->0 -> 'agencyAccountingOffice' -> 'agency' -> 'hmxLegacyId' as agency_hmx_legacy_id
, response_body::jsonb -> 'customer' -> 'agencyAccountingOffices' ->0 -> 'agencyAccountingOffice' -> 'agency' -> 'code' as agency_code
, response_body::jsonb -> 'customer' -> 'agencyAccountingOffices' ->0 -> 'agencyAccountingOffice' -> 'agency' -> 'name' as agency_name
, response_body::jsonb -> 'masterClient' as master_client_id
, (response_body::jsonb -> 'clientGroups')::text as client_groups
, (response_body::jsonb -> 'clientCategories')::text as client_categories
, (select jsonb_agg(t -> 'name' order by t -> 'name') from jsonb_array_elements(response_body::jsonb -> 'mediaTypes') as x(t)) as media_types
, (response_body::jsonb -> 'addresses')::text as addresses
, (response_body::jsonb -> 'contacts')::text as contacts
from audit_api_log
where api_name in ('createClient', 'updateClient')
and status = 200
and holding_company_id = 1001
and (response_body::jsonb -> 'code') = '"CEO"'
and (response_body::jsonb -> 'customer' -> 'agencyAccountingOffices' ->0 -> 'agencyAccountingOffice' -> 'agency' -> 'code')::text = '"US03"'
)
select audit_log_id,
modified_by_user_name,
log_datetime_created,
client_mdm_id,
client_mdm_id::text <> (lead(client_mdm_id) over (order by audit_log_id desc))::text as client_mdm_id_modified,
client_code,
client_code::text <> (lead(client_code) over (order by audit_log_id desc))::text as client_code_modified,
name,
name::text <> (lead(name) over (order by audit_log_id desc))::text as name_modified,
is_active,
is_active::text <> (lead(is_active) over (order by audit_log_id desc))::text as is_active_modified,
hmx_legacy_id,
hmx_legacy_id::text <> (lead(hmx_legacy_id) over (order by audit_log_id desc))::text as hmx_legacy_id_modified,
localized_name,
localized_name::text <> (lead(localized_name) over (order by audit_log_id desc))::text as localized_name_modified,
type,
type::text <> (lead(type) over (order by audit_log_id desc))::text as type_modified,
aor_client_code,
aor_client_code::text <> (lead(aor_client_code) over (order by audit_log_id desc))::text as aor_client_code_modified,
fiscal_start_date,
fiscal_start_date::text <> (lead(fiscal_start_date) over (order by audit_log_id desc))::text as fiscal_start_date_modified,
fiscal_start_month_day,
fiscal_start_month_day::text <> (lead(fiscal_start_month_day) over (order by audit_log_id desc))::text as fiscal_start_month_day_modified,
credit_status_flag,
credit_status_flag::text <> (lead(credit_status_flag) over (order by audit_log_id desc))::text as credit_status_flag_modified,
tax_exempt_status_flag,
tax_exempt_status_flag::text <> (lead(tax_exempt_status_flag) over (order by audit_log_id desc))::text as tax_exempt_status_flag_modified,
user_defined_fields,
user_defined_fields::text <> (lead(user_defined_fields) over (order by audit_log_id desc))::text as user_defined_fields_modified,
customer_mdm_id,
customer_mdm_id::text <> (lead(customer_mdm_id) over (order by audit_log_id desc))::text as customer_mdm_id_modified,
agency_mdm_id,
agency_mdm_id::text <> (lead(agency_mdm_id) over (order by audit_log_id desc))::text as agency_mdm_id_modified,
agency_hmx_legacy_id,
agency_hmx_legacy_id::text <> (lead(agency_hmx_legacy_id) over (order by audit_log_id desc))::text as agency_hmx_legacy_id_modified,
agency_code,
agency_code::text <> (lead(agency_code) over (order by audit_log_id desc))::text as agency_code_modified,
agency_name,
agency_name::text <> (lead(agency_name) over (order by audit_log_id desc))::text as agency_name_modified,
master_client_id,
master_client_id::text <> (lead(master_client_id) over (order by audit_log_id desc))::text as master_client_id_modified,
client_groups,
client_groups::text <> (lead(client_groups) over (order by audit_log_id desc))::text as client_groups_modified,
client_categories,
client_categories::text <> (lead(client_categories) over (order by audit_log_id desc))::text as client_categories_modified,
media_types,
media_types::text <> (lead(media_types) over (order by audit_log_id desc))::text as media_types_modified,
addresses,
addresses::text <> (lead(addresses) over (order by audit_log_id desc))::text as addresses_modified,
contacts,
contacts::text <> (lead(contacts) over (order by audit_log_id desc))::text as contacts_modified
from client_audits
order by agency_code, client_code, log_datetime_created desc;
--customer audits
with customer_audits as
(
select id as audit_log_id
, log_datetime_created
, user_name as modified_by_user_name
, response_body::jsonb -> 'id' as customer_mdm_id
, response_body::jsonb -> 'addresses' as addresses
, response_body::jsonb -> 'contacts' as contacts
, response_body::jsonb -> 'code' as customer_code
, response_body::jsonb -> 'name' as name
, response_body::jsonb -> 'isActive' as is_active
, response_body::jsonb -> 'localizedName' as localized_name
, response_body::jsonb -> 'logo' as logo
, response_body::jsonb -> 'erpCustomerId' as erp_customer_id
, response_body::jsonb -> 'customerGroup' as customer_group
, response_body::jsonb -> 'serviceSegmentCode' as service_segment_code
, response_body::jsonb -> 'creditInsuranceCode' as credit_insurance_code
, response_body::jsonb -> 'paymentTerm' as payment_term
, response_body::jsonb -> 'paymentType' as payment_type
, response_body::jsonb -> 'currencyCode' as currency_code
, response_body::jsonb -> 'userDefinedFields' as user_defined_fields
, response_body::jsonb -> 'agencyAccountingOffices' -> 0 -> 'agencyAccountingOffice' -> 'agency' -> 'id' as agency_mdm_id
, response_body::jsonb -> 'agencyAccountingOffices' -> 0 -> 'agencyAccountingOffice' -> 'agency' -> 'hmxLegacyId' as agency_hmx_legacy_id
, response_body::jsonb -> 'agencyAccountingOffices' -> 0 -> 'agencyAccountingOffice' -> 'agency' -> 'code' as agency_code
, response_body::jsonb -> 'agencyAccountingOffices' -> 0 -> 'agencyAccountingOffice' -> 'agency' -> 'name' as agency_name
from audit_api_log
where api_name in ('createCustomer', 'updateCustomer')
and status = 200
and holding_company_id = 1001
)
select * from customer_audits
order by agency_code, customer_code, audit_log_id desc;
--customer audit difference checks
with customer_audits as
(
select id as audit_log_id
, log_datetime_created
, user_name as modified_by_user_name
, response_body::jsonb -> 'id' as customer_mdm_id
, response_body::jsonb -> 'addresses' as addresses
, response_body::jsonb -> 'contacts' as contacts
, response_body::jsonb -> 'code' as customer_code
, response_body::jsonb -> 'name' as name
, response_body::jsonb -> 'isActive' as is_active
, response_body::jsonb -> 'localizedName' as localized_name
, response_body::jsonb -> 'logo' as logo
, response_body::jsonb -> 'erpCustomerId' as erp_customer_id
, response_body::jsonb -> 'customerGroup' as customer_group
, response_body::jsonb -> 'serviceSegmentCode' as service_segment_code
, response_body::jsonb -> 'creditInsuranceCode' as credit_insurance_code
, response_body::jsonb -> 'paymentTerm' as payment_term
, response_body::jsonb -> 'paymentType' as payment_type
, response_body::jsonb -> 'currencyCode' as currency_code
, response_body::jsonb -> 'userDefinedFields' as user_defined_fields
, response_body::jsonb -> 'agencyAccountingOffices' -> 0 -> 'agencyAccountingOffice' -> 'agency' -> 'id' as agency_mdm_id
, response_body::jsonb -> 'agencyAccountingOffices' -> 0 -> 'agencyAccountingOffice' -> 'agency' -> 'hmxLegacyId' as agency_hmx_legacy_id
, response_body::jsonb -> 'agencyAccountingOffices' -> 0 -> 'agencyAccountingOffice' -> 'agency' -> 'code' as agency_code
, response_body::jsonb -> 'agencyAccountingOffices' -> 0 -> 'agencyAccountingOffice' -> 'agency' -> 'name' as agency_name
from audit_api_log
where api_name in ('createCustomer', 'updateCustomer')
and status = 200
and holding_company_id = 1001
and response_body::jsonb -> 'code' = '"C34156"'
and response_body::jsonb -> 'agencyAccountingOffices' -> 0 -> 'agencyAccountingOffice' -> 'agency' -> 'code' = '"US03"'
)
select audit_log_id
, log_datetime_created
, modified_by_user_name
, customer_mdm_id
, customer_mdm_id::text <> (lead(customer_mdm_id) over (order by audit_log_id desc))::text as customer_mdm_id_modified
, addresses
, addresses::text <> (lead(addresses) over (order by audit_log_id desc))::text as addresses_modified
, contacts
, contacts::text <> (lead(contacts) over (order by audit_log_id desc))::text as contacts_modified
, customer_code
, customer_code::text <> (lead(customer_code) over (order by audit_log_id desc))::text as customer_code_modified
, name
, name::text <> (lead(name) over (order by audit_log_id desc))::text as name_modified
, is_active
, is_active::text <> (lead(is_active) over (order by audit_log_id desc))::text as is_active_modified
, localized_name
, localized_name::text <> (lead(localized_name) over (order by audit_log_id desc))::text as localized_name_modified
, logo
, logo::text <> (lead(logo) over (order by audit_log_id desc))::text as logo_modified
, erp_customer_id
, erp_customer_id::text <> (lead(erp_customer_id) over (order by audit_log_id desc))::text as erp_customer_id_modified
, customer_group
, customer_group::text <> (lead(customer_group) over (order by audit_log_id desc))::text as customer_group_modified
, service_segment_code
, service_segment_code::text <> (lead(service_segment_code) over (order by audit_log_id desc))::text as service_segment_code_modified
, credit_insurance_code
, credit_insurance_code::text <> (lead(credit_insurance_code) over (order by audit_log_id desc))::text as credit_insurance_code_modified
, payment_term
, payment_term::text <> (lead(payment_term) over (order by audit_log_id desc))::text as payment_term_modified
, payment_type
, payment_type::text <> (lead(payment_type) over (order by audit_log_id desc))::text as payment_type_modified
, currency_code
, currency_code::text <> (lead(currency_code) over (order by audit_log_id desc))::text as currency_code_modified
, user_defined_fields
, user_defined_fields::text <> (lead(user_defined_fields) over (order by audit_log_id desc))::text as user_defined_fields_modified
, agency_mdm_id
, agency_mdm_id::text <> (lead(agency_mdm_id) over (order by audit_log_id desc))::text as agency_mdm_id_modified
, agency_hmx_legacy_id
, agency_hmx_legacy_id::text <> (lead(agency_hmx_legacy_id) over (order by audit_log_id desc))::text as agency_hmx_legacy_id_modified
, agency_code
, agency_code::text <> (lead(agency_code) over (order by audit_log_id desc))::text as agency_code_modified
, agency_name
, agency_name::text <> (lead(agency_name) over (order by audit_log_id desc))::text as agency_name_modified
from customer_audits
order by agency_code, customer_code, audit_log_id desc;
--all agencies audit
with customer_audits as
(
select id as audit_mdm_agency_audit_log_id
, log_datetime_created
, user_name as audit_mdm_agency_modified_by_user_name
, response_body::jsonb -> 'id' as audit_mdm_agency_agency_mdm_id
, response_body::jsonb -> 'addresses' as audit_mdm_agency_agency_addresses
, response_body::jsonb -> 'contacts' as audit_mdm_agency_agency_contacts
, response_body::jsonb -> 'code' as audit_mdm_agency_agency_code
, response_body::jsonb -> 'name' as audit_mdm_agency_agency_name
, response_body::jsonb -> 'isActive' as audit_mdm_agency_agency_is_active
, response_body::jsonb -> 'hmxLegacyId' as audit_mdm_agency_agency_hmx_legacy_id
, response_body::jsonb -> 'erpId' as audit_mdm_agency_agency_erp_id
, response_body::jsonb -> 'erpCompanyCode' as audit_mdm_agency_agency_erp_company_code
, response_body::jsonb -> 'officialName' as audit_mdm_agency_agency_official_name
, response_body::jsonb -> 'localizedName' as audit_mdm_agency_agency_localized_name
, response_body::jsonb -> 'logo' as audit_mdm_agency_logo
, response_body::jsonb -> 'currencyCode' as audit_mdm_agency_currency_code
, response_body::jsonb -> 'holdingCompany' -> 'id' as audit_mdm_agency_holding_company_id
, (select jsonb_agg(t -> 'name' order by t -> 'name') from jsonb_array_elements(response_body::jsonb -> 'mediaTypes') as x(t)) as agency_change_media_types
from audit_api_log
where api_name in ('createAgency', 'updateAgency')
and status = 200
and holding_company_id = 1001
)
select *
from customer_audits
order by audit_mdm_agency_agency_code, audit_mdm_agency_audit_log_id desc;
-- by agency, with diff checking
with customer_audits as
(
select id as audit_mdm_agency_audit_log_id
, log_datetime_created
, user_name as audit_mdm_agency_modified_by_user_name
, response_body::jsonb -> 'id' as audit_mdm_agency_agency_mdm_id
, response_body::jsonb -> 'addresses' as audit_mdm_agency_agency_addresses
, response_body::jsonb -> 'contacts' as audit_mdm_agency_agency_contacts
, response_body::jsonb -> 'code' as audit_mdm_agency_agency_code
, response_body::jsonb -> 'name' as audit_mdm_agency_agency_name
, response_body::jsonb -> 'isActive' as audit_mdm_agency_agency_is_active
, response_body::jsonb -> 'hmxLegacyId' as audit_mdm_agency_agency_hmx_legacy_id
, response_body::jsonb -> 'erpId' as audit_mdm_agency_agency_erp_id
, response_body::jsonb -> 'erpCompanyCode' as audit_mdm_agency_agency_erp_company_code
, response_body::jsonb -> 'officialName' as audit_mdm_agency_agency_official_name
, response_body::jsonb -> 'localizedName' as audit_mdm_agency_agency_localized_name
, response_body::jsonb -> 'logo' as audit_mdm_agency_logo
, response_body::jsonb -> 'currencyCode' as audit_mdm_agency_currency_code
, response_body::jsonb -> 'holdingCompany' -> 'id' as audit_mdm_agency_holding_company_id
, (select jsonb_agg(t -> 'name' order by t -> 'name') from jsonb_array_elements(response_body::jsonb -> 'mediaTypes') as x(t)) as agency_change_media_types
from audit_api_log
where api_name in ('createAgency', 'updateAgency')
and status = 200
and holding_company_id = 1001
and response_body::jsonb -> 'code' = '"US03"'
)
select audit_mdm_agency_audit_log_id
, log_datetime_created
, audit_mdm_agency_modified_by_user_name
, audit_mdm_agency_agency_mdm_id
, audit_mdm_agency_agency_mdm_id::text <> (lead(audit_mdm_agency_agency_mdm_id) over (order by audit_mdm_agency_audit_log_id desc))::text as audit_mdm_agency_agency_mdm_id_modified
, audit_mdm_agency_agency_addresses
, audit_mdm_agency_agency_addresses::text <> (lead(audit_mdm_agency_agency_addresses) over (order by audit_mdm_agency_audit_log_id desc))::text as audit_mdm_agency_agency_addresses_modified
, audit_mdm_agency_agency_contacts
, audit_mdm_agency_agency_contacts::text <> (lead(audit_mdm_agency_agency_contacts) over (order by audit_mdm_agency_audit_log_id desc))::text as audit_mdm_agency_agency_contacts_modified
, audit_mdm_agency_agency_code
, audit_mdm_agency_agency_code::text <> (lead(audit_mdm_agency_agency_code) over (order by audit_mdm_agency_audit_log_id desc))::text as audit_mdm_agency_agency_code_modified
, audit_mdm_agency_agency_name
, audit_mdm_agency_agency_name::text <> (lead(audit_mdm_agency_agency_name) over (order by audit_mdm_agency_audit_log_id desc))::text as audit_mdm_agency_agency_name_modified
, audit_mdm_agency_agency_is_active
, audit_mdm_agency_agency_is_active::text <> (lead(audit_mdm_agency_agency_is_active) over (order by audit_mdm_agency_audit_log_id desc))::text as audit_mdm_agency_agency_is_active_modified
, audit_mdm_agency_agency_hmx_legacy_id
, audit_mdm_agency_agency_hmx_legacy_id::text <> (lead(audit_mdm_agency_agency_hmx_legacy_id) over (order by audit_mdm_agency_audit_log_id desc))::text as audit_mdm_agency_agency_hmx_legacy_id_modified
, audit_mdm_agency_agency_erp_id
, audit_mdm_agency_agency_erp_id::text <> (lead(audit_mdm_agency_agency_erp_id) over (order by audit_mdm_agency_audit_log_id desc))::text as audit_mdm_agency_agency_erp_id_modified
, audit_mdm_agency_agency_erp_company_code
, audit_mdm_agency_agency_erp_company_code::text <> (lead(audit_mdm_agency_agency_erp_company_code) over (order by audit_mdm_agency_audit_log_id desc))::text as audit_mdm_agency_agency_erp_company_code_modified
, audit_mdm_agency_agency_official_name
, audit_mdm_agency_agency_official_name::text <> (lead(audit_mdm_agency_agency_official_name) over (order by audit_mdm_agency_audit_log_id desc))::text as audit_mdm_agency_agency_official_name_modified
, audit_mdm_agency_agency_localized_name
, audit_mdm_agency_agency_localized_name::text <> (lead(audit_mdm_agency_agency_localized_name) over (order by audit_mdm_agency_audit_log_id desc))::text as audit_mdm_agency_agency_localized_name_modified
, audit_mdm_agency_logo
, audit_mdm_agency_logo::text <> (lead(audit_mdm_agency_logo) over (order by audit_mdm_agency_audit_log_id desc))::text as audit_mdm_agency_logo_modified
, audit_mdm_agency_currency_code
, audit_mdm_agency_currency_code::text <> (lead(audit_mdm_agency_currency_code) over (order by audit_mdm_agency_audit_log_id desc))::text as audit_mdm_agency_currency_code_modified
, audit_mdm_agency_holding_company_id
, audit_mdm_agency_holding_company_id::text <> (lead(audit_mdm_agency_holding_company_id) over (order by audit_mdm_agency_audit_log_id desc))::text as audit_mdm_agency_holding_company_id_modified
from customer_audits
order by audit_mdm_agency_agency_code, audit_mdm_agency_audit_log_id desc;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment