Data Dump Help!

[needabitofhelp]

We have recently moved away from ServiceNow to another ITSM provider.

I have a team of SQL dba's asking "how do we access the data"?

1. we cannot connect to the database, please see the below error:

I’m getting an error with companyname_sftpmig_lhr_CHGxxxxxx_key.sql:

CREATE TABLE `cmdb` ( `sys_class_name` varchar(80) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `asset_tag` varchar(40) DEFAULT NULL, `serial_number` varchar(255) DEFAULT NULL, `assigned_to` varchar(32) DEFAULT NULL, `company` varchar(32) DEFAULT NULL, `assigned` datetime DEFAULT NULL, `assignment_group` varchar(32) DEFAULT NULL, `install_status` int(11) DEFAULT NULL, `purchase_date` date DEFAULT NULL, `order_date` datetime DEFAULT NULL, `delivery_date` datetime DEFAULT NULL, `install_date` datetime DEFAULT NULL, `manufacturer` varchar(32) DEFAULT NULL, `vendor` varchar(32) DEFAULT NULL, `model_id` varchar(32) DEFAULT NULL, `justification` varchar(80) DEFAULT NULL, `location` varchar(32) DEFAULT NULL, `department` varchar(32) DEFAULT NULL, `lease_id` varchar(40) DEFAULT NULL, `warranty_expiration` date DEFAULT NULL, `po_number` varchar(40) DEFAULT NULL, `invoice_number` varchar(40) DEFAULT NULL, `gl_account` varchar(40) DEFAULT NULL, `cost` double(18,7) DEFAULT NULL, `cost_cc` varchar(3) DEFAULT NULL, `checked_in` datetime DEFAULT NULL, `checked_out` datetime DEFAULT NULL, `due` datetime DEFAULT NULL, `due_in` varchar(40) DEFAULT NULL, `support_group` varchar(32) DEFAULT NULL, `supported_by` varchar(32) DEFAULT NULL, `owned_by` varchar(32) DEFAULT NULL, `managed_by` varchar(32) DEFAULT NULL, `sys_domain` varchar(32) DEFAULT NULL, `sys_domain_path` varchar(255) DEFAULT NULL, `cost_center` varchar(32) DEFAULT NULL, `asset` varchar(32) DEFAULT NULL, `skip_sync` tinyint(1) DEFAULT NULL, `unverified` tinyint(1) DEFAULT NULL, `sys_id` char(32) NOT NULL, `sys_class_path` varchar(255) DEFAULT NULL, `sys_updated_by` varchar(40) DEFAULT NULL, `sys_updated_on` datetime DEFAULT NULL, `sys_created_by` varchar(40) DEFAULT NULL, `sys_created_on` datetime DEFAULT NULL, `sys_mod_count` int(11) DEFAULT NULL, `category` varchar(40) DEFAULT NULL, `subcategory` varchar(40) DEFAULT NULL, `operational_status` int(11) DEFAULT NULL, `fault_count` int(11) DEFAULT NULL, `model_number` varchar(255) DEFAULT NULL, `short_description` mediumtext DEFAULT NULL, `comments` mediumtext DEFAULT NULL, `discovery_source` varchar(40) DEFAULT NULL, `first_discovered` datetime DEFAULT NULL, `last_discovered` datetime DEFAULT NULL, `start_date` datetime DEFAULT NULL, `change_control` varchar(32) DEFAULT NULL, `monitor` tinyint(1) DEFAULT NULL, `attributes` mediumtext DEFAULT NULL, `ip_address` varchar(255) DEFAULT NULL, `mac_address` varchar(24) DEFAULT NULL, `can_print` tinyint(1) DEFAULT NULL, `correlation_id` mediumtext DEFAULT NULL, `dns_domain` varchar(255) DEFAULT NULL, `fqdn` varchar(255) DEFAULT NULL, `a_ref_1` varchar(32) DEFAULT NULL, `a_int_1` int(11) DEFAULT NULL, `a_str_1` varchar(255) DEFAULT NULL, `a_str_2` varchar(255) DEFAULT NULL, `a_str_3` varchar(255) DEFAULT NULL, `a_str_4` varchar(255) DEFAULT NULL, `a_str_5` varchar(255) DEFAULT NULL, `a_str_6` varchar(255) DEFAULT NULL, `a_str_7` mediumtext DEFAULT NULL, `a_str_8` mediumtext DEFAULT NULL, `a_str_9` varchar(255) DEFAULT NULL, `a_str_10` varchar(255) DEFAULT NULL, `a_str_11` varchar(100) DEFAULT NULL, `a_bln_1` tinyint(1) DEFAULT NULL, `a_str_12` varchar(255) DEFAULT NULL, `a_str_13` varchar(255) DEFAULT NULL, `a_str_14` varchar(255) DEFAULT NULL, `a_str_15` varchar(40) DEFAULT NULL, `a_int_2` int(11) DEFAULT NULL, `a_str_16` varchar(40) DEFAULT NULL, `a_ref_2` varchar(32) DEFAULT NULL, `a_int_3` bigint(20) DEFAULT NULL, `a_int_4` bigint(20) DEFAULT NULL, `a_ref_3` varchar(32) DEFAULT NULL, `dest_ip_network` varchar(32) DEFAULT NULL, `a_num_1` decimal(15,2) DEFAULT NULL, `a_num_2` decimal(15,2) DEFAULT NULL, `a_bln_2` tinyint(1) DEFAULT NULL, `a_bln_3` tinyint(1) DEFAULT NULL, `a_num_3` decimal(15,2) DEFAULT NULL, `a_bln_4` tinyint(1) DEFAULT NULL, `a_str_17` varchar(255) DEFAULT NULL, `a_str_18` varchar(255) DEFAULT NULL, `a_str_19` varchar(255) DEFAULT NULL, `a_str_20` varchar(255) DEFA... Error Code: 1069. Too many keys specified; max 64 keys allowed 0.000 sec

Any help would be appreciated.

2. Once we get access, how do we access all the incidents, requests, changes, problem data tickets etc?

[Robbie · Accepted solution]

Hi Mark @needabitofhelp,

To help others, can you mark any of my previous responses (or this response) correct and/or helpful please? It helps everyone in the ServiceNow community - thanks in advance.

It's quite a big area so with a quick search I've found a link that gives a high-level overview and example of the data model in each area. Check p5 for the 'Task' data model as well as p8 for a small part of the 'Incident' data model.  This should provide a good starting point.

You've got quite a task here.... It's one thing to obtain the data, it's another thing to understand how it's all connected and related, but I'll be happy to try and help where I can.

To help others (or for me to help you more directly), please mark this response as correct and/or helpful.

Thanks, Robbie

https://www.snow-mirror.com/wp-content/uploads/2016/07/ServiceNow-Data-Model-v3.4.pdf

[Robbie · Accepted solution]

Hi @needabitofhelp,

Interesting. I can see the core table of 'task', along with other heavily used tables such as 'cmdb' which is great, but, like you, I am questioning as to why you can't retrieve or see the 'incident', or 'change_request' tables.

There does seem to be a pattern here. Any table that extends 'task' (Remember I mentioned the OO - Object Oriented design) is missing. Depending on what applications/modules you had installed, you're missing 60 or so plus. This is just for tables that extend 'task' such as 

'incident', 'change_request', 'problem' etc. FYI - 'incident_fact_table' does not extend 'task'.

I've also noticed the same pattern against other missing core tables such as 'cmdb_ci' which extends from 'cmdb'.

It seems the query is not pulling back extended tables.

@needabitofhelp  - To help others (or for me to help you more directly), please mark this response as correct and/or helpful.

Thanks, Robbie

[Robbie · Accepted solution]

Hi @needabitofhelp,

Ahhh - this makes more sense now. If you look at the 'task' table data, a key column for you is the 'sys_class_name' column (database name: 'sys_class_name' database label: 'Task Type').

This will help you identify which of the 'task' records are in fact 'Incident' records or 'Change Request' records etc. 

@needabitofhelp  - Do you see the option to mark this or any response as 'Accept as Solution' as shown below?

To help others (or for me to help you more directly), please mark this response as correct. 

Thanks, Robbie

[needabitofhelp · Accepted solution]

Hi Robbie - thanks for your help again, appreciated.  I have the option to tick as Helpful and Accept as Solution, I thought Accept as Solution would close this thread down as resolved?

[Robbie · Accepted solution]

Hi @needabitofhelp,

Thanks for the response. 'Accept as Solution' does not close the thread. The thread remains open and the conversation and questions can continue. Each or many responses can be marked as a solution.

How did you go with checking the sys_class_name against the 'task' table records? Do you see the different records in your extract?

Thanks,

Robbie

[Robbie · Accepted solution]

Hi @needabitofhelp,

Thanks for the response and marking my previous response as correct.

Whenever you see a sys_id (aka a GUID - 32 character string), this is a reference to another table. The 'opened_by' and 'closed_by' in this case refer to Users stored in the 'sys_user' table.

To help others (or for me to help you more directly), please mark this response by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie

[Robbie · Accepted solution]

Hi @needabitofhelp,

Sorry if I was unclear, what I meant was, were you able to filter the 'task' record set by using the 'sys_class_name' value of 'incident' and 'change_request' etc?

Thanks,

Robbie

[Robbie · Accepted solution]

Hi @needabitofhelp,

'business_service' is a column on the 'task' table. It is a reference field allowing the storage of a  sys_id (aka foreign key) to a CMDB 'Service' ('cmdb_ci_service' table). This is a 1:1 relationship.

'work_notes_list' is again a column on the 'task' table. It is a list field allowing the storage of 1:M sys_id's (aka foreign keys) of users from the 'sys_user' table

To help others (or for me to help you more directly), please mark this response by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie

[Robbie · Accepted solution]

Hi @needabitofhelp ,

Variables are found within the 'item_option_new' table.

To help others (or for me to help you more directly), please mark this response by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie

[Robbie · Accepted solution]

Hi @needabitofhelp,

The 'sys_id' is absolutely key. It's a unique 32-character GUID (Globally Unique ID) that identifies each record in a SN (ServiceNow) instance.

The 'a_ref_1' etc are indexes that are present on the table.

To help others (or for me to help you more directly), please mark this response by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie

[Robbie · Accepted solution]

Hi @needabitofhelp 

The sys_id is the primary key. It's the unique identifier for each record.

Let me try and explain with a simple example:

An incident record contains numerous data points from the 'description' field to the 'priority' and 'urgency' fields etc. Whilst the Incident 'number' field could contain a value of 'INC0010001', the unique identifier and sys_id for this record is: c0cf7954874023003c1c8467a7cb0b54

An incident record can also store data points from other tables, for example, the 'caller' field which stores the user that reported the incident. The 'caller' field on the incident record will contain the 'sys_id' from the user table ('sys_user') - think foreign key in general database terms.

To answer your specific question and provide another example for the relationship between the 'task' and 'audit' table. Every record in the 'sys_audit' table will have a unique identifier (The 'sys_id' for each record).

A 'sys_audit' record contains a column called 'documentkey'. The 'documentkey' will contain the 'sys_id' of the 'task' record that has been changed/updated. 

For example. If a work note has been added to an incident, the 'sys_audit' table will show the old value and new value of the incident and the 'work_notes' column that has been updated.

Does that make sense?

To help others (or for me to help you more directly), please mark this response by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie

[Robbie · Accepted solution]

Hi @needabitofhelp,

You want the join between the 'sys_id' column on the 'task' table and the 'documentkey' column on the 'sys_audit' table.

To help others (or for me to help you more directly), please mark this response by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie

[Robbie · Accepted solution]

Hi @needabitofhelp,

I'm glad you've managed to recreate the relationship between audit history and requests.

To answer your question regarding the 'item_option_new' table. I believe you asked me in an earlier question about where 'Variables' are stored.

'Variables' for context are table columns, however, they differ slightly from what you'd normally expect if you're new to ServiceNow. 'Variables' in a ServiceNow context are predominantly used within the Request/Request Item process. (Although they can be used elsewhere).

This table hierarchy and relationship in this process is very different from an Incident where all data points are pretty much stored on the Incident table directly or with a foreign key to a related table for example.

'Variables' on the other hand are different, at least in the Request/Request Item process.

Let me try and explain, each Request ('sc_request' table) can have 1 (or many) Request Item(s) ('sc_req_item' table). A Request Item contains Request Item data points (Columns specific to and stored within the Request Item table such as Request Item Number and Requested For etc) as well as a relationship to a Catalog item ('sc_cat_item' table).

The Catalog Item is the thing you're requesting such as a 'New Laptop' for example. The Catalog Item has a relationship to 'Variables'.

The architecture and design of ServiceNow's Request process optimizes storage with the concept that a field (Variable) used when ordering a laptop would not be used when onboarding a new employee for example.

Data collected and stored is very different for each Request/Request Item so therefore is not stored directly on the Request Item table hence the related table structure via Catalog Items and Variables.

FYI ServiceNow allows customers to pretty much build any type of Request from an IT request to a HR request and beyond, so as you can imagine the data points can differ greatly.

Other processes and tables, such as Incident, Problem and Change are simplified.

Hopefully, that makes sense.

To help others (or for me to help you more directly), please mark this response by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie

[Robbie · Accepted solution]

Hi Mark @needabitofhelp,

When it comes to the Request/Request item process, once a request is submitted, the 'Variables' are not updated so therefore there would be no Variable history or audit data. 

You would however be able to see the high-level history against a Request and Request item with regards to its lifecycle, who it was assigned to and approved by etc. (Request Item table columns and non 'Variable' values)

I'm not sure if I've answered your question here. Please expand on any specific questions or use cases and I'll see how I can help or advise.

To help others (or for me to help you more directly), please mark this response correct by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie

[Robbie · Accepted solution]

Hi @needabitofhelp,

I believe these variables will be stored on the 'question_answer'.

For context, whilst in our previous questions and thread we've been discussing the Request process and Catalog items, there is another table where 'Record producer' variables are stored - 'question_answer' table.

To help others (or for me to help you more directly), please mark this response correct by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie

[Robbie · Accepted solution]

Hi Mark / @needabitofhelp,

How's the export/transition going?

To answer your question, the 'question_answer' table has 2 key columns to note. The 'table_name' column and the 'table_sys_id' column.

The 'table_name' column relates to which table the variable relates to, eg: Incident or Change Request. ('incident' or 'change_request').

The 'table_sys_id' column relates to the specific record of the given table. eg: Incident record number INC0008111 (Please note, the unique 'sys_id' is referenced) 

See the below screenshot to help visualize. 

To help others (or for me to help you more directly), please mark this response correct by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie

[Robbie · Accepted solution]

Hi Mark / @needabitofhelp,

I'm not going to lie, since diving deep into the world of ServiceNow, my SQL has become a little rusty. (Advanced apologies if I need to go back and relearn SQL)

Based on my reading of the statement and understanding, a few things we need to consider. (I've added line numbers to help explain and confirm the SQL query).

Essentially, it's at the 'LEFT OUTER JOIN' and 'RIGHT OUTER JOIN' I believe (However, please confirm and let me know how you go).

- Line 4 and the reference to 'snow.task t' - Whilst the tables referenced here typically (but not always) extend the base 'task' table, I believe you'd have to reference the actual table. For example, 'snow.incident t' or 'snow.change_request', or 'snow.sc_cat_item'.  

(Records are not typically created on the base 'task' table)

- Line 10 - same explanation as above for Line 4. I believe you need to reference the specific table name and not the base table.

SQL Query with Line numbers:

1. SELECT t.sys_class_name, t.number, q.question_text, qa.value, q.help_text, qa.table_name, qa.sys_updated_by, qa.sys_updated_on, qa.sys_created_by, qa.sys_created_on
2. FROM snow.question_answer qa
3. LEFT OUTER JOIN snow.question q ON qa.question = q.sys_id
4. LEFT OUTER JOIN snow.task t ON qa.table_sys_id = t.sys_id
5. WHERE qa.sys_created_on >= '2023-01-01'
6. UNION ALL
7. SELECT t.sys_class_name, t.number, q.question_text, qa.value, q.help_text, qa.table_name, qa.sys_updated_by, qa.sys_updated_on, qa.sys_created_by, qa.sys_created_on
8. FROM snow.question_answer qa
9.RIGHT OUTER JOIN snow.question q ON qa.question = q.sys_id
10. RIGHT OUTER JOIN snow.task t ON qa.table_sys_id = t.sys_id
11. WHERE qa.sys_created_on >= '2023-01-01';

(Please do let me know how you go and feedback to me)

To help others (or for me to help you more directly), please mark this response correct by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie

[Robbie · Accepted solution]

Hi Mark / @needabitofhelp,

Ahhh - back to the table flattening of the task table. Sorry, I should have remembered that.

Based on this, I'm struggling to understand where the problem is. The task.sys_id would indeed match the question_answer.table_sys_id

Let me see if I can break the query down into smaller chunks.

A question I have here is, what data point values do you see when you extract a single record from the 'question_answer' table at the database layer? 

Can you provide a single record example with the data points please?

To help others (or for me to help you more directly), please mark this response correct by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie

[needabitofhelp]

Robbie, thanks again. 

Are you familiar with the ServiceNow table and database structure?  - not at all, any help would be appreciated.

Mark

[Robbie · Accepted solution]

Hi Mark @needabitofhelp,

To help others, can you mark any of my previous responses (or this response) correct and/or helpful please? It helps everyone in the ServiceNow community - thanks in advance.

It's quite a big area so with a quick search I've found a link that gives a high-level overview and example of the data model in each area. Check p5 for the 'Task' data model as well as p8 for a small part of the 'Incident' data model.  This should provide a good starting point.

You've got quite a task here.... It's one thing to obtain the data, it's another thing to understand how it's all connected and related, but I'll be happy to try and help where I can.

To help others (or for me to help you more directly), please mark this response as correct and/or helpful.

Thanks, Robbie

https://www.snow-mirror.com/wp-content/uploads/2016/07/ServiceNow-Data-Model-v3.4.pdf

[needabitofhelp]

Thanks Robbie.

I appreciate any help on this. 

Come back to you soon.

I will mark further correct and/or helpfuls, I cannot mark previous comments.

Mark

[needabitofhelp]

Hi Robbie, we are still struggling with this..  we have no tables called change_request, sc_request or incident for example.  I have attached the tables we can see. 

[Robbie · Accepted solution]

Hi @needabitofhelp,

Interesting. I can see the core table of 'task', along with other heavily used tables such as 'cmdb' which is great, but, like you, I am questioning as to why you can't retrieve or see the 'incident', or 'change_request' tables.

There does seem to be a pattern here. Any table that extends 'task' (Remember I mentioned the OO - Object Oriented design) is missing. Depending on what applications/modules you had installed, you're missing 60 or so plus. This is just for tables that extend 'task' such as 

'incident', 'change_request', 'problem' etc. FYI - 'incident_fact_table' does not extend 'task'.

I've also noticed the same pattern against other missing core tables such as 'cmdb_ci' which extends from 'cmdb'.

It seems the query is not pulling back extended tables.

@needabitofhelp  - To help others (or for me to help you more directly), please mark this response as correct and/or helpful.

Thanks, Robbie