Analytics Email Error

[HarishG]

Hi everyone,

We are trying fetch all the pending tasks to send email to respective endpoint owners. getting the below error when trying to run the analytics report.

error:

Elasticsearch exception [type=mapper_parsing_exception, reason=failed to parse field [username] of type [long] in document with id 'libk9ogB7QO_3t3vznUa']]; nested: ElasticsearchException[Elasticsearch exception [type=illegal_argument_exception, reason=For input string: "harish.xxxxx@xxxx.com"]];]

Query:

select distinct a.taskkey as 'TaskID',a.taskdate as 'TaskDate',a.updatedate as 'Task_Complete_Date',u.FIRSTNAME as 'First Name',u.LASTNAME as 'Last Name',a.ACCOUNTNAME as 'AccountName',s.SYSTEMNAME as 'Security System',e.endpointname as 'Endpoint',ev.ENTITLEMENT_VALUE as 'Entitlement',a.requestkey as 'Request Id',(select username from users where userkey=e.ownerkey) as 'username', (select username from users where userkey=e.ownerkey) as 'ApplicationOwner',(select displayname from users where userkey=a.UPADTEUSER) as 'UPADTEUSER',CASE WHEN a.status='1' THEN 'New' WHEN a.status='2' THEN 'InProgress' WHEN a.status='4' THEN 'Discontinued' WHEN a.status='8' THEN 'Errored' else a.status end as 'TASk Status', a.source as 'Source',u.username AS 'REQUESTED FOR',u.Displayname AS 'REQUESTEE NAME', CASE WHEN a.TASKTYPE='1' THEN 'ADD ACCESS' WHEN a.TASKTYPE='2' THEN 'REMOVE ACCESS' WHEN a.TASKTYPE='3' THEN 'NEWACCOUNT' WHEN a.TASKTYPE='4' THEN 'ROLE REQUEST' WHEN a.TASKTYPE='5' THEN 'CHANGEPASSWORD' WHEN a.TASKTYPE='6' THEN 'ENABLE ACCOUNT' WHEN a.TASKTYPE='7' THEN 'PROPOSED ACCOUNT OWNERS' WHEN a.TASKTYPE='8' THEN 'DELETE ACCOUNT' WHEN a.TASKTYPE='9' THEN 'UPDATE USER' WHEN a.TASKTYPE='12' THEN 'UPDATE ACCOUNT' WHEN a.TASKTYPE='13' THEN 'PROPOSED Entitlement OWNERS' WHEN a.TASKTYPE='14' THEN 'DISABLE ACCOUNT' WHEN a.TASKTYPE='23' THEN 'MODIFY PRIVILEGE' WHEN a.TASKTYPE='24' THEN 'CREATE ENTITLEMENT' WHEN a.TASKTYPE='27' THEN 'UPDATE ENTITLEMENT' WHEN a.TASKTYPE='28' THEN 'DELETE ENTITLEMENT' WHEN a.TASKTYPE='25' THEN 'UPDATE ENTITLEMENT ACCESS ADD' WHEN a.TASKTYPE='26' THEN 'UPDATE ENTITLEMENT ACCESS REMOVE' WHEN a.TASKTYPE='29' THEN 'FIREFIGHTER ID GRANT ACCESS' WHEN a.TASKTYPE='30' THEN 'FIREFIGHTER ID REVOKE ACCESS' WHEN a.TASKTYPE='31' THEN 'UPDATE ACCESS END DATE' WHEN a.TASKTYPE='32' THEN 'LOCK ACCOUNT' WHEN a.TASKTYPE='33' THEN 'UNLOCK ACCOUNT' else a.tasktype end as 'TaskType' FROM arstasks a, users u, endpoints e, entitlement_values ev, securitysystems s where a.status in (1,2) and a.endpoint = e.endpointkey and u.userkey = a.userkey and e.securitysystemkey = s.systemkey and ev.entitlement_valuekey =a.entitlement_valuekey; 

Any suggestions?

Thanks in Advance!

Harish

[Darshanjain]

Hi @HarishG 

It looks like one of the value in username column is not fetching it correctly ? have you updated endpoint owner for all endpoints, Also try to run for one single endpoint see and see if you are able to get the record in data analyzer and then check in analytics as well.

Thanks

Darshan

[HarishG · Accepted solution]

Hi @Darshanjain ,

Thank you.

Issue got fixed when i recreated the report with same query! May be its a bug.

There is one more issue:

This query is not returning the Remove Account tasks from pending tasks in ARS.

any idea, how to include them?

Best regards

Harish

[Darshanjain]

Hi @HarishG 

Remove Account and remove Access task type is 2 only so both will be coming in the report, please check

Thanks

Darshan

[rushikeshvartak]

Use union and wherever entitlement_valuekey is null in arstasks table and tasktype =2