External Identities updated per day

[AshirvadhN]

Hi Team,

This query i used to find the count of usernames who are updated within today. This is showing the result perfectly if i updated the users through UI. But when i tried updating users through CSV the result is not increasing. My intension is this query should show the count of usernames which are updated today when i update through UI and CSV.

But this is working only UI but not CSV.
When i tried updating users through CSV the result is not increasing

please does any have solution for this or any idea.

please let me know.

select distinct now()
as 'Report Run Date Time',b.employeetype,count(b.username) as 'Count of Updated Users' from users b where DATEDIFF(now(),b.UPDATEDATE) < 1 and b.STATUSKEY =1 AND b.EMPLOYEEID IS NOT NULL AND b.employeetype in ('BeSEE','US Vendor','DA Vendor') group by b.employeetype

[This post has been edited by a Moderator to consolidate information.]

[sahil]

You can leverage the usershistory table to know the information of the users updated from UI/CSV and use join with the user's table.

- Users.userkey = usershistory.userkey column

- Column for modified timestamp in usershistory =  updatedate 

Note: usershistory table is not accessible from Data Analyzer but will work in Analytics. 

[rushikeshvartak]

Does user history tab gets updated while doing user import via csv

[AshirvadhN]

Hi @rushikeshvartak 

Yes when i update the user details from UI/CSV the details is showing in the update history tab.

[AshirvadhN]

Hi @sahil 

Is this the query to use instead of upone.

select distinct now() as 'Report Run Date Time', b.employeetype, DATE(a.UPDATEDATE) as 'UserUpdateDate',count(b.username) as 'Count of Users' from usershistory a,users b where DATEDIFF(now(),a.UPDATEDATE) <= 1 and a.USERKEY = b.USERKEY AND b.STATUSKEY =1 AND b.EMPLOYEEID IS NOT NULL AND b.employeetype in ('BeSEE','US Vendor','DA Vendor') group by b.employeetype,UserUpdateDate;

[sahil]

Yes, you can use the query.