Greetings!
We've created a database view that makes it very easy to see which credential Discovery uses to access each CI it finds. It has made Discovery troubleshooting, as well as credential management easier for me - so I figured I'd share it with the entire community here. The steps below should result in a handy credential affinity database view for you.
Navigate to System Definition > Database Views, and click "New"
Give your new view a name. I used "u_credential_affinity_view". Assign a label and plural - I used Friendly Credential View(s). Populate the description with something easy to understand, like "Makes it much easier to see what credentials were used to discover things".
Next, we'll create some View Tables. Click "New", and select the Credential Affinity Table (dscy_credentials_affinity), and assign "ca" (no quotes) as the Variable Prefix. Check the "Left Join" box, and leave the order at 100.
Under "View Fields", select "New". Select the credential_id field, and click "Submit". Repeat the same process and select the ip_address field.
Click "Update" to navigate back to your Database View.
Now we need to create another view table; click "New" under "View Tables" and select the Credentials Table (discovery_credentials), and assign "c" (no quotes) as the Variable Prefix. Leave the order at 100, and enter "ca_credential_id=c_sys_id" (no quotes) as the Where Clause.
Add the following View fields to this View Table in the following order: name, user_name, and type. Click "Update" to navigate back to your Database View.
Now to create our last View table, click "New" under "View Tables" and select the Configuration Item Table (cmdb_ci), and assign "ci" as the Variable Prefix. Leave the order at 100 and set "ca_ip_address=ci_ip_address" (no quotes) as the Where Clause. Click "Update" to navigate back to your Database View.
Now that we're done, go ahead and click "Try It" under Related Links to test out your new Database View! On the database view, click the gear icon to personalize your list columns. You'll want the following fields in the right-hand slushbucket:
Name(ci_name)
Class
IP address
Name(c_name)
Type
Created
You should be able to see the name of each CI found by Discovery, along with it's class, IP address, the name and type of credential used to access it, and the date/time at which that CI was created.
Enjoy!
Hi Dan, This looks very promising and I thank you for providing it. I'm interested in this because we're having issues discovering our Windows servers and this will help find out what credentials we're using on each machine. The problem is that I'm running into a bit of a snag. I put eveything in, but when I click "Try It", I get an error ( I get it twice actually 😞
Syntax Error or Access Rule Violation detected by database (Unknown column 'ca.ip_address' in 'on clause')
Here's my setup:
I've had problems with database views in the past, I'm I'm sure I'm missing something basic and would appreciate any thoughts on how a I can work through this. FWIW: I'm on Eureka.
Hey Steve Driscoll,
No problem, I'm happy to help out where I can. We're on Berlin - but I tested the view in Eueka, and it works there too.
That error appears to indicate that the "ip_address" View Field is missing from the dscy_credentials_affinity View Table you created. Could that be the case? I've captured screen shots of each of my View Tables for you... keep in mind that Service-Now can be "fussy" about the order in which we create these, so you may want to start fresh if you are indeed lacking the necessary View Field(s):
dscy_credentials_affinity View Table:
discovery_credentials View Table:
cmdb_ci View Table:
Hope this helps! Please let me know what you find out.
Dan
