0 Replies Latest reply on Aug 11, 2011 5:04 PM by egarlepp

    Tip/Trick - Set Patch Category In LANDesk DB To Show In Console

    egarlepp Employee

      This applies to v8.8 and V9 up to SP2.

       

      This is a simple tip/trick to populate the Category field in the LANDesk DB to show up in the console when viewing the scan/detected areas.  By default LANDesk includes the column Category in the LANDesk console,for security rules and such, but does not populate it with any information for patches.  This field has a column and data field in the LANDesk DB in the Vulnerability table.

       

      What I figured is why not populate this with pertinent information like whether a patch is Application/OS/LANDesk/etc... related so that you can filter and sort as needed.

       

      The basic premise behind this tip/trick is to create a SQL Job that runs nightly or when you need it to look for specific information for each vulnerability and then populate the Category field appropriately.  There will be 2 steps in the job, the first sets all the patches to Operating System and the second then looks for and updates the other categories.  I choose to do it this way as it is much easier to filter for the other categories then for OS.  Ultimately,  you can use this code to set the categories to be whatever you want and whatever you need them to be.

       

      There will need to 2 steps in this job for each LANDesk DB.

       

      Step 1 - Set all patches to Operating System first. Be sure to select the DB of your LD core.  Also in teh advanced section, be sure to set the "On Success Action" to be "Go to next step"

       

      UPDATE Vulnerability SET Category = 'Operating System'
      

       

      Step 2 - Filter our and update patches for other categories.  Again be sure to select the DB of your core.  You may need to review and update code for your specific needs.  This script needs to be updated as new vuls are added and new vendors.

      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%Office %' OR Title LIKE '% Word %' OR Title LIKE '%Outlook%' OR Title LIKE '%Excel%' OR Title LIKE '%Powerpoint%' OR Title LIKE '% Project %' OR Title LIKE '%Visio %' OR Title LIKE '%Word 2000%' OR Title LIKE '%Word 2003%' OR Title LIKE '%Word 2007%' OR Title LIKE '%Word XP%'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%Acrobat%' OR Title LIKE '%Adobe%' OR Title LIKE '%Communicator%' OR Title LIKE '%Itunes%' or title like '% Access Snapshot Viewer%'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%RealVNC%' OR Title LIKE '%Quicktime %' OR Title LIKE '%Skype%' OR Title LIKE '%Winzip%'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%Firefox%' OR Title LIKE '%JRE,JDK%' OR Title LIKE '%Wireshark%' OR Title LIKE '%Sophos%'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%Symantec%' OR Title LIKE '%Norton%' OR Title LIKE '%Opera%' OR Title LIKE '%Sharepoint%' OR Title LIKE '%Winamp%'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%McAfee%' OR Title LIKE '%Realplayer%' OR Title LIKE '%Thunderbird%' OR Title LIKE '%Yahoo%'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '% Flash %' OR Title LIKE '%Shockwave%' OR Title LIKE '% Publisher%' OR Title LIKE '%Services for UNIX%'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%Pidgin%' OR Title LIKE '%SQL %' or Title LIKE '%Crystal Reports %' OR Title LIKE '%Visual Studio%' OR Title LIKE '% SQLXML %' OR Title LIKE '% Windows Defender %'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%VMWare%' OR Title LIKE '%MSDE%' or title like '%Flash Player %' or title like '% Access 2003%' or title like '% Access 2007%'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%Mozilla%' OR Title LIKE '%Ask Toolbar%' OR Title LIKE '%Lenovo%'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%Adium%' OR Title LIKE '%AOL %' or title like '%Visual FoxPro%'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%JAVA%' OR Title LIKE '%RealNetworks%' or Title LIKE 'Word %'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%Nullsoft%' OR Title LIKE '%Eudora%' or title like '%BizTalk Server%' or title like '% ISA Firewall%' or title like '% ISA Server%'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%Ethereal%' OR Title LIKE '%Thinkvantage%' OR Title LIKE '%Desktop Search%' OR Title LIKE '%IASC%'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%Foxit%' OR Title LIKE '%Google%' OR Title LIKE '%ServerProtect %' OR Title LIKE '%Thinkvantage%'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%ICQ%' OR Title LIKE '%J2SE%' OR Title LIKE '%Exchange %' OR Title LIKE '%Exchange Server%' OR Title LIKE '%ISA Server%' OR Title LIKE '%Host Integration Server%' OR Title LIKE '%Content Management Server%' OR Title LIKE '%Commerce Server%'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%Ethereal %' OR Title LIKE '%Trillian %' OR Title LIKE '% Works %' OR Title LIKE '%Virtual PC %' OR Title like '% Silverlight %'
      UPDATE Vulnerability SET Category = 'Application' WHERE Title LIKE '%System Center Configuration Manager%' OR Title LIKE '%InfoPath %' OR Title LIKE '%Communications Server%' OR Title LIKE '%OneNote %' OR Title LIKE '%System Center Data Protection Manager%' OR Title LIKE '%Forefront%' OR Title LIKE '%Tortoise%'
      UPDATE Vulnerability SET Category = 'Application' WHERE Vul_ID LIKE 'SMS-10-Update%' OR Vul_ID LIKE '%_Office%' OR Vul_ID LIKE 'CAN-%'
      
      
      UPDATE Vulnerability SET Category = 'DST Updates' WHERE Title LIKE '%Daylight Saving Time%' OR Title LIKE '%time zone update%'
      
      
      UPDATE Vulnerability SET Category = 'LANDesk' WHERE Title LIKE '%LANDesk%' 
      UPDATE Vulnerability SET Category = 'LANDesk' WHERE Vul_ID LIKE 'LD-%' OR Vul_ID LIKE 'LDDAV%'  OR Vul_ID LIKE 'CF0000%'  OR Vul_ID LIKE 'LD88-%'
      
      
      UPDATE Vulnerability SET Category = 'Drivers' WHERE Title LIKE 'Intel Pro%'
      UPDATE Vulnerability SET Category = 'Drivers' WHERE Title LIKE '%Realtek HD Audio%'
      UPDATE Vulnerability SET Category = 'Drivers' WHERE Vul_ID LIKE 'Dell%'
      
      
      UPDATE Vulnerability SET Category = 'Operating System' WHERE Title LIKE '%Wi-Fi%' OR Title LIKE '%access violation%' OR Title LIKE '%cannot access%' OR Title LIKE '%INACCESSIBLE%' OR Title LIKE '%Internet Explorer 7%' or title like '%Java VM JDBC %'
      
      
      UPDATE Vulnerability SET Category = 'Application' WHERE Vendor LIKE 'Yahoo' OR Vendor LIKE 'Wireshark%' OR Vendor LIKE 'Winzip%' OR Vendor LIKE 'VMware%'
      OR Vendor LIKE 'Symantec%' OR Vendor LIKE 'Trend Micro%' OR Vendor LIKE 'Sun%' OR Vendor LIKE 'Skype%' OR Vendor LIKE 'Real%' OR Vendor LIKE 'Pidgin%' 
      OR Vendor LIKE 'Adium%' OR Vendor LIKE 'Adobe%' OR Vendor LIKE 'AOL%' OR Vendor LIKE 'CERULEAN %' OR Vendor LIKE 'Foxit %' OR Vendor LIKE 'Google%' 
      OR Vendor LIKE 'HP%' OR Vendor LIKE 'IAC Search%' OR Vendor LIKE 'ICQ %' OR Vendor LIKE 'McAfee%' OR Vendor LIKE 'QUALCOMM %' OR Vendor LIKE 'Opera%' 
      OR Vendor LIKE 'OpenOffice%' OR Vendor LIKE 'Nullsoft%' OR Vendor LIKE 'Mozilla%' OR Vendor LIKE 'mozilla%'

       

      Hope this helps someone out there.  It is simple but gets the job done, let me know if you have any questions or comments.