User Change History

report.json

{"name":"User Data History","owner":"[email protected]","creation":"2025-04-18 22:58:51.526033","modified":"2025-05-07 10:18:12.185534","modified_by":"[email protected]","docstatus":0,"idx":0,"report_name":"User Data History","ref_doctype":"Version","is_standard":"No","module":"Core","report_type":"Query Report","letter_head":"","add_total_row":0,"disabled":0,"prepared_report":0,"timeout":0,"query":"SELECT \n    v.name AS version_id,\n    v.creation AS change_timestamp,\n    v.owner AS changed_by,\n    v.ref_doctype AS document_type,\n    v.docname AS document_name,\n    CASE \n        WHEN j.source = 'changed' THEN 'changed'\n        WHEN j.source = 'added' THEN 'added'\n        WHEN j.source = 'removed' THEN 'removed'\n        ELSE 'unknown'\n    END AS change_type,\n    j.field_name,\n    CASE \n        WHEN j.source = 'added' THEN NULL\n        ELSE CONVERT(j.old_value USING utf8mb4) COLLATE utf8mb4_turkish_ci\n    END AS old_value,\n    CASE \n        WHEN j.source = 'removed' THEN NULL\n        ELSE CONVERT(j.new_value USING utf8mb4) COLLATE utf8mb4_turkish_ci\n    END AS new_value,\n    CASE\n        WHEN j.field_name IN ('uoms', 'item_defaults', 'barcodes') AND j.source IN ('added', 'removed') \n        THEN JSON_EXTRACT(v.data, CONCAT('$.', j.source, '_row_', j.field_name))\n        ELSE NULL\n    END AS detail_data\nFROM \n    `tabVersion` v,\n    JSON_TABLE(\n        CONCAT('[', \n            '{\"source\":\"changed\",\"data\":', COALESCE(JSON_EXTRACT(v.data, '$.changed'), 'null'), '},',\n            '{\"source\":\"added\",\"data\":', COALESCE(JSON_EXTRACT(v.data, '$.added'), 'null'), '},',\n            '{\"source\":\"removed\",\"data\":', COALESCE(JSON_EXTRACT(v.data, '$.removed'), 'null'), '}'\n        , ']'),\n        '$[*]' COLUMNS (\n            source VARCHAR(50) PATH '$.source',\n            NESTED PATH '$.data[*]' COLUMNS (\n                field_name VARCHAR(255) PATH '$[0]',\n                old_value VARCHAR(255) PATH '$[1]',\n                new_value VARCHAR(255) PATH '$[2]'\n            )\n        )\n    ) AS j\nWHERE \n    v.owner = %(user)s\n    AND j.field_name IS NOT NULL\n    AND v.creation BETWEEN %(begin_date)s AND %(end_date)s\nORDER BY \n    change_timestamp DESC, document_name;\n","doctype":"Report","filters":[{"name":"6nqbdqsm6f","owner":"[email protected]","creation":"2025-04-18 22:58:51.526033","modified":"2025-05-07 10:18:12.185534","modified_by":"[email protected]","docstatus":0,"idx":1,"label":"User","fieldtype":"Link","fieldname":"user","mandatory":1,"wildcard_filter":0,"options":"User","default":"","parent":"User Data History","parentfield":"filters","parenttype":"Report","doctype":"Report Filter"},{"name":"s1mnqhv1ra","owner":"[email protected]","creation":"2025-04-18 22:58:51.526033","modified":"2025-05-07 10:18:12.185534","modified_by":"[email protected]","docstatus":0,"idx":2,"label":"Begin Date","fieldtype":"Date","fieldname":"begin_date","mandatory":1,"wildcard_filter":0,"default":"2025-01-01","parent":"User Data History","parentfield":"filters","parenttype":"Report","doctype":"Report Filter"},{"name":"di2im5e7ql","owner":"[email protected]","creation":"2025-04-18 22:58:51.526033","modified":"2025-05-07 10:18:12.185534","modified_by":"[email protected]","docstatus":0,"idx":3,"label":"End Date","fieldtype":"Date","fieldname":"end_date","mandatory":1,"wildcard_filter":0,"default":"2025-01-01","parent":"User Data History","parentfield":"filters","parenttype":"Report","doctype":"Report Filter"}],"roles":[{"name":"mk8l6q927f","owner":"[email protected]","creation":"2025-04-18 22:58:51.526033","modified":"2025-05-07 10:18:12.185534","modified_by":"[email protected]","docstatus":0,"idx":1,"role":"System Manager","parent":"User Data History","parentfield":"roles","parenttype":"Report","doctype":"Has Role"},{"name":"tbgq0aird7","owner":"[email protected]","creation":"2025-04-18 22:58:51.526033","modified":"2025-05-07 10:18:12.185534","modified_by":"[email protected]","docstatus":0,"idx":2,"role":"Administrator","parent":"User Data History","parentfield":"roles","parenttype":"Report","doctype":"Has Role"}],"columns":[]}