Hi,
I am trying to create a SOD report which will identify all the SODs occured in last 30 days. Along with it, to add all the function names and entitlement value associated with it. Upon joining the other function key, it is throwing an error, and the time condition is not working. Below is the attached basic query created. Can you check as to how the mentioned stuff can be applied.
select u.username, u.systemusername, u.email, a.name as 'Account Name', case when a.status=1 then 'Active' when a.status=2 then 'InActive' when a.status in ('Manually Provisioned') then 'Manually Provisioned' when a.status in ('Manually Suspended') then 'Manually Suspended' when a.status in ('SUSPENDED FROM IMPORT SERVICE') then 'SUSPENDED FROM IMPORT SERVICE' end as 'Account Status', se.ACCOUNTKEY, e.displayname as 'endpointname', se.SODKEY, s.COMMENTS as 'sod comments',s.RISKCODE,s.STATUS as ' sod_status',f.FUNCTION_NAME,ev.ENTITLEMENT_VALUE, ev.displayname from users u, sodrisks s, endpoints e, sodrisk_entitlement se, accounts a, functions f, function_entitlements fe,entitlement_values ev where s.SODKEY=se.SODKEY and a.ACCOUNTKEY=se.ACCOUNTKEY and u.userkey=s.userkey and se.FUNCTIONKEY=f.functionkey and fe.FUNCTIONKEY=f.FUNCTIONKEY and fe.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY and e.endpointkey=a.endpointkey group by se.ACCOUNTKEY, se.SODKEY;
