Who is the owner of an attached file

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

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.

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.

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

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

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

1 Like