Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

What's the proper way to query all the users of an AEM installation.

Avatar

Avatar
Give Back
Level 1
davids23429739
Level 1

Likes

2 likes

Total Posts

16 posts

Correct Reply

0 solutions
Top badges earned
Give Back
Ignite 1
Validate 1
Boost 1
View profile

Avatar
Give Back
Level 1
davids23429739
Level 1

Likes

2 likes

Total Posts

16 posts

Correct Reply

0 solutions
Top badges earned
Give Back
Ignite 1
Validate 1
Boost 1
View profile
davids23429739
Level 1

07-03-2019

Hello,

Do you guys know what's the proper way (if exists) to query all the users in the system (under /home/users)?

We have a site for one of our clients in which in one component they need to select a user from the system, it can be any user. Currently we use the following query to retrieve the users based on a search term ("%s").

SELECT * FROM [nt:unstructured] AS p WHERE (ISDESCENDANTNODE([/home/users]) AND (CONTAINS(p.email, \"*%s*\") OR CONTAINS(p.givenName, \"*%s*\") OR CONTAINS(p.familyName, \"*%s*\") OR CONTAINS(p.preferredName, \"*%s*\")) AND [email] IS NOT NULL AND [id] IS NOT NULL)

There are a lot of users in the system, we use SAML so the profiles have a lot of custom properties. Using this query and an admin session is pretty much what we do to retrieve the users.

Currently we're experiencing some performance issues and the query is starting to take more and more time.

Is there like an out of the box feature for AEM to do this?

Any help is greatly appreciated.

Thank you so much.

Replies

Avatar

Avatar
Validate 25
Level 10
smacdonald2008
Level 10

Likes

1,406 likes

Total Posts

12,671 posts

Correct Reply

2,278 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Give back 900
Give back 600
View profile

Avatar
Validate 25
Level 10
smacdonald2008
Level 10

Likes

1,406 likes

Total Posts

12,671 posts

Correct Reply

2,278 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Give back 900
Give back 600
View profile
smacdonald2008
Level 10

07-03-2019

Your approach is valid. Using JCR SQL2. We had written tools in past and you are correct. Depending on the number of user nodes, it May take time to. process the result set.

Avatar

Avatar
Give Back
Level 1
davids23429739
Level 1

Likes

2 likes

Total Posts

16 posts

Correct Reply

0 solutions
Top badges earned
Give Back
Ignite 1
Validate 1
Boost 1
View profile

Avatar
Give Back
Level 1
davids23429739
Level 1

Likes

2 likes

Total Posts

16 posts

Correct Reply

0 solutions
Top badges earned
Give Back
Ignite 1
Validate 1
Boost 1
View profile
davids23429739
Level 1

07-03-2019

Hi,

Thanks for your response.

Is there another way to optimize this (besides adding the required OAK indexes) ?

Avatar

Avatar
Give back 300
MVP
Gaurav-Behl
MVP

Likes

243 likes

Total Posts

1,145 posts

Correct Reply

281 solutions
Top badges earned
Give back 300
Give Back 50
Give Back 5
Give Back 3
Give Back 25
View profile

Avatar
Give back 300
MVP
Gaurav-Behl
MVP

Likes

243 likes

Total Posts

1,145 posts

Correct Reply

281 solutions
Top badges earned
Give back 300
Give Back 50
Give Back 5
Give Back 3
Give Back 25
View profile
Gaurav-Behl
MVP

08-03-2019

Per my knowledge, SQL2 query optimization follows more or less similar best practices as SQL. You may search for 'SQL tuning best practices' and implement the same.

This query is already using "CONTAINS" and specific node (nt:unstructured) to be searched which is good.

Couple of things may be revisited (and tested) accordingly:

-Using select fields rather than select *

-avoiding wild cards in the front

-use UNION than ORs

-use inner joins

https://www.sisense.com/blog/8-ways-fine-tune-sql-queries-production-databases/

If you use OOB Query Performance tool (http://localhost:4502/libs/granite/operations/content/diagnosistools/queryPerformance.html), it would provide you with optimized query. In your case, the optimized query follows same SQL standards.

The cost can be worked upon using indexes.

Attempting optimisation

Alternative query available:

select [p].[jcr:primaryType] as [p.jcr:primaryType] from [nt:unstructured] as [p] where (contains([p].[familyName], '*%s*')) and (isdescendantnode([p], [/home/users])) and ([p].[email] is not null) and ([p].[id] is not null) union

select [p].[jcr:primaryType] as [p.jcr:primaryType] from [nt:unstructured] as [p] where (contains([p].[email], '*%s*')) and (isdescendantnode([p], [/home/users])) and ([p].[email] is not null) and ([p].[id] is not null) union

select [p].[jcr:primaryType] as [p.jcr:primaryType] from [nt:unstructured] as [p] where (contains([p].[preferredName], '*%s*')) and (isdescendantnode([p], [/home/users])) and ([p].[email] is not null) and ([p].[id] is not null) union

select [p].[jcr:primaryType] as [p.jcr:primaryType] from [nt:unstructured] as [p] where (contains([p].[givenName], '*%s*')) and (isdescendantnode([p], [/home/users])) and ([p].[email] is not null) and ([p].[id] is not null)

1707760_pastedImage_11.png

Avatar

Avatar
Coach
Employee
Jörg_Hoh
Employee

Likes

1,081 likes

Total Posts

3,121 posts

Correct Reply

1,061 solutions
Top badges earned
Coach
Give back 600
Ignite 5
Ignite 3
Ignite 1
View profile

Avatar
Coach
Employee
Jörg_Hoh
Employee

Likes

1,081 likes

Total Posts

3,121 posts

Correct Reply

1,061 solutions
Top badges earned
Coach
Give back 600
Ignite 5
Ignite 3
Ignite 1
View profile
Jörg_Hoh
Employee

11-03-2019

If you need to have the complete list of users quite often:

* use a query and fill the result into an in-memory cache

* when something below /home/users changes, either invalidate the cache completely or just re-read the change user.

Jörg