Tip/Trick - Show Extra Patch Related Columns In System Inventory

Version 1

    Works and tested with v9 SP2 ONLY.  This may work with V9 gold and SP1, but has not been tested.  Will not work with v8.8 or earlier.

     

    The premise behind this Tip/Trick is that certain fields in the LANDesk DB are not available within the LANDesk inventory of a system preventing granular queries and added fields in reports.  These fields are added to the "Detected Patch and Compliance Definitions" section of a systems inventory.  The nice thing about this addition to the inventory is that it is immediate and does not require the systems to scan in or to change the LDAPPL in anyway.  The other nice thing is that the data is not custom data but is set up as part of the DB schema and is modeled.  If you have any questions on what modeling data is or how to do so review this - http://community.landesk.com/support/docs/DOC-2538 The doc is for 8.8 but will work for v9 SP2 as well.

     

    When you model data you basically set the DB and the schema of LANDesk to recognize the data throughout the console and the inventory.

     

    The fields that are added to the "Detected Patch and Compliance Definitions" inventory area

    • Patch is set to Autofix
      • Normally come in as 1 or 0
      • We connect them to a table that converts the 1/0 to Yes/No so that it can be better understood
    • Patch is part of Compliance Group
      • Normally come in as 1 or 0
      • We connect them to a table that converts the 1/0 to Yes/No so that it can be better understood
    • Patch Vendor
    • Patch Category
    • Patch published date

     

    These fields can be sliced and dices multiple ways for reports and queries for establishing compliance and a setting enterprise maintenance.

     

    Secondary Datamart.xml file.  You should create a company specific Datamart_my.xml file that you can add all your modeled data to going forward.

    This file gets added into the LANDesk DB by running Coredbutil.exe with some specific switches.

    • coredbutil /xml=datamart_my.xml
    • Then click build components and it will follow the XML below to create what is needed
    • The first 2 sections create the supporting tables for converting the 0/1 to Yes/No for each field compliance and autofix
    • The 3rd section updates the DB view and makes the new fields available in the inventory and console.  You can name(displayname) the fields whatever you want in the console to show.  This was from the existing DatamartPM.xml file; changes and updates were made to allow added data.
    • The 4th section is where some more advanced SQL stuff takes place like joins and matching fields up.  Do not make changes to these as it may cause undesired results. This was from the existing DatamartPM.xml file; changes and updates were made to allow added data.
    <?xml version="1.0" encoding="utf-8" ?>
    <schema version="9.00.2.0" type="tables">
    
      <table name="LD_Integer">
        <column name="LDInteger_Idn" type="Int" identity="Yes" null="No" />
        <column name="YesNo_ID" type="Int" />
        <column name="Autofix_Name" type="Varchar(50)" />
        <primaryKey name="XPKLD_Integer">
          <primaryKeyColumn column="LDInteger_Idn" />
        </primaryKey>
        <values unique="Autofix_Name">
          <value YesNo_ID="0" Autofix_Name="No" />
          <value YesNo_ID="1" Autofix_Name="Yes" />
        </values>
      </table>
      
       <table name="LD_Integer2">
        <column name="LDInteger2_Idn" type="Int" identity="Yes" null="No" />
        <column name="YesNo2_ID" type="Int" />
        <column name="Compliance_Name" type="Varchar(50)" />
        <primaryKey name="XPKLD_Integer2">
          <primaryKeyColumn column="LDInteger2_Idn" />
        </primaryKey>
        <values unique="Compliance_Name">
          <value YesNo2_ID="0" Compliance_Name="No" />
          <value YesNo2_ID="1" Compliance_Name="Yes" />
        </values>
      </table>
    
        <table name="CVDetected" desc="" metaAdd="Yes" equiJoin="No" view="CVDetectedV" displayName="Detected Patch and Compliance Definitions" parentRelation="Computer" parent="Computer" tableType="6" rollupIdentity="Yes" image="vulnerability18.bmp">
            <column name="CVDetected_Idn" type="Int" identity="Yes" null="No" />
            <column name="Computer_Idn" type="Int" null="No" />
            <viewcolumn name="Vulnerability_Idn" type="Int" null="No" displayName ="Idn" attributeTableType="99" />
            <viewcolumn name="Patch" type="Varchar(255)" displayName="Patch Required" attributeTableType="99" />
            <viewcolumn name="Reason" type="Varchar(1500)" displayName="Reason" attributeTableType="99" />
            <viewcolumn name="Expected" type="Varchar(1500)" displayName="Expected" attributeTableType="99" />
            <viewcolumn name="Found" type="Varchar(1500)" displayName="Found" attributeTableType="99" />
            <viewcolumn name="DateDetected" type="DateTime" displayName="Date Detected" attributeTableType="99" />
            <viewcolumn name="PatchInstallSucceeded" type="Int" displayName="Patch Install Succeeded" attributeTableType="99" />
            <viewcolumn name="PatchDetected" type="Int" displayName="Patch Currently Installed" attributeTableType="99" />
            <viewcolumn name="VulType" type="Varchar(255)" displayName="Type" attributeTableType="99" />
            <viewcolumn name="VulSeverity" type="Varchar(255)" displayName="Severity" attributeTableType="99" />
            <viewcolumn name="Vul_ID" type="Varchar(50)" displayName="ID" PK="Yes" attributeTableType="99" />
            <viewcolumn name="Lang" type="Char(6)" displayName="Lang" attributeTableType="99" />
            <viewcolumn name="Title" type="Varchar(255)" displayName="Title" attributeTableType="99" />
            <viewcolumn name="Autofix_name" type="Varchar(50)" displayName="Autofix Enabled" attributeTableType="99" />
            <viewcolumn name="Compliance_name" type="Varchar(50)" displayName="Security Compliance" attributeTableType="99" />
            <viewcolumn name="Vendor" type="Varchar(255)" displayName="Vendor" attributeTableType="99" />
            <viewcolumn name="PublishDate" type="DateTime" displayName="Released By Vendor" attributeTableType="99" />
            <viewcolumn name="Category" type="Varchar(50)" displayName="Category" attributeTableType="99" />
        <primaryKey name="XPKCVDetected">
                <primaryKeyColumn column="Computer_Idn" />
                <primaryKeyColumn column="CVDetected_Idn" />
            </primaryKey>
            <index name="CVDetected_IdnIdx">
                <indexColumn column="CVDetected_Idn" />
            </index>
            <foreignKey name="CVDtoVulidnFK" foreignTable="Vulnerability">
                <foreignKeyColumn column="Vulnerability_Idn" foreignColumn="Vulnerability_Idn" />
            </foreignKey>
            <foreignKey name="CVDtoCompIdnFK" foreignTable="Computer">
                <foreignKeyColumn column="Computer_Idn" foreignColumn="Computer_Idn" />
            </foreignKey>
            <index name="CVDVulnerabilityIdnIdx">
                <indexColumn column="Vulnerability_Idn" />
            </index>
            <index name="XIFCVDetectedComputer_Idn">
                <indexColumn column="Computer_Idn" />
            </index>
            <index name="XCVDetectedPatch">
                <indexColumn column="Patch" />
            </index>
            <index name="CVDateDetectedIdx">
                <indexColumn column="DateDetected" />
            </index>
        </table>
    
        <view name="CVDetectedV" columns="(CVDetected_Idn, Computer_Idn, Vulnerability_Idn, Patch, Reason, Expected, Found, DateDetected, PatchInstallSucceeded, PatchDetected, VulType, VulSeverity, Vul_ID, Lang, Title, Autofix_name, Compliance_Name, Vendor, PublishDate, Category)" desc="">
            Select cvd.*, vt.DisplayName as VulType, vs.DisplayName as VulSeverity, v.Vul_ID, v.Lang, v.Title, LD_Integer.Autofix_Name, LD_Integer2.Compliance_Name, v.Vendor, v.PublishDate, v.Category FROM CVDetected cvd, VulSeverity vs, VulType vt, Vulnerability v, LD_Integer, LD_Integer2 WHERE cvd.Vulnerability_Idn = v.Vulnerability_Idn AND v.Type = vt.Type_ID AND v.Severity = vs.Severity_ID and v.Autofix = LD_Integer.yesno_id and v.Compliance = LD_Integer2.yesno2_id
        </view>
    
    </schema>

    I have attached this as a file as well in XML.  You can copy out the required xml and post to your own XML datamart file.

     

    Test this out on your DEV cores first before unleashing it into production.  We have implemented this on 5 production cores globally supporting 20k+ nodes.

     

    This document was generated from the following discussion: Tip/Trick - Show Extra Patch Related Columns In System Inventory