Where is historical data stored for catalog tasks (sc_task table)?

[Mike Rivney]

Hi, I would like to query by manager (at the task level (sc_task).  However, the manager field (Assigned to.Manager) contains the current manager of the Assignee for the task.  Note, the Assignee has changed teams.  I want to retrieve the manager field value at the time of the creation of the task, not the Assignee's current manager.  Is this possible?  If so, then how?  Thanks.  Note, I don't know my Service Now version number.  Note, I work for MGM Resorts International, based in Las Vegas, NV.

[Tudor]

Hi Mike,

I'm sorry to say that in case you will require this for reporting you will have a lot of headaches achieving this - most likely only via script conditioned that the sys_user table is audited and has been so throughout the period of this for which the information is required.

For all future records, you can create a new metric that will store this information and afterward report on it. Documentation -> here.

Hope this helps!

 Tudor

[Mike Rivney]

Hi Tudor,  Thanks for the quick response.  I'm betting that the sys_user table was not audited.  So for future records, I understand that I can create a new metric.  My question now, is can this metric data be joined with the sc_task table when querying/reporting?  Thanks again, Mike.

[Tudor]

Hi Mike,

If you take a look at the metric_instance_list.do table, you'll understand what already happening with out-of-the-box defined metrics and how you can report to those.

The biggest issue for reporting on this data is that you can't impose any conditions on the sc_task records with the ID field which stores the record is actually a document_id type field.

Tudor

[Mike Rivney]

Hi Tudor,  

  I'm not able to see the metric_instance_list.do table.  That's probably because the Metric Definition plugin has yet to be installed.  

  I'm not clear on your second paragraph/sentence.  Can the two tables, sc_task and metric_instance_list.do, be joined? 

  Note, I do see another table listed in the Data step of creating a report.  It is named as the Catalog Task Metric table [sc_task_metric].  However, in this Data step, it indicates that this table has no description.  Probably again due to the plugin not yet installed.  Would this third table be of any help in a join?

  Note, if the tables can be joined, then I would export the data and report using Power BI.

Thanks, Mike.