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!
A little bit of feedback (belated I know!). I had the same error message as above,
Syntax Error or Access Rule Violation detected by database (Unknown column 'ca.ip_address' in 'on clause')
I managed to resolve this by updating the order field of the tables as shown below.
