nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

Sql database query to display all users and emails

wshomeworker10

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;

Find more posts tagged with

Alfresco Content Services

Comments

abhinavmishra14

Why don't you use the REST Apis:

V1 rest api: <a href="http://localhost:8080/alfresco/api/people" target="_blank" rel="nofollow noopener noreferrer">http://localhost:8080/alfresco/api/people</a>

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

GET - <a href="http://localhost:8080/alfresco/api/-default-/public/alfresco/versions/1/people" target="_blank" rel="nofollow noopener noreferrer">http://localhost:8080/alfresco/api/-default-/public/alfresco/versions/1/people</a>

$ 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"
        }
      }
    ]
  }
}

cesarista

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.

heiko_robert

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;