Query on User updates

[SaiKiran]

Hi Team,

We are using below query to get a report of users that are updated on  current day. 

Select u.username,u.customproperty14,customproperty20.........
from users u, usershistory uh, USER_ACCOUNTS UA, ACCOUNTS A, ENDPOINTS E
where
(CAST(uh.updatedate as date)=CAST(curdate() as date))
and uh.USERKEY = u.USERKEY
AND u.EMPLOYEEID IS NOT NULL
AND U.USERKEY = UA.USERKEY
AND UA.ACCOUNTKEY = A.ACCOUNTKEY
AND A.ENDPOINTKEY= E.ENDPOINTKEY
AND E.ENDPOINTNAME='Active Directory'
and a.status in ('1','2')

Now we want to optimize the query to get only the updates of particular attributes updated on that particular day for users. Lets say i have 10 attributes and if there is any update happened on any of these 10 attributes for users then it should return only those users.

Request you to help me on how to achieve this. Thanks in advance.

[Amit_Malik]

@SaiKiran , you can add something like this in analytic query 

       AND (changelog LIKE '%customproperty11%' or changelog LIKE '%customproperty112%')

Select u.username,u.customproperty14,customproperty20.........
from users u, usershistory uh, USER_ACCOUNTS UA, ACCOUNTS A, ENDPOINTS E
where
(CAST(uh.updatedate as date)=CAST(curdate() as date))
and uh.USERKEY = u.USERKEY
AND u.EMPLOYEEID IS NOT NULL
AND U.USERKEY = UA.USERKEY
AND UA.ACCOUNTKEY = A.ACCOUNTKEY
AND A.ENDPOINTKEY= E.ENDPOINTKEY
AND E.ENDPOINTNAME='Active Directory'
and a.status in ('1','2') AND (changelog LIKE '%customproperty11%' or changelog LIKE '%customproperty12%')

[rushikeshvartak]

SELECT 
    u.username, 
    u.customproperty14, 
    u.customproperty20
FROM 
    users u
JOIN 
    usershistory uh ON uh.USERKEY = u.USERKEY
JOIN 
    USER_ACCOUNTS UA ON u.USERKEY = UA.USERKEY
JOIN 
    ACCOUNTS A ON UA.ACCOUNTKEY = A.ACCOUNTKEY
JOIN 
    ENDPOINTS E ON A.ENDPOINTKEY = E.ENDPOINTKEY
WHERE 
    CAST(uh.updatedate AS DATE) = CAST(CURDATE() AS DATE)
    AND u.EMPLOYEEID IS NOT NULL
    AND E.ENDPOINTNAME = 'Active Directory'
    AND A.status IN ('1', '2')
    AND (
        uh.changelog LIKE '%customproperty11%' OR
        uh.changelog LIKE '%customproperty12%' OR
        uh.changelog LIKE '%customproperty13%' OR
        uh.changelog LIKE '%customproperty14%' OR
        uh.changelog LIKE '%customproperty15%' OR
        uh.changelog LIKE '%customproperty16%' OR
        uh.changelog LIKE '%customproperty17%' OR
        uh.changelog LIKE '%customproperty18%' OR
        uh.changelog LIKE '%customproperty19%' OR
        uh.changelog LIKE '%customproperty20%'
    )