The final official goal I'm looking for is to generate a report that provides a list of servers that are not being discovered in our CMDB but should be discovered. Certain Appliances have security restrictions that do not allow SSH to be enabled and thus, they should be excluded from discovery. This currently requires a few things.
- The Server in the Server table (cmdb_ci_server) has an updated date of before yesterday.
- The VM Instance in the VM Instance table (cmdb_ci_vmware_instance) is associated with the Server.
- There are key values in the tag table (cmdb_key_value) that contain "cmdb-appliance"
I want my report to show me all the Servers that aren't being discovered, but aren't tagged with cmdb-appliance. In order to do this, I need to use a database view.
When I do the database view using the server and vm instance tables, everything works fine, but when I add the key value table, if I attempt to exclude just those VM Instances, it attempts to load all the CIs that reside in cmdb_ci (which is the parent of both the Server and VM Instance tables.) This includes any selection of left join in any combination and even using != to join the VM Instance and Key Value Tables
I'm stuck trying to figure out how to parse the VM Instances so that it removes the servers tagged with what I want to exclude. Once I get this down, I can add necessary filters (install status of 1, etc.) Please let me know if this is even possible.
If I only wanted to include the items tagged, this works and provides me all the VM Instances that ARE tagged.
But since I want the opposite, the key_value in the screenshot below is intentionally left blank right now as I can't seem to put anything in it that works as I need.
Any help would be greatly appreciated.
Ok, I figured this out. I'm posting this in here because I saw numerous threads about people asking how to do this and never receiving an answer. The main problem was adding a where clause in the first table.
Because I was looking for servers not being discovered, I also re-ordered the tables. I also made the "where" clause a single item on each one.
From there, I would go into the view and filter as necessary (Install Status, last update day, either not tagged at tall or doesn't contain the tags I want to ignore)
Ok, I figured this out. I'm posting this in here because I saw numerous threads about people asking how to do this and never receiving an answer. The main problem was adding a where clause in the first table.
Because I was looking for servers not being discovered, I also re-ordered the tables. I also made the "where" clause a single item on each one.
From there, I would go into the view and filter as necessary (Install Status, last update day, either not tagged at tall or doesn't contain the tags I want to ignore)
