CA Client Automation

  • Private Community
 View Only

  • 1.  sql patch managment

    Posted Aug 30, 2018 12:24 PM

    Hi

     

    I am trying to obtain the same information found on the website in SQL
    Patch Management Center - CA Technologies 
    because the integration of the UPM with the CA Service Desk Manager does not give many
    details about the patch.
    I have thought about seeing if it is possible that more details are included when
    registering the RFC as it is shown in the last URL as well as if it is possible that
    the computers that are affected by the patch will be put all this automatically. 
    otherwise, I would be creating an additional document in Excel with the first part of
    the information if it is possible to obtain it by the SQL and the list of equipment
    to which said patch is applied. 

    someone could guide me...


  • 2.  Re: sql patch managment

    Posted Aug 30, 2018 02:01 PM

    This information is stored in the ca_install_package table in the database.

     

    Using your example above,

    select release_notes from ca_install_package where ipkg_name like '%MS - Win 10 1803 64Bit - Cumulative Update KB4338819 v1807.00%'

     

    The format is not nearly as elegant as the web site, but all the content appears to be there.



  • 3.  Re: sql patch managment

    Posted Aug 30, 2018 05:28 PM

    Hi Brian

     

    this table don't include fields

    - Severity

    - Impact

    - Published Date

     

    you have joined table 



  • 4.  Re: sql patch managment
    Best Answer

    Broadcom Employee
    Posted Sep 04, 2018 05:15 AM

    Hi Jonathan,

     

    Here is a SQL query which may help :

     

    SELECT
    i.ipkg_name 'Name',
    c.company_name 'Vendor',
    ISNULL(d.lang_code,'') 'Language',
    dateadd(ss, d.creation_date,convert(datetime,'19700101')) 'Published Date',
    dateadd(ss, d.last_update_date,convert(datetime,'19700101')) 'Last Update Date',
    CASE d.severity
    WHEN 4 THEN 'Low'
    WHEN 3 THEN 'Medium'
    WHEN 2 THEN 'High'
    WHEN 1 THEN 'Critical'
    ELSE ''
    END 'Severity',
    CASE d.impact
    WHEN 2 THEN 'Availability'
    WHEN 3 THEN 'Functionnal'
    WHEN 4 THEN 'Performance'
    WHEN 5 THEN 'Security'
    WHEN 6 THEN 'Usability'
    ELSE ''
    END 'Impact',
    d.description 'Description',
    i.release_notes
    FROM ca_install_package i, ca_software_def d, ca_company c
    WHERE i.sw_def_uuid=d.sw_def_uuid and c.company_uuid=d.manufacturer_uuid
    ORDER BY d.creation_date DESC

     

     

    Remark :

    'Published Date' and 'Last Update Date' may be different as it is the date when the row has been inserted or updated in our mdb database (using CIC).

     

    Thanks.

    Regards,

    Jean-Yves



  • 5.  Re: sql patch managment

    Posted Sep 04, 2018 11:52 AM

    thanks this SQL Query resolve my problem