topic Sql database query to display all users and emails in Alfresco Forum

Sql database query to display all users and emails

wshomeworker10 — Tue, 28 Sep 2021 11:17:02 GMT

Hi All,

I was just wondering if anyone know how to get an sql query to return all users and their emails i have a query that shows all users just nee to edit it to show all email address also.

SELECT n.id,

n.uuid,

p1.string_value AS "Username"

FROM alf_node AS n,

alf_qname AS q,

alf_node_properties AS p1,

alf_node_properties AS p2

WHERE n.type_qname_id=q.id

AND n.id=p1.node_id

AND p1.node_id=p2.node_id

AND p1.string_value!=''

AND

((

q.local_name='person'

AND

p1.qname_id IN (SELECT id FROM alf_qname WHERE local_name='userName')

) OR (

q.local_name='user'

AND

p1.qname_id IN (SELECT id FROM alf_qname WHERE local_name='username')

)) AND

p2.qname_id IN (SELECT id FROM alf_qname WHERE local_name='enabled')

ORDER BY p1.string_value;

Re: Sql database query to display all users and emails

abhinavmishra14 — Tue, 28 Sep 2021 15:14:12 GMT

Why don't you use the REST Apis:

V1 rest api: http://localhost:8080/alfresco/api/people

V2 rest api: https://docs.alfresco.com/content-services/6.2/develop/rest-api-guide/people-groups/

GET - http://localhost:8080/alfresco/api/-default-/public/alfresco/versions/1/people

$ curl -X GET -H 'Accept: application/json' -H 'Authorization: Basic VElDS0VUXzA4ZWI3ZTJlMmMxNzk2NGNhNTFmMGYzMzE4NmNjMmZjOWQ1NmQ1OTM=' 'http://localhost:8080/alfresco/api/-default-/public/alfresco/versions/1/people' | jq
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  1686  100  1686    0     0   4052      0 --:--:-- --:--:-- --:--:--  4052
{
  "list": {
    "pagination": {
      "count": 4,
      "hasMoreItems": false,
      "totalItems": 4,
      "skipCount": 0,
      "maxItems": 100
    },
    "entries": [
      {
        "entry": {
          "lastName": "Beecher",
          "userStatus": "Helping to design the look and feel of the new web site",
          "jobTitle": "Graphic Designer",
          "statusUpdatedAt": "2011-02-15T20:20:13.432+0000",
          "mobile": "0112211001100",
          "emailNotificationsEnabled": true,
          "description": "Alice is a demo user for the sample Alfresco Team site.",
          "telephone": "0112211001100",
          "enabled": false,
          "firstName": "Alice",
          "skypeId": "abeecher",
          "avatarId": "198500fc-1e99-4f5f-8926-248cea433366",
          "location": "Tilbury, UK",
          "company": {
            "organization": "Moresby, Garland and Wedge",
            "address1": "200 Butterwick Street",
            "address2": "Tilbury",
            "address3": "UK",
            "postcode": "ALF1 SAM1"
          },
          "id": "abeecher",
          "email": "abeecher@example.com"
        }
      },
      {
        "entry": {
          "firstName": "Administrator",
          "emailNotificationsEnabled": true,
          "company": {},
          "id": "admin",
          "enabled": true,
          "email": "admin@alfresco.com"
        }
      },
      {
        "entry": {
          "firstName": "Guest",
          "emailNotificationsEnabled": true,
          "company": {},
          "id": "guest",
          "enabled": false
        }
      },
      {
        "entry": {
          "lastName": "Jackson",
          "userStatus": "Working on a new web design for the corporate site",
          "jobTitle": "Web Site Manager",
          "statusUpdatedAt": "2011-02-15T20:13:09.649+0000",
          "mobile": "012211331100",
          "emailNotificationsEnabled": true,
          "description": "Mike is a demo user for the sample Alfresco Team site.",
          "telephone": "012211331100",
          "enabled": false,
          "firstName": "Mike",
          "skypeId": "mjackson",
          "avatarId": "3fbde500-298b-4e80-ae50-e65a5cbc2c4d",
          "location": "Threepwood, UK",
          "company": {
            "organization": "Green Energy",
            "address1": "100 Cavendish Street",
            "address2": "Threepwood",
            "address3": "UK",
            "postcode": "ALF1 SAM1"
          },
          "id": "mjackson",
          "email": "mjackson@example.com"
        }
      }
    ]
  }
}

Re: Sql database query to display all users and emails

cesarista — Thu, 30 Sep 2021 08:54:33 GMT

Hi:

Just check the following projects, they are using rest api (v1):

https://github.com/ecm4u/alfresco-shell-tools/blob/master/bin/alfListUsers.sh

or this:

https://github.com/zylklab/alfresco-export-scripts#getpeoplesh

Regards.

--C.

Re: Sql database query to display all users and emails

heiko_robert — Fri, 01 Oct 2021 18:44:34 GMT

If you really need that as database sql query this should do the job:

SELECT 
    n.id AS node_id
    , concat(s.protocol,'://',s.identifier,'/', n.uuid) AS node_ref
    , np1.string_value AS username
    , np2.string_value AS email
FROM alf_node n
JOIN alf_store s ON (n.store_id=s.id)
-- filter on type person
JOIN alf_qname qn ON (n.type_qname_id=qn.id AND qn.local_name='person' 
    AND ns_id=(SELECT id FROM alf_namespace WHERE uri='http://www.alfresco.org/model/content/1.0')
 )
-- join property userName
left JOIN alf_node_properties np1 ON (n.id=np1.node_id 
    AND np1.qname_id =(SELECT id FROM alf_qname WHERE local_name='userName'
        AND ns_id=(SELECT id FROM alf_namespace WHERE uri='http://www.alfresco.org/model/content/1.0')
        )
    )
-- join property email
left JOIN alf_node_properties np2 ON (n.id=np2.node_id 
    AND np2.qname_id =(SELECT id FROM alf_qname WHERE local_name='email'
        AND ns_id=(SELECT id FROM alf_namespace WHERE uri='http://www.alfresco.org/model/content/1.0')
        )
    )
ORDER BY np1.string_value;