Auto-populating host field during discovery

[blanm]

Hello!

I am fairly new to service now and I have been tasked with all things discovery and cmdb. I will try my best to explain this, as I have limited knowledge still.

Whenever discovery completes for network related ci's, the host field does not auto-populate, even when the pattern itself makes the connection from the ci to the server table. I will be using a specific example to explain this, but this does happen on all tables that has a host field. 

When discovering a MS SQL instance, it will set the instance name correctly - MSSQLSERVER@hostexample

The host field on this table however (which is set as a reference field to the server table), always shows "empty", meaning I have to double click the field and search for the host to manually add it. 

This field references our server table to find names. We would like the value of this field to be hostexample, so that we can click it and have it take us to the record of hostexample.

My question is how can I make this field populate with the host name automatically, since its already being discovered and added as a part of the instance's name?

Again though, this happens on other tables, the host field is always empty, even when a relationship is made to the IP/host and I can see it in the dependency view. 

I have looked tirelessly through the community articles, as well as all servicenow discovery, ci class manager, script action, business rules and many other documentation pages.

I feel like this would be a script action, but I am not quite there yet with my understanding of how to write this, I only know basic java and I am still learning all of the different functions and syntax. 

I tried to make a basic script, but I am not even sure where I could try to test this without affecting other data in our dev instance. We do have dev people as we are a large organization, but they are all very busy so I am a little on my own here. Any assistance would be greatly appreciated!

[AdamEndwright]

I'm trying to find the exact same answer.  My Database team would like the host name available on the Instances and the Databases.  It makes the List View much more valuable and they won't have to drill into each Instance record.  The Host field already exists on those objects and is set to reference servers.  I've tried looking at the pattern for MSSQL DB on Windows, but it's a little over my head.  I would have to assume I have enough credentials to do this because it's creating the instance name that contains the hostname, and I'm getting database records created for each instance.

[Doug5]

I have the same problem.  Did you get an answer yet? 

I could not get MS SQL Discovery working using WMI.  However it works with WinRM.  So I have written a workaround - where we flip flop between WMI and WinRM on alternate days.  However, the host field is still empty even with WinRM discovery - just like you said.  ServiceNow support are still working on the WMI issue.

[blanm]

Hi,

So I found that the relationship that is made during discovery is on the form side, which is why you can see the relationships being made once you click into a CI. The list view for the MSSQL table is separate from this. So essentially you need to create a create relation/reference step within the MSSQL DB on Windows pattern, which would be within the "MSSQL populate database table" shared library step at the end of the first section pattern. I was able to compare that to another pattern where a reference field was auto-populated, but I had already created two business rule scripts that solved my issue before I discovered this explanation/information. 

I had to add a couple of custom steps to the "MSSQL populate database table" pattern to add the host name to a field, remove duplicates from that temporary table, and then union the temporary table ($dbs) with the $cmdb_ci_db_mssql_database table. This puts the string of the host in the field I chose and created in the CI class manager for this table, which was the host_id field. 

Then I wrote two business rules. The first looks for the string value in the host_id and compares it to our server table's name field. If it finds a match, it inserts the sys_id of the matching host into the host field on the cmdb_ci_db_mssql_database table. If it doesnt find a match, it stops.

The second rule looks at the value of the host_id field and then finds related information we were specifically looking to fill in other referenced fields from the host record on the server table (company, department, environment, managed_by_group).

I am very aware this logic was not the best way to do this and can be simplified. It works though, and is temporary until I can fix the pattern to make the relation on the table itself. I am sorry if that was not the best explanation, but the customizations I made got complicated pretty quickly.