Query new users since some date

[WORMrus]

Hello Community,

I am working on a small integration service. In order for it to works, it needs to get all the new users registered in my community since (last sync) some date. I would like to use API v2 for that, but that's not mandatory.

I expected the following query to work, but it returns "604 invalid query syntax":

SELECT id, email FROM users WHERE users.registration_data.registration_time>636650496000000000

The time above is the datetime.Ticks value from C# ( I have seen a suggestion to use the long value as a baseline). I have also tried the "2010-05-07T20:45:21.000-07:00" format instead of long.

May be I am misunderstanding something here? 

[srujanayeruvaka · Accepted solution]

Hi @WORMrus,

registration_data.registration_time is not allowed in the 'WHERE' clause. You may have to get all users, ORDER BY registration_data.registration_time and then filter out the rows you need.

SELECT id FROM users ORDER BY registration_data.registration_time DESC

Please refer to the documentation for more information - https://community.lithium.com/t5/Developer-Documentation/bd-p/dev-doc-portal?section=commv2&collecti...

Hope this helps!

Thanks,
Srujana.

[BeckyS]

If you are open to using the admin metrics (you’re needing a static report and not an ongoing query), you can get this data on the “user reports” tab: community admin > metrics > user reports. You can query on registration date.

[WORMrus]

@BeckyS I am not currently an admin there, but I guess I can ask for elevation. This means that I am not really familiar with this feature.

As far as I understand, this method is not a "proper" way to automate the process of retrieving the most recent users, isn't it? I mean it is designed to be used by human through its GUI, right?

[srujanayeruvaka]

Yes @WORMrus, the Community Admin can generate user reports (and many other reports) from the Admin console. The output is a .csv file. However, as @BeckyS mentioned, this is a static report generated on-demand.

Thanks,
Srujana.

[WORMrus]

@srujanayeruvaka I might have been not clear there. Please let me try to explain better:

I am working on fully automated service. So it is preferred that the solution is meant to be used in automation. For example: calling API and retrieving JSON response is a good solution. I am currently looking into your query, but it requires manual filtering and (probably) subsequent requests in cases when there are too many recent users. So this solution is good, but not perfect.

The reports solution might work as well, given that I can setup this report manually once and then call it programmatically as needed. Is this possible? I suppose this solution involves "clicking" GUI buttons and not really suitable for automation. If this is true, then I'll stick with 1st answer.

Thank you.

[srujanayeruvaka]

Hi @WORMrus,

The query I had suggested earlier will be helpful for your automated solution. You have to retrieve all users using the above API and parse the response to filter out the users who have registered after a certain date. This step of filtering is not manual; it has to be via your code.

And yes, the user report from the Admin console involves clicks and is a manual process.

Thanks,
Srujana.

[WORMrus]

Thank you @srujanayeruvaka for your reply. By manual I mean "on my side". It would've been better if API allowed it, but oh well.

Thank you form your prompt responses. I'll accept your solution and will work with it.