4 Replies Latest reply on Feb 12, 2020 8:51 AM by Ryan Nicosia

    SQL Query for CMDB data

    Ryan Nicosia
      Share This:

      I've got a requirement to provide a SQL query that displays a Computer System CI and all of the associated IP addresses in a comma delimited list.  I've been trying to figure this out for a couple of days and this is close as I can get.  The problem is it is slow and the output for the field contains a bunch of extra things like the Table Name and extra t hings like

       

      <T1693><IP>1.1.1.1</IP><IP>1.2.3.4, etc,etc

       

      Since Computer System has a 1 to many relationship with IP End points, I'm trying to find a query that can just pull back a list of all the IP's for a particular Computer System record

       

      Here is the query I've got thus far

       

       

      SELECT T712.C200000020,

             T712.C678000205,

             T712.C179

                 , (SELECT T693.C200000020

            FROM T693

            LEFT JOIN T693 AS I ON I.C400131200 = T712.C179

                        FOR XML PATH(''), ROOT('T693'), TYPE)

             FROM T712 WHERE T712.C200000020 = 'server.hostname'

       

       

      Anybody have any ideas how to get this working?

        • 1. Re: SQL Query for CMDB data
          Ryan Nicosia

          Made this adjustment but it is spitting out way more IP's than it should

           

          SELECT T712.C200000020,

                 T712.C678000205,

             T712.C179

                     , (SELECT T693.C200000020 + ',' AS 'data()'

                            FROM T693

            LEFT JOIN T693 AS I ON T712.C179 = I.C400131200

                            FOR XML PATH(''), TYPE)

                 FROM T712 WHERE T712.C200000020 = 'server.name'

                 AND T712.C400127400 = 'BMC.ASSET'

          • 2. Re: SQL Query for CMDB data
            Ryan Nicosia

            35 reviews and no responses.  Surely somebody has an idea.

            • 3. Re: SQL Query for CMDB data
              Mohammad Rehman

              Hi Ryan,

               

              Would you please share the form names of 712 and 693 and field name of the ids used in Query?

              Also how you want the output?

              If you can mock the output would be fine.

               

              Thanks

              Mohammad

              • 4. Re: SQL Query for CMDB data
                Ryan Nicosia

                For anybody needing to do this.  This is the query I got to work.  This is from Computer System and ProtocolEndpoint forms where you want to pull 1 computer system and the related IP addresses into a single row of data with IP's being in a comma separated list.

                 

                T712 is the Computer System form and T693 is the Protocol Endpoint form.

                 

                SELECT DISTINCT ST2.C200000020 AS Name,

                    SUBSTRING(

                        (

                           SELECT ','+ST1.C200000020 AS [text()]

                           FROM dbo.T693 ST1

                           WHERE ST1.C400131200 = ST2.C179 AND ST1.C301141500 = '2'

                           ORDER BY ST1.C179

                           FOR XML PATH ('')

                         ), 2, 1000) [IPs]

                FROM dbo.T712 ST2 WHERE ST2.C400127400 = 'BMC.Asset'

                2 of 2 people found this helpful