flowfuse icon indicating copy to clipboard operation
flowfuse copied to clipboard
verified publisher icon FlowFuse

Allow csv export of audit-log

Open knolleary opened this issue 1 year ago • 6 comments

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

knolleary avatar Jun 07 '24 09:06 knolleary

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

hardillb avatar Aug 05 '24 11:08 hardillb

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 avatar Aug 05 '24 14:08 hardillb

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

hardillb avatar Aug 06 '24 08:08 hardillb

  • 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.

knolleary avatar Aug 06 '24 08:08 knolleary

  • 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 avatar Aug 06 '24 08:08 hardillb

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 "

hardillb avatar Aug 06 '24 09:08 hardillb