Allow csv export of audit-log - Githubissues

FlowFuse / flowfuse

Build bespoke, flexible, and resilient manufacturing low-code applications with FlowFuse and Node-RED

https://flowfuse.com

Other

269 stars 63 forks source link

Allow csv export of audit-log #3992

Closed knolleary closed 1 month ago

knolleary commented 3 months ago

Epic

No response

Description

As a: team owner

I want to: be able to download a copy of the audit log locally for auditing/recording

So that: I can meet my audit requirements

Proposal is a button to download the audit log in csv format.

https://app-eu1.hubspot.com/contacts/26586079/record/0-2/9822854636

Which customers would this be available to

Everyone - CE/Starter/Team/Enterprise

Acceptance Criteria

No response

Have you provided an initial effort estimate for this issue?

I have provided an initial effort estimate

hardillb commented 1 month ago

Do we want to add a similar route for the platform audit log while we are at it?

hardillb commented 1 month ago

Sample array of audit log entries

[
  {
    "hashid": "adxZDkb14Y",
    "id": 4966,
    "event": "user.pat.created",
    "body": "{\"updates\":[{\"key\":\"id\",\"old\":\"L1mGyQegz2\",\"dif\":\"updated\"},{\"key\":\"name\",\"old\":\"audit-log\",\"dif\":\"updated\"},{\"key\":\"scope\",\"old\":\"\",\"dif\":\"updated\"}]}",
    "entityId": "1",
    "entityType": "user",
    "createdAt": "2024-08-05T13:19:29.845Z",
    "UserId": 1,
    "ProjectId": null,
    "ownerId": null,
    "User": {
      "hashid": "DMpxaBOzEv",
      "id": 1,
      "username": "alice"
    }
  },
  {
    "hashid": "VBmJnPgmva",
    "id": 4965,
    "event": "account.login",
    "body": null,
    "entityId": "1",
    "entityType": "user",
    "createdAt": "2024-08-05T13:17:49.876Z",
    "UserId": 1,
    "ProjectId": null,
    "ownerId": null,
    "User": {
      "hashid": "DMpxaBOzEv",
      "id": 1,
      "username": "alice"
    }
  }
]

Do we start with headers matching the keys e.g.

id	event	body	entityId	entityType	createdAt	UserId	ProjectId	ownerId
4966	user.pat.created	"{\"updates\":[{\"key\":\"id\",\"old\":\"L1mGyQegz2\",\"dif\":\"updated\"},{\"key\":\"name\",\"old\":\"audit-log\",\"dif\":\"updated\"},{\"key\":\"scope\",\"old\":\"\",\"dif\":\"updated\"}]}"	1	user	"2024-08-05T13:19:29.845Z"	1	null	null
4965	account.login	null	1	user	"2024-08-05T13:17:49.876Z"	1	null	null

hardillb commented 1 month ago

Works

        reply.send([
            ['id', 'event', 'body', 'scope', 'trigger', 'createdAt'],
            ...result.log.map(row => [
                row.id,
                row.event,
                JSON.stringify(row.body),
                JSON.stringify(row.scope),
                JSON.stringify(row.trigger),
                row.createdAt
            ])
        ]
        .map(row => row.join(','))
        .join('\n'))

But will not scale to large number of entries

knolleary commented 1 month ago

All ids must be hashids, not bare ids.
We already have forge/db/views/AuditLog.js for generating external views of an auditLog entry. This should be extended to include a csv function to keep things together.
The existing api defaults to limiting how many entries are shown. For v1, this api should have the same behaviour. The url could even be the same as the existing audit-log entries but with /csv on the end (or a query param to specify format).
This then makes it no worse than the existing api that loads the audit-log in terms of how much data is accessed in a single request.

hardillb commented 1 month ago

Yeah, I moved to the "clean" view that is using hashids.
I added /export to the end of the existing routes and are using the same paging.
I suppose the serialization to the JSON string, should be no worse than to a CSV (unless they have a object to Stream encoder)

hardillb commented 1 month ago

Platform

curl -H "Authorization: Bearer $TOKEN" http://localhost:3000/api/v1/admin/audit-log/export?limit=5
id,event,body,scope,trigger,createdAt
adxZDkb14Y,user.pat.created,"{\"updates\":[{\"key\":\"id\",\"old\":\"L1mGyQegz2\",\"dif\":\"updated\"},{\"key\":\"name\",\"old\":\"audit-log\",\"dif\":\"updated\"},{\"key\":\"scope\",\"old\":\"\",\"dif\":\"updated\"}]}","{\"id\":\"DMpxaBOzEv\",\"type\":\"user\"}","{\"id\":\"DMpxaBOzEv\",\"type\":\"user\",\"name\":\"alice\"}","Mon Aug 05 2024 14:19:29 GMT+0100 (British Summer Time)"
VBmJnPgmva,account.login,,"{\"id\":\"DMpxaBOzEv\",\"type\":\"user\"}","{\"id\":\"DMpxaBOzEv\",\"type\":\"user\",\"name\":\"alice\"}","Mon Aug 05 2024 14:17:49 GMT+0100 (British Summer Time)"
wPxLzkw1ld,account.login,,"{\"id\":\"DMpxaBOzEv\",\"type\":\"user\"}","{\"id\":\"DMpxaBOzEv\",\"type\":\"user\",\"name\":\"alice\"}","Fri Aug 02 2024 15:04:46 GMT+0100 (British Summer Time)"
QE1Wd2NmNP,account.login,"{\"error\":{\"code\":\"unauthorized\",\"message\":\"unauthorized\"},\"user\":{\"id\":null,\"name\":null,\"username\":\"alice\",\"email\":null}}","{\"id\":\"\",\"type\":\"user\"}","{\"id\":null,\"hashid\":null,\"type\":\"unknown\",\"name\":\"unknown\"}","Fri Aug 02 2024 15:04:38 GMT+0100 (British Summer Time)"
4pxRjklxNP,account.login,"{\"error\":{\"code\":\"unauthorized\",\"message\":\"unauthorized\"},\"user\":{\"id\":null,\"name\":null,\"username\":\"alice\",\"email\":null}}","{\"id\":\"\",\"type\":\"user\"}","{\"id\":null,\"hashid\":null,\"type\":\"unknown\",\"name\":\"unknown\"}","Fri Aug 02 2024 15:04:35 GMT+0100 (British Summer Time)"

Instance

curl -H "Authorization: Bearer $TOKEN" http://localhost:3000/api/v1/projects/152fb007-7eb4-4e41-ad3b-8f946d11108b/audit-log/export?limit=5
id,event,body,scope,trigger,createdAt
RNG3Y24mEk,project.settings.updated,"{\"project\":{\"id\":\"152fb007-7eb4-4e41-ad3b-8f946d11108b\",\"name\":\"first\"},\"updates\":[{\"key\":\"palette.npmrc\",\"old\":\"\",\"new\":\"//_password=foo\",\"dif\":\"updated\"}]}","{\"id\":\"152fb007-7eb4-4e41-ad3b-8f946d11108b\",\"type\":\"project\"}","{\"id\":\"DMpxaBOzEv\",\"type\":\"user\",\"name\":\"alice\"}","Fri Aug 02 2024 15:01:45 GMT+0100 (British Summer Time)"
4zGE3PJmaq,project.settings.updated,"{\"project\":{\"id\":\"152fb007-7eb4-4e41-ad3b-8f946d11108b\",\"name\":\"first\"},\"updates\":[{\"key\":\"palette.npmrc\",\"new\":\"\",\"dif\":\"created\"}]}","{\"id\":\"152fb007-7eb4-4e41-ad3b-8f946d11108b\",\"type\":\"project\"}","{\"id\":\"DMpxaBOzEv\",\"type\":\"user\",\"name\":\"alice\"}","Fri Aug 02 2024 11:49:49 GMT+0100 (British Summer Time)"
qgGnP5emjl,project.settings.updated,"{\"project\":{\"id\":\"152fb007-7eb4-4e41-ad3b-8f946d11108b\",\"name\":\"first\"},\"updates\":[{\"key\":\"palette.npmrc\",\"old\":\"//_authToken=\\\"ben\\\"\\n//_auth=foo\\n//_password=bar\",\"dif\":\"deleted\"}]}","{\"id\":\"152fb007-7eb4-4e41-ad3b-8f946d11108b\",\"type\":\"project\"}","{\"id\":\"DMpxaBOzEv\",\"type\":\"user\",\"name\":\"alice\"}","Fri Aug 02 2024 11:48:53 GMT+0100 (British Summer Time)"
Wq1XWV8Gp0,project.settings.updated,"{\"project\":{\"id\":\"152fb007-7eb4-4e41-ad3b-8f946d11108b\",\"name\":\"first\"},\"updates\":[{\"key\":\"palette.npmrc\",\"new\":\"//_authToken=\\\"ben\\\"\\n//_auth=foo\\n//_password=bar\",\"dif\":\"created\"}]}","{\"id\":\"152fb007-7eb4-4e41-ad3b-8f946d11108b\",\"type\":\"project\"}","{\"id\":\"DMpxaBOzEv\",\"type\":\"user\",\"name\":\"alice\"}","Fri Aug 02 2024 11:32:40 GMT+0100 (British Summer Time)"
Bl19JP2mQy,project.settings.updated,"{\"project\":{\"id\":\"152fb007-7eb4-4e41-ad3b-8f946d11108b\",\"name\":\"first\"},\"updates\":[{\"key\":\"palette.npmrc\",\"old\":\"; foo\",\"dif\":\"deleted\"}]}","{\"id\":\"152fb007-7eb4-4e41-ad3b-8f946d11108b\",\"type\":\"project\"}","{\"id\":\"DMpxaBOzEv\",\"type\":\"user\",\"name\":\"alice\"}","Fri Aug 02 2024 11:28:32 GMT+0100 (British Summer Time)"

I've escaped the JSON as it includes , and the whole thing needs wrapping in "