question Re: CMDB Query Builder - Technical Service Offering in Common Service Data Model forum

CMDB Query Builder - Technical Service Offering

CW7 — Mon, 10 Apr 2023 10:37:18 GMT

Hi,

I am trying to use the CMDB Query builder to create a dynamic query that can be used in a Technical Service Offering.

The logic includes 2 CI classes; Server and MSFT SQL Instance. I am trying to create Technical service offerings for the the different environments and apply to the DB Instance. eg SQL Prod, SQL Test, SQL Dev. These can then have different criticality, support team, etc...

The logic relies on the environment of the server. If the server has a production environment then so will the database instance that runs on that server...

I have created the query however the results cannot be configured to exclude the servers and so cannot be used in in a dynamic CI group. The servers are part of another service offering and are supported by windows platform teams so I dont want to include them. I only want the instances.

The server is required for the logic to work in the query but should not be in the output, I only want the DB instances.

Re: CMDB Query Builder - Technical Service Offering

SN Arch Guy — Thu, 13 Apr 2023 22:23:07 GMT

Try using something like msft sql instance that runs on server whose environment is PRD in the natural language query box at the top of the CMDB Query Builder form. Be sure the rest of the pane is empty. Click the Search button, then click the Run button.

Re: CMDB Query Builder - Technical Service Offering

Srinivas Cheer1 — Fri, 14 Apr 2023 09:45:24 GMT

The MSFT SQL Instance class should be the starting node in your CMDB Query Builder.

The starting node resulted CIs will be the targeted CIs for Dynamic CI Group.

Re: CMDB Query Builder - Technical Service Offering

CW7 — Fri, 14 Apr 2023 12:59:29 GMT

Hi, thanks for your response. The natural language query box does not produce the query, it feels a bit clunky and not really fit for purpose.

I am happy that my query is accurate and produces the correct data (its a simple logic), the problem is the way the results are used in dynamic CI groups. I dont want to see servers, just db instances.

Re: CMDB Query Builder - Technical Service Offering

CW7 — Fri, 14 Apr 2023 13:03:24 GMT

Hi, Thanks for your response.

It doesn't matter which which is the starting node, when you link the query to a dynamic ci group and select the "view group cmdb cis" it gives me both servers and instances.

Re: CMDB Query Builder - Technical Service Offering

SN Arch Guy — Fri, 14 Apr 2023 13:15:54 GMT

what results did you get with the NLQ? it worked great in my system, and only listed the SQL Server instances; it did not list the servers. it will also show you the query builder it creates, which i haven't tried to recreate outside the NLQ as it uses constructs that i don't know how to recreate through the drag and drop method. but NLQ should work fine for your use case.

Re: CMDB Query Builder - Technical Service Offering

CW7 — Fri, 14 Apr 2023 13:45:09 GMT

I struggled to get useful results but I am new to this functionality though have used TQL CMDB tools extensively.

Can you please share the NLQ string you used (verbatim) so that I can test ? - and hopefully learn to use this new feature.

Re: CMDB Query Builder - Technical Service Offering

Amit Gujarathi — Fri, 14 Apr 2023 13:54:12 GMT

Hi @CW7 ,
I trust you are doing great.

To implement this, we can navigate to the CMDB Query Builder and enter the following query in the natural language query box: "MSFT SQL Instance that runs on server whose environment is PRD". This query specifies that we are looking for MSFT SQL Instances that are associated with servers having a production environment.

After entering the query, it is important to ensure that the rest of the pane is empty before clicking the "Search" button. Once the search results are displayed, we can then click the "Run" button to execute the query.

By using this method, we can ensure that only the MSFT SQL instances that meet our specific criteria are returned in the result set, while excluding the servers themselves. This is particularly useful in situations where we want to create a dynamic CI group or technical service offering that only includes the relevant instances, without including the servers that support them.

If further filtering criteria are required, such as criticality or support team, these can be easily added to the query using the same natural language syntax. This allows us to create highly specific and targeted queries that meet our exact needs.

Re: CMDB Query Builder - Technical Service Offering

SN Arch Guy — Fri, 14 Apr 2023 13:55:13 GMT

you should be able to use the exact string i put in my original post:

msft sql instance that runs on server whose environment is PRD

the only things you may need to change are:

"environment": use the field name from the server CI records that you use to check whether they are production systemsor not.
"PRD": use the field contents that you use, to determine whether a server is a production system or not.

Re: CMDB Query Builder - Technical Service Offering

Srinivas Cheer1 — Fri, 14 Apr 2023 15:27:27 GMT

Hi,

Did you tried the solution what I've given. If not, please make a note on below points.

1. Make DB Instance class as starting node and connect with Server class by applying your filters

2. When you run the CMDB query, results will be displayed for both DB instances and servers.

3. But when you called that CMDB query into the CMDB group and Dynamic CI Group, starting node CIs will be picked and associate to the Dynamic CI Group.

Please note there is an OOB scheduled job runs for every 10min to associate the CIs

I'm confident you can achieve with this, try once.

Thanks!

Re: CMDB Query Builder - Technical Service Offering

CW7 — Mon, 24 Apr 2023 14:01:48 GMT

Hi Amit, thanks for your help.

The NLQ works so long as I don't include the "environment is prod/production". If I add this using the filter I get the exact same query as I made manually. This is not the problem, its a very simple query.

If I associate this query with a dynamic CI group and then display the group members it shows both SQL instances and Servers.

Re: CMDB Query Builder - Technical Service Offering

CMDB Whisperer — Mon, 24 Apr 2023 14:16:35 GMT

This is just how the Dynamic CI Groups/CMDB Groups work. They take ALL of the CIs returned in the Saved Query. For CMDB Groups created with standard filters (which is a common use case) you look for a specific set of CIs of a specified class that meet certain conditions. However, if you use a Saved Query to populate your CMDB Group, then it will include all of the CIs that are in that query.

Re: CMDB Query Builder - Technical Service Offering

Barry Kant — Mon, 24 Apr 2023 14:21:02 GMT

That way you can also use it to group things for modern support/stack support.

Re: CMDB Query Builder - Technical Service Offering

CW7 — Mon, 24 Apr 2023 14:36:56 GMT

Hi Srinivas,

Thanks for your response. I have tested this and both the server and the db instance is updated by the CSDM data sync. The Change/Manged by/Supported by groups are propagated down onto all members of the dynamic group, not just the starting node CIs. Point 3 above is incorrect.

Re: CMDB Query Builder - Technical Service Offering

CW7 — Mon, 24 Apr 2023 14:54:03 GMT

Hi Whisperer,

Yes this seems to be the case after much testing. This is a significant limitation of this functionality IMHO as you can only use it for dynamic group queries containing the class you are querying. This is of no real advantage than using the encoded query on the CMDB group rather than a saved query.

For this use case I've worked out a business rule to populate the environment on the DB instance. I was hoping to use it (for other cases) in conjunction with running processes/installed software in combination with multiple ci classes...

You should be able to specify an output class as you would for generally in DB update queries despite what is in the query logic. Perhaps this will come in the future...

Re: CMDB Query Builder - Technical Service Offering

CMDB Whisperer — Mon, 24 Apr 2023 15:28:38 GMT

I see both potential scenarios. I think the "idea" that I would post (if I had access to post ideas, <ahem>) would be to add the ability to define a CMDB Group using the intersection (not the union) of two or more queries, or to just exclude certain query nodes from the actual results.

While in some cases I might want to use the query relationships to identify certain nodes, and ignore the nodes that I used to perform the query itself, in other cases it does make sense to include all of the nodes in the query. I think this would be a great feature to add to CMDB Query Builder.