Who is the owner of an attached file

Frequently Asked Questions
1

Is there a way to find out which attachments were posted by which users and date?

2

Hi Roger,

as far as I know, this information can only be pulled out of the database right now.
An example query (without resolving the IDs) would look like this on a PostgreSQL installation:

mattermost=# SELECT creatorid,postid,createat,name FROM fileinfo LIMIT 10;
         creatorid          |           postid           |   createat    |          
----------------------------+----------------------------+---------------+----------
 57os7g51u3g9i8fzixsceqkjto | damobgqrtt8zjdrb7qwei6h3wy | 1628667204111 | Image Pas
 uf9huytu9jbn9bkehpg8jhfm7e | 53aa9rqymf8x3foyuqtw6i3dza | 1628667314647 | Image Pas
 j9xeh1qnti8stppqq7o8nty3fh | ypu5i8sohin6detwdxfxsk16oh | 1560228850509 | SSL_STATU
 kc1df5rkzjft88wkr11aa6ki1r | 8q7uyyhtn7r5xmcekg8nm7sr3a | 1560241046626 | Kein_Supp
 kiproorrn386tf9gwpguxe8o1y | ru1e6xu31idxmcrn536ys3p5tw | 1560251696558 | leads.txt
 tj6yc69ymfdujeattogaoej1ao | ckr5myfpmfnrbq5y7xphwdxxiw | 1560327442677 | D005736.p
 kw1ccc3pcty1iy1inifih3aq1r | rzax3iypk3d3icq6ct6954dqoo | 1560340047058 | Angebot-1
 ypupscd95ibjxgxnxix1gibm5y | ogdy7pgzstrcic9xcz9c9uu6go | 1628667321506 | Image Pas
 mym4yoz187yeujs1q4sunbzrho | n6qxrbbn83nxiqgczzrjg5a8rc | 1628667473015 | Screensho
 1kua7j8zo7ndfmgd1xw7oyugae | nha87abrtjnppff7q71mn7daso | 1628667916435 | Image Pas

Can you define the output format you’d like to see? I’m sure we can work on a query which produces the expected format then.

3

Hey Alex, very cool, my interesting is the user name, email address , date similar like 2022/08/05 , File name and the stored name in the data directory of mattermost.

4

Alright, this is the query to use for that:

SELECT u.username,u.email,to_timestamp(f.createat/1000),f.path FROM fileinfo AS f,users AS u WHERE u.id=f.creatorid LIMIT 10;

It’s limited to 10 results so you can check if that works for you. If it does, remove the LIMIT 10 at the end (or increase the limit).

5

Hi Alex, this is exactly what I was looking for. Really thank you for the quick help.
Best regards, Roger

6

You’re welcome :slight_smile: Let me know if you need additional reports.

1 Like