Download / Git clone the script from the below URL,

https://github.com/vinothkumarselvaraj/vulnerability-Assessment-patching-Semi-Automation.git

vulnerability_Assessment_patch_pkg_render_v2.py:

This python script will give you the package name list that needs to be updated on the server for patching the server group. The list is obtained by rendering the VA report provided by the infosec team by grouping the column “plugin_text” based on the node_type and then filter the package by removing the duplication.

vulnerability_Assessment_USN_action_v2.py:

This script will give you the ACTION items (Restart or Reboot or Standard system update) required for the given USN number in the document.

Prerequisites for an excel input

1) Add the column “Hostname” in the VA sheet to match the “IP address” column provided in the report.

2) Delete a formula and keep only the value from the cells if any formula used, as this python script will read the cell on its value.

Ref Link:- https://support.office.com/en-us/article/delete-or-remove-a-formula-193dbbed-6fcf-4f07-9119-5acff81b89c5

3) Make sure your workstation has network connectivity to reach https://usn.ubuntu.com/, as the script will do curl call for each USN number given.

4) Your workstation should have python module “openpyxl” installed. [pip install openpyxl]

Script Usage:

$python vulnerability_Assessment_patch_pkg_render.py <node_type>

where node_type is one or more of “‘bmk’, ‘cadf’, ‘ceph’, ‘cfg’, ‘consul’, ‘cpu’, ‘ctl’, ‘des’, ‘dns’, ‘jmp’, ‘kvm’, ‘nal’, ‘ntp’, ‘ntw’, ‘prx’, ‘rmq’, ‘sql’ or ‘all'”

Output sample:

root@workstation01:~/automation_final# ll
total 652
drwxr-xr-x  2 root root   4096 Apr 16 17:01 ./
drwx------ 12 root root   4096 Apr 16 17:01 ../
-rw-r--r--  1 root root 647232 Apr 16 15:15 JPE1_BAVA_full.xlsx
-rw-r--r--  1 root root   3076 Apr 16 16:44 vulnerability_Assessment_patch_pkg_render.py
-rw-r--r--  1 root root   2068 Apr 16 16:32 vulnerability_Assessment_USN_action_v2.py
root@workstation01:~/automation_final# python vulnerability_Assessment_patch_pkg_render.py all

                        =========IMPORTANT==========

1)Add the column "Hostname" in the VA sheet to match the "IP address" column provided in the report.
2) Delete a formula and keep only the value from the cells if any formula used, as this python script will read the cell on its value.
 Ref Link:- https://support.office.com/en-us/article/delete-or-remove-a-formula-193dbbed-6fcf-4f07-9119-5acff81b89c5

                        ===========================

Enter the Excel Document name with full path: JPE1_BAVA_full.xlsx
The below sheets are available in the excel document
[u'BA', u'VA', u'Sheet2', u'MA', u'vjpe1', u'filtered_package', u'Sheet3', u'USN']
Enter the sheet name to load data from: VA
Enter the Column name of hostname entries: F
Enter the Column name of Plugin_text entries: G

===== Execution of vulnerability_Assessment_patch_pkg_render.py will generate a xlsx file with name syntax [VA_Report_with_pkg_filtered__<current_time>] which needs to be given as an input file for vulnerability_Assessment_USN_action_v2.py ============

-rw-r--r--  1 root root 1942718 Apr 16 17:47 VA_Report_with_pkg_filtered__20190416-174728.xlsx
-rw-r--r--  1 root root    3076 Apr 16 16:44 vulnerability_Assessment_patch_pkg_render.py
-rw-r--r--  1 root root    2068 Apr 16 16:32 vulnerability_Assessment_USN_action_v2.py
root@workstation01:~/automation_final# python vulnerability_Assessment_USN_action_v2.py

                        =========IMPORTANT==========

 Make sure your workstation has network connectivity to reach https://usn.ubuntu.com/, as the script will do curl call for each USN number given

                        ===========================

Enter the Excel Document name with full path: VA_Report_with_pkg_filtered__20190416-174728.xlsx
The below sheets are available in the excel document

[u'BA', u'VA', u'Sheet2', u'MA', u'vjpe1', u'filtered_package', u'Sheet3', u'USN']
Enter the sheet name to load data from: VA
Enter the Column name of USN entries: K

==== On successful execution, the script will generate a new xlsx file with the name syntax [VA_Report_with_USN_Action_<Current_time>] which is your final VA report.========