SQL Examples

You should only write SQL queries against the database views that are documented under dist/etc/views on the TADDM server. Never write SQL queries against the tables themselves as they are subject to change without warning.

The following are some example SQL queries against the TADDM data model.

How to pipe .sql files to dbquery.sh

You can take the queries below and create a .sql file on a Linux TADDM server and use the following command to execute it with the dbquery tool.

dist/bin/dbquery.sh -q -c -u administrator -p collation "`cat ~/vmware.sql`"

Querying XML Config Files

The following example shows how to query XML config files from a WebSphere cell.

SELECT T1.NAME_C as cellname,
T1.GUID_C AS CELLGUID,
case when T2.EnforceJava2Security_c = 0 then 'false'
     when T2.EnforceJava2Security_c = 1 then 'true'
 else 'unknown' end as EnforceJava2Security,
 -- if there are cases where there is a newline instead of the first <xml> tag this will crap out and die
XMLCAST (
    XMLPARSE (
      DOCUMENT CAST (
        SECURITY_XML.CONTENT AS BLOB
      ) 
      PRESERVE WHITESPACE
    ) as XML
  ) AS SECURITY_XML_CONTENTS,
XMLCAST (
    XMLPARSE (
      DOCUMENT CAST (
        CLU_RESOURCES_XML.CONTENT AS BLOB
      ) 
      PRESERVE WHITESPACE
    ) as XML
  ) AS CLU_RESOURCES_XML_CONTENTS
FROM
BB_WEBSPHERECELL98_V T1
LEFT OUTER JOIN BB_WEBSPHEREGLOBALSECURIT74_V T2 ON T1.PK_C = T2.PK__PARENTWEBSPHINGS_FF343DFDC
LEFT OUTER JOIN (
    SELECT T2.PK__JDOID_C, T3.CONTENT_C AS CONTENT
    FROM
    BB_WEBSPHERECELENTS_40AF1776J T2,
    BB_LOGICALCONTENT42_V T3
    WHERE T3.PK_C = T2.PK__CONFIGCONTENTS_C
    AND T3.FIXEDPATH_C LIKE '%/security.xml'
) SECURITY_XML ON T1.PK_C = SECURITY_XML.PK__JDOID_C
LEFT OUTER JOIN (
    SELECT T2.PK__JDOID_C, T3.CONTENT_C AS CONTENT
    FROM
    BB_WEBSPHERECELENTS_40AF1776J T2,
    BB_LOGICALCONTENT42_V T3
    WHERE T3.PK_C = T2.PK__CONFIGCONTENTS_C
    AND T3.FIXEDPATH_C LIKE '%/clusters/%/resources.xml'
) CLU_RESOURCES_XML ON T1.PK_C = CLU_RESOURCES_XML.PK__JDOID_C

Extended attributes (DB2)

The following example shows how to query and parse out the XA_C attribute that contains the XML for the extended attribute. You can follow this pattern to pick out multiple extended attribute values for an instance. In this example, there are 2 extended attributes for AppServer; myXA1 and myXA2. The LEFT OUTER JOIN is used so that records where XA_C is null will still be returned. This has only been tested with DB2 as the backend database.

SELECT
    A1.GUID_C,
    XA.MYXA1,
    XA.MYXA2
FROM
    BB_APPSERVER6_V A1
    LEFT OUTER JOIN XMLTABLE ('$d/xml' passing A1.XA_C as "d"
        COLUMNS
        MYXA1    VARCHAR(30)    PATH 'attribute[@name="myXA1"]',
        MYXA2    VARCHAR(30)    PATH 'attribute[@name="myXA2"]',
    ) AS XA ON 1=1

Working with LASTMODIFIEDTIME_C (DB2)

The following example is a query that takes a date string and converts it epoch.

SELECT * FROM BB_COMPUTERSYSTEM40_V WHERE DATE(TIMESTAMP('2011-02-18 00:00:00')) > DATE(LASTMODIFIEDTIME_C)

All network switches

SELECT SWITCH.* FROM
 BB_UNITARYCOMPUTERSYSTEM24_V SWITCH,
 BB_COMPUTERSYSTIONS_6C816818J SWITCH_FUNCTION,
 BB_FUNCTION34_V BRIDGE
WHERE
 SWITCH.PK_C = SWITCH_FUNCTION.PK__JDOID_C
 AND BRIDGE.PK_C = SWITCH_FUNCTION.PK__FUNCTIONS_C
 AND BRIDGE.NAME_C = 'Bridge'

All network switches with all connected systems

This is a query that will give the cable map for discovered switches and routers and will provide port to port information if available.

SELECT DISTINCT SWCSOS.*,
                IPTIP.STRINGNOTATION_C AS IP,
                VLAN_INTF.VLANID_C,
                VLAN_INTF.TRUNKINTERFACE_C
FROM   (SELECT SWCSINFO.*,
               OS.NAME_C AS OSNAME
        FROM   (SELECT SW.DISPLAYNAME_C          AS SWITCHNAME,
                       INTR.NAME_C        AS PORTNAME,
                       INTR.PK_C          AS PORTPK,
                       CSINTR.HWADDRESS_C AS CSMAC,
                       CS.NAME_C          AS CSNAME,
                       CSINTR.NAME_C      AS INTRNAME,
                       CSINTR.PK_C        AS INTRPK,
                       CS.VIRTUAL_C,
                       CS.PK__OSRUNNING_C AS OSRUNNING,
                       CS.PK_C            AS CSPK
                FROM   BB_COMPUTERSYSTEM40_V SW,
                       BB_L2INTERFACE41_V INTR,
                       BB_SEGMENTJDO_L2INTERFACES_J J1,
                       BB_SEGMENT21_V SEG,
                       BB_SEGMENTJDO_L2INTERFACES_J J2,
                       BB_L2INTERFACE41_V CSINTR,
                       BB_COMPUTERSYSTEM40_V CS
                WHERE  ( SW.TYPE_C = 'Bridge'
                          OR SW.TYPE_C = 'Router' )
                       AND SW.PK_C = INTR.PK__PARENTL2INTERFACE_C
                       AND INTR.PK_C = J1.PK__L2INTERFACES_C
                       AND J1.PK__JDOID_C = SEG.PK_C
                       AND SEG.PK_C = J2.PK__JDOID_C
                       AND J2.PK__L2INTERFACES_C = CSINTR.PK_C
                       AND CSINTR.PK__PARENTL2INTERFACE_C = CS.PK_C
                       AND
                       -- DON'T SHOW THE SWITCH CONNECTED TO ITSELF
                       CS.GUID_C != SW.GUID_C) SWCSINFO
               LEFT OUTER JOIN BB_OPERATINGSYSTEM62_V OS
                            ON SWCSINFO.OSRUNNING = OS.PK_C) SWCSOS
       LEFT OUTER JOIN (SELECT VLAN.VLANID_C,
                               L2INTF.PK_C AS L2INTF_PK,
                               VLANINTF.TRUNKINTERFACE_C
                        FROM
                               BB_VLANINTERFACE56_V VLANINTF,
                               BB_VLAN89_V VLAN,
                               BB_L2INTERFACE41_V L2INTF
                        WHERE  VLANINTF.PK__VLAN_C = VLAN.PK_C AND
                               VLANINTF.PK__INTERFACE_C = L2INTF.PK_C) VLAN_INTF
                    ON VLAN_INTF.L2INTF_PK = SWCSOS.PORTPK
       LEFT OUTER JOIN (SELECT PK__L2INTERFACE_C,
                               PK__SYSTEM_C,
                               IPADDR.STRINGNOTATION_C
                        FROM   BB_IPINTERFACE96_V IPINTR,
                               BB_IPADDRESS73_V IPADDR
                        WHERE  IPADDR.PK_C = IPINTR.PK__IPADDRESS_C) IPTIP
                    ON SWCSOS.INTRPK = IPTIP.PK__L2INTERFACE_C
                       AND SWCSOS.CSPK = IPTIP.PK__SYSTEM_C
ORDER  BY SWCSOS.SWITCHNAME, SWCSOS.PORTNAME

L2 discovered ComputerSystem CIs with the same FQDN

This is a query that can be used to find potential duplicates.

SELECT T1.guid_c      AS FIRST_GUID,
       T2.guid_c      AS SECOND_GUID,
       T1.signature_c AS FIRST_SIG,
       T2.signature_c AS SECOND_SIG,
       T1.fqdn_c
FROM   bb_computersystem40_v T1,
       bb_computersystem40_v T2,
       bb_operatingsystem62_v OS1,
       bb_operatingsystem62_v OS2
WHERE  T1.fqdn_c = T2.fqdn_c
       AND T1.guid_c != T2.guid_c
       AND T1.pk__osrunning_c = OS1.pk_c
       AND T2.pk__osrunning_c = OS2.pk_c
       AND OS1.osconfidence_c = 100
       AND OS2.osconfidence_c = 100