Analytics- emailhistory job -error while generating

[Puspanjali]

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

error

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

[rushikeshvartak]

Mentioned columns are restricted to use. 

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

[Puspanjali]

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 

whereas in SQL Builder I can see  some records are present

[rushikeshvartak]

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

[Puspanjali]

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.

[rushikeshvartak]

Does ES Analytics Service is working?

Check Result in Preview 

[Puspanjali]

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

Query returned no records

[rushikeshvartak]

Does other report working ?

is your ES Analytics Service is UP?

[Puspanjali]

Hi Rushikesh

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

One test report result

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

[rushikeshvartak · Accepted solution]

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)

[sahajranajee]

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.

[Puspanjali]

Hi Rushikesh and Sahaj Ranajee

Thank you both for your support.

I'm able to export the report now.

[avinashchhetri]

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.

[Manu269]

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?