How to get user profile pictures (directory) using SQL (postgres) from Confluence 5.9.6

[Fabian Odoni]

I am trying to get the profile picture location from Confluence 5.9.6 using SQL from a postgres data base.

I tried this: https://answers.atlassian.com/questions/15018 but it didn't work:

SELECT string_val
FROM os_propertyentry
WHERE entity_key = 'confluence.user.profile.picture'

As result i only get:

('user-avatar',)
('user-avatar',)
('user-avatar',)
('user-avatar',)

and that doesn't help me any further. The profile pictures are there, saved for example under 

attachements/ver003/nonspaced/56/200/950306/950307/1

Can anyone please help me here. I need to get the profile pictures of every user in Confluence. Something like:

user_id, dir_of_profile_picture

Thank you very much!

[Fabian Odoni]

Well, since no one seems to know the answer and I happen to know a SQL-wizard, here's the solution:

SELECT
	cu.lower_username,
    'attachments/ver003/nonspaced/'
    	|| (c.pageid % 250) || '/'
    	|| ((c.pageid / 1000) % 250) || '/'
    	|| (c.pageid) || '/'
    	|| c.contentid || '/'
    	|| c.version 
	AS avatar_path
FROM
	user_mapping cu
	LEFT JOIN content c
		ON c.creator = cu.user_key
		AND c.contenttype = 'ATTACHMENT'
		AND c.title = 'user-avatar'
		AND c.content_status = 'current'
WHERE 
	cu.lower_username = 'admin';

Just replace 'admin' with the desired user name.

Relevant documentation can be found here:
https://confluence.atlassian.com/doc/hierarchical-file-system-attachment-storage-704578486.html

Now since I know the solution, just one question to Atlassian: WHY?