Analytics- emailhistory job -error while generating

Puspanjali
New Contributor
New Contributor

Hi Team,

I'm using the below query to get the status of the mail sent.

select createdate, toaddress, subjectofemail, status from emailhistory where status in (5,15,404)

In SQL Builder(Data analyzer) = I'm getting the record.

But when I'm trying to implement it in Analytics I'm getting the below message, which is not allowing me to generate a report

Puspanjali_1-1658821220979.png

error

Column status are restricted from use. Please specify columns other than these. Also, use of symbols <,>,& in column name is restricted.

 

13 REPLIES 13

rushikeshvartak
All-Star
All-Star

Mentioned columns are restricted to use. 

 

select createdate, toaddress, subjectofemail, status as state from emailhistory where status in (5,15,404)


Regards,
Rushikesh Vartak
If the response is helpful, please click Accept As Solution and kudos it.

Hi Rushikesh

Thank you for your help, I'm able to create the analytics now.

But when I'm running this report it is giving me zero records 

Puspanjali_0-1658827540524.png

whereas in SQL Builder I can see  some records are present

Puspanjali_1-1658827585937.png

 

 

select createdate as 'emailsentdate', toaddress, subjectofemail, status as state from emailhistory where status in (5,15,404)


Regards,
Rushikesh Vartak
If the response is helpful, please click Accept As Solution and kudos it.

Hi Rushikesh

The purpose of my query is to identify, which emails have not been sent yet

select createdate as 'emailsentdate', toaddress, subjectofemail, status as state from emailhistory where status in (5,15,404)

By using the above query as well, I'm getting zero records.

Puspanjali_0-1658835471626.png

 

 

Does ES Analytics Service is working?

rushikeshvartak_0-1658836884139.png

Check Result in Preview 


Regards,
Rushikesh Vartak
If the response is helpful, please click Accept As Solution and kudos it.

Hi Rushikesh

In Preview, I also can see the result but after I created that  report and try to run it again to have record,It is showing zero record

Puspanjali_0-1658837787357.png

Query returned no records

Puspanjali_1-1658837959228.png

Puspanjali_2-1658838235618.png

 

 

 

 

Does other report working ?

is your ES Analytics Service is UP?


Regards,
Rushikesh Vartak
If the response is helpful, please click Accept As Solution and kudos it.

Hi Rushikesh

Other reports are fine, I'm able to create a new report with records

One test report result

Puspanjali_0-1658841189036.png

 

Could you please help me with the steps, how I can verify whether ES Analytics Service is UP or not?

 

Since you have createdate column in report its throwing error 

 

{"log":"2022-07-27 15:11:17,016 [elastic-apm-server-reporter] WARN co.elastic.apm.agent.report.IntakeV2ReportingEventHandler - It seems like you are using a version of the APM Server which is not compatible with this agent. Please use APM Server 6.5.0 or newer.\n","stream":"stdout","time":"2022-07-27T15:11:17.016669276Z"}
 
Solution: Keep date column not as 1st column
 
select toaddress as 'To_address', subjectofemail as 'Email_Subject', status as state from emailhistory where status in (5,15,404)

 
 

 


Regards,
Rushikesh Vartak
If the response is helpful, please click Accept As Solution and kudos it.

sahajranajee
Saviynt Employee
Saviynt Employee

Hi @Puspanjali ,

Please check logs when you click on run. You should be seeing errors. If you are able to run other analytics, then your ES is working.

 


Regards,
Sahaj Ranajee
Sr. Product Specialist

Puspanjali
New Contributor
New Contributor

Hi Rushikesh and Sahaj Ranajee

Thank you both for your support.

I'm able to export the report now.

 

Puspanjali_0-1658983273159.png

 

Hello @Puspanjali,

Could you let us all know what was the issue with the reports and how did you fix it ? It will help other members of this community.

 

 

Regards,
Avinash Chhetri

Manu269
All-Star
All-Star

Hello team,

We tried validating the same in SSM v5.5 SP3.11, We don't see any issue if we are accommodating the createdate at first instance. Not sure if this is identified issue in lower version?

Manu269_0-1658985054209.pngManu269_1-1658985085242.png

 

Regards
Manish Kumar
If the response answered your query, please Accept As Solution and Kudos
.