Reporting against sys_email

[DaSmith9]

Had a reporting scenario come up and wanted to see how or if anyone has ever handled this. 

Scenario is to report against 'sys_email' and 'incident' and be able to filter for emails against certain incident criteria. A database view was my first thought, but I discovered database views cannot be configured against tables in rotation, which 'sys_email' is. Also, adding 'sys_email' to the system properties to be able to report directly against it is not viable as you cannot dot walk to the incident table (that I could find) to bring in incident data for filtering against.

Is this an exercise to query separately and join outside of ServiceNow, or maybe into a staging table that could be reported on? 

Thanks,
Daniel

[DaSmith9]

Definitely aware of the table size and performance constraints, that's why the table is on rotation to begin with. I'm just curious what creative alternatives in platform there are besides database view. 

Business requirement I think is irrelevant, it's just a "how-to" scenario at this point. 

[SanjivMeher]

What do you need from the email in the report?

[sachin_namjoshi]

Another alternative it to configure ETL jobs to export records from service now to external database.

After exporting records from sys_email and incident using optimized filters , you can write SQL query in external database to generate reports.

I implemented similar requirement with Data pump integration with service now.

https://sourceforge.net/projects/servicenowpump/

Regards,

Sachin

[Michael Ritchie]

An idea that is a complete hack, is to create a M2M table between incident and email, or task and email and use an after insert business rule to create this M2M on sys_email.  Then using this record since you have a direct reference to both tables you can query.  Again very ugly but doable.

[DaSmith9]

Interesting, thank you for chiming in.