Patch Management Solution

 View Only

{CWoc} Patch Trending SiteBuilder 

Aug 20, 2013 12:13 PM

[END OF "SUPPORT" NOTICE]

Hello everyone, after close to 5 years maintaining various tools around Symantec Connect this legacy is turning to be more of a burden than anything else.

It's still is a great set of tool and they all have their use, but as such I'm not going to maintain them anymore.

The source code for this tool may still change over time, and can be found on Github: https://github.com/somewhatsomewhere?tab=repositories

[/END OF "SUPPORT" NOTICE]

Welcome to the {CWoC} Patch Trending SiteBuilder (v15) download.

Content:

Features

The Patch Trending site builder generates static web-site to visualize Patch Compliance Trending Data stored on a Symantec_CMDB database. It has the the following attributes:

  • A landing page showing:
    • global compliance graphs
    • a bulletin search function
    • link to custom compliance views
  • a dynamic page (html + javascript) to quickly access compliance per bulletin
  • static pages to show updates details per bulletins
  • Custom compliance view, which is a set fully custom pages to show groups of bulletins graphs
  • Troubleshooting pages showing "bottom-10-compliance" by bulletin and "top-10-vulnerable" bulletins, and (from v10) 2 pages to show top 10 movers (by installed count i.e up and vulnerable count i.e. down).

Top

Prerequisites

There are no SQL prerequisites anymore. Run PAtchTrending-8.1.exe /install to install the required stored procedure.

Run "patchtrending-8.1.exe /collectdata" to collect the data. This is the command that should be run daily.

Top

Usage

Here is the complete list of valid invocations. For additional details please check the command line help message below or via "patchtrending-81. /?".

	sitebuilder
	patchtrending-8.1 /?
	patchtrending-8.1 /install
	patchtrending-8.1 /upgrade
	patchtrending-8.1 /version
	patchtrending-8.1 /write-all
	patchtrending-8.1 /collectdata
	patchtrending-8.1 /buildsite

The site layout file format use comma separated value (with or without spaces) with the first entry being the page name and following entries being bulletins to be added on the page. If the bulletin name is invalid no page will be generated.

A sample site-layout page is attached with the executables zip.

Here is the command line help message:

Welcome to the Patch Trending SiteBuilder. Here are the currently supported
command line arguments:

    /buildsite
    
        This option is required if you want the tool to output the Patch 
        Trending site, based on the SiteConfig.txt file.

    /collectdata
    
        Run the trending procedure for each site that is enabled on the
        siteconfig.txt.
    
    /collectionguid=
    
        When this option is used the Patch Trending site will be generated for
        the provided collection guid.

    /install

        This command line installs the pre-requisite stored procedures to the
        Symantec CMDB and terminates.

    /upgrade

        Upgrade the database objects and _data_ from the previous schema to the
        latest version which adds a CollectionGuid in various location.

    /write-all

        This command line will prevent static html and css  files from being 
        written to disk. This allows you to customise the site look and feel
        to better suit your needs.
        

    /? || /help

        This command line prints out this help message and terminates.

    /version

Configuration files names and content:

    SiteConfig.txt:
    
        The site config file contains a a list of line seperated sites that
        should be built upon /buildsite invocations, or for which data will be 
        collected upon /collectdata invocations.
        
        The root site is created under the working directory, whilst other
        sites will be created in a directory named using the site-name field.
        
        Here's a sample file with explanation of each fields:
        
# Lines started with # are not process
#
# Fields descripitions:
# enabled (1 | 0), collectionguid, site-name (comma not allowed), site-description, root-site (only one allowed)
#

1, 01024956-1000-4cdb-b452-7db0cff541b6, AllComputers2, All computers with the Software Update plugin installed, 1
1, 01024956-1000-4cdb-b452-7db0cff541b6, AllComputers, All computers with the Software Update plugin installed, 0
1, b677c36f-8cf8-4c57-aa6f-f11948e128c7, Windows-Servers, All windows servers, 0
1, 66167acf-2484-4244-92fb-a2ffaa5aebd2, Windows-Desktops, All Windows Desktops, 0
0, 3faa8b67-250b-42ad-8186-fe2f49a9e707, Windows-64-bit, All Windows 64-bit systems, 0
1, 8afb27a1-5dc7-43ca-a88c-8391252f5b7b, Windows-32-bit, All Windows 32-bit systems, 0
    
    SiteLayout.txt:

microsoft-2015-april, ms15-031, ms15-032, ms15-033, ms15-034, ms15-035, ms15-036, ms15-037, ms15-038, ms15-039, ms15-040, ms15-041, ms15-042
microsoft-2015-march, ms15-018, ms15-019, ms15-020, ms15-021, ms15-022, ms15-023, ms15-024, ms15-025, ms15-026, ms15-027, ms15-028, ms15-029, ms15-030
microsoft-2015-february, ms15-009, ms15-010, ms15-011, ms15-012, ms15-013, ms15-014, ms15-015, ms15-016, ms15-017
microsoft-2015-january, ms15-001, ms15-002, ms15-003, ms15-004, ms15-006, ms15-007, ms15-008
microsoft-2014-december, ms14-080, ms14-081, ms14-082, ms14-083, ms14-084, ms14-085, ms14-086, ms14-087, ms14-088, ms14-089, ms14-090, ms14-091, ms14-092, ms14-093, ms14-094, ms14-095, ms14-096, ms14-097, ms14-098, ms14-099
microsoft-2014-november, ms14-064, ms14-065, ms14-066, ms14-067, ms14-068, ms14-069, ms14-070, ms14-071, ms14-072, ms14-073, ms14-074, ms14-075, ms14-078, ms14-079
microsoft-2014-october, ms14-056, ms14-057, ms14-058, ms14-059, ms14-060, ms14-061, ms14-062, ms14-063
microsoft-2014-september, ms14-052, ms14-053, ms14-054, ms14-055

Top

Screenshots

Note that you are more than welcome to submit sample data to be included on this page on on the mini-site. This would allow us to spotlight standard trends and how well the software performance (Patch Management Solution) in certain environments. If you want to submit data, please send a direct message to Ludovic Ferre on Symantec Connect or email me (ludovic underscore ferre at symantec.com).

Note! The below sample is an image - but the tool mini-site contains live graphs (using the google API and Javascript) and a complete sample site: 

 

The landing page (1): global compliance + compliance by computer

sample-site-landing2.png

The landing page (2): global compliance + inactive computers

58.png

The landing page (3): global compliance + compliance by computer + inactive computers

58.png

Global view (1):

global_stats.png

Global view (2):

global_compliance.png

Global view (3):

pc-compliance.png

Bulletin view:

ms13-058_bulletinview.png

Updates per bulletin view:

updates_per_bulletin.png

Top

Release Notes

Release 17

Note that version 17 is available for 8.0 and 8.1. If you want to run this in 7.6 or 7.5, please use the Github version and run the build.bat

This is the last and final release of the Patch Trending tool. Note that the DB schema is _not_ compatible with previous versions and will require you to start afresh (i.e. delete all the stored procedures and tables created by the tool, unless the /upgrade feature works for you - but it proved so problematic that the version 17 had been in production a number of year before being pushed out here to all users).

We have a few key features now integrated into the tool:

  • Installation of the SQL stored procedure is now fully automated (/install)
  • Execution of the stored procedure to collect the data is also automaed (/collectdata)

Now, going back to the potential issues with /upgrade. If it doesn't work you'll have to stored your existing data somewhere else in the CDMB (rename the table) and possibly restore the data once the DB schema has been upgrade. It's not all that hard after all :D.

Here is some SQL code to help you start from scratch (note that this will delete _all_ existing trending objects and the contained data):

drop table TREND_InactiveComputerCounts
drop table TREND_InactiveComputer_Current
drop table TREND_InactiveComputer_Previous
drop table TREND_WindowsCompliance_ByComputer
drop table TREND_WindowsCompliance_ByUpdate

drop table TREND_InactiveComputerCounts_old
drop table TREND_InactiveComputer_Current_old
drop table TREND_InactiveComputer_Previous_old
drop table TREND_WindowsCompliance_ByComputer_old
drop table TREND_WindowsCompliance_ByUpdate_old

drop procedure spTrendInactiveComputers
drop procedure spTrendPatchComplianceByComputer
drop procedure spTrendPatchComplianceByUpdate

 

Release 15

This release contains a major codefix, a minor codefix and two important new features and a minor CLI change:

  • Code fix (1): Modified the getbulletin.html page to ensure it loads charts properly under various Internet Explorer versions (tested on Version 8, 9 and 10)
  • Code fix (2): Modified getbulletin.html to verify whether trending data exists or not for the requested entry. If not the message 'No data is available...' is displayed.
  • Feature (1): Added command line option /write-all to prevent the following static pages from being over-written with each site builder invocation (i.e. they will only be overwritten if you invoke 'sitebuilder.exe /write-all'):

    • inactive-computers.html
    • compliance-by-computer.html
    • getbulletin.html
    • webpart-fullview.html
    • menu.css
    • help.html
    • javascript/helper.js

    You will notice that this feature include the menu.css. This will allow you to customise the look and feel of the site without loosing your work in between all execution. The same is true for the html pages, as you can now customise them further without the risk of loosing them.

  • Feature (2): Added a new html page name 'webpart-fullview.html'. This page is a copy of getbulletin.html without the site navigation. It is designed to be used inside the SMP console right-click actions inside a virtual window.
  • CLI change: Added a standard message to display all valid option when invoking the executable with the help paremeter (/? or --help)

Release 14

Adding the stored procedure code inside the site builder to simplify the installation process. The command line invocation is simple: 'sitebuilder.exe /install'.

Note! This will reset the stored procedures to default if they were customized.

Release 13

Added some information in the help section. Also generalized the menu to all pages and changes some of the pages linking. One important feature is that the site layout file is now optional, as the site navigation does not depend on customised pages. Also fixed a fair few problems.

Release 12

Version 12 is here with massive amount of changes. A full release note article will be published soon, but here's a short list of additions / improvements: all dates are not ISO based and displayed on the graphs using the MMM dd (for example 2013-07-14 is displayed Jul 14). We have a new site layout that lists all Microsoft bulletins by month, all the way to January 2009, we now have a site map, headers (linked or not) on all stub pages, a navigation tool, a help center (empty for now), we filter out superseded / inactive updates / bulletins from the site, we added a Compliance by Computer page that use a range selector and we have used the same range selector in the bulletin / update page (getbulletin.html).

Release 11

Added Inactive computer trending pages. One page is added to the custom compliance view, and if the data exist a graph is added to the landing page, beside the compliance by computer graph if you have this trending enable, or on its own (see the 2 screenshots added above).

Release 10

Added two troubleshooting pages to list the top 10 bulletins with most changes up (net increase in installed updates)  and down (net increase in vulnerable count). Also took some times to re-order the html pages generated. In this manner the browser will display the html content before it tries to build up the graphs in javascript. Finally I added page title to all generated html pages for additional clarity on the site.

Release 9

Fixed the landing page search function. It will now only redirect to the getbulletin.html page if we can find data for the user input (bulletin name).

Release 8c

Added compliance by computer graphics. This is a single graphs that shows on the landing page if you have enabled Compliance by Computer trending reports (awaiting release here on Connect). The graphs is of Candlestick type and shows data as illustrated above. With enough trending done you will see single line going thru the boxes. This is because we display the historical low, histroical high and changes since the previous data capture.

Note that you can use this version without having the Compliance by Computer report running, as this is an optional add-on.

Release 6c

Fixed a problem with Internet Explorer support. The pages now render properly for IE 8.0 and above. It may work with IE 7 but was not tested yet.

Release 6b

Switched the compliance data to be computed from the installed versus applicable datasets, thus reducing the amount of SQL queries executed by half.

Release 6

Introduced vulnerable count on the Installed vers Applicable graphs. This gives us 3 lines (curves) that are easy to comprehend as you can see from the sample above.

Release 5b

Corrected some performance issues from the previous build and added instrumentation. The site builder now logs entry in the Altiris Logs and will indicate the count of html and js pages generated as well as the count of SQL queries it ran. During the performance issue troubleshooting we considered using a single Databasecontext entry but this was a wrong lead. The problem was database performance as the use of code based stop watch indicated. This was fixed by a non-clustered index on the table to keep track of data by updates.

Release 5

Refactored the graph per update generation. Added the link to the bulletin update page on the bulletin view and on the various aggregate pages.

Release 4

Introduced the Updates per bulletin pages. This pages are crafted for all the bulletins found in the trending table, and each page is named after the bulletin (escaped by replacing dot and hyphens with underscore.

Release 3

Introduced the global compliance graphs on the landing page. This makes the first look at the site very powerful, as we get compliance levels for the entire estate.

There were no prior release (or production use) of the tool.

Top

Sample site:

A complete, fully dynamic, sample site is now available: you can jump to it right now!

Statistics
0 Favorited
3 Views
4 Files
0 Shares
0 Downloads
Attachment(s)
zip file
Patch Trending Package.zip   41 KB   1 version
Uploaded - Feb 25, 2020
zip file
PatchTrending-8.0_version17.zip   38 KB   1 version
Uploaded - Feb 25, 2020
zip file
PatchTrending-8.1.version17.zip   38 KB   1 version
Uploaded - Feb 25, 2020
zip file
SiteBuilder-v15.zip   32 KB   1 version
Uploaded - Feb 25, 2020

Tags and Keywords

Comments

Mar 01, 2018 05:52 AM

Hi Michael,

It's good to see your question. Funnily enough I made a last build I never got around to publish early in October. So it's here right now.

One feature I have added there is a non-feature: the site layout file is now optional. So you can generate a fully fledged and browsable site without creating any custom pages.

I guess that's not the answer you wanted, but it fits the bill. Customisation, afterall, is not something I want to handle myself.

Finally, for the fun of it, I have update the mini-site and added  earlier versions as well, so you can see how far down the line we are from earlier version:

[Links no longer available and removed]

Jun 23, 2015 04:07 AM

You are most welcome Vikram!

Jun 23, 2015 04:00 AM

Awesome...this did the trick :)

Thanks so much..

Vikram

Jun 23, 2015 03:26 AM

Hello Vikod,

Make sure the tool runs under an Altiris Admin account else it won't be able to access the DB, which is most likely the case here.

Back in the days Server Tasks used to run under tha AppID but this is no longer true: all tasks run under the system account by default now, and server tasks have no ability to specify the account to use - so you can only use a Client Task now.

Jun 23, 2015 03:02 AM

I guess i'm still struggling with the Patch Trending not getting updated. Does only when i run the Sitebuilder.exe manually.

The "Run SiteBuilder (Patch Trending)" runs as per schedule but comes out with the below message..

I can see the Table listed in the My SQL Server..

 

Help Please..

 

Regards,

Vikram

Jun 19, 2015 01:38 AM

Got this to work - by running the sitebuilder.exe again. :)

thanks so much for this tool - waiting for release 17 :)

 

cheers,

Vikram

Jun 17, 2015 02:15 AM

Hi Ludovic,

I just upgraded my setup from 7.1 to 7.5 version - and everything seems to be intact, except for the Patch Trending does'nt seem to update now... the last update on the site was prior to the upgrade.

i was was wondering if i had to re-run the install script - and this is the error that comes up - not sure why?

Do i need to re-install patch trending from scratch after an upgrade?

 

Thank You,

Vikram

Jan 12, 2015 05:30 AM

Hello Prep,

The site-builder is a blunt instrument and will remain as his, however you can alway use the site-layout file to group the bulleints / updates by level.

If you just need the Important / Critical updates then you should go back to the tranding procedures you have put in place in order to ensure it only captures the bulletins / updates of interest for the collection that suits your need.

Jan 12, 2015 05:28 AM

Hello Vikod,

 

The site layout is controlled by the site-layout.txt file, which resides in the running directory.

Here's the latest version of the file, with all Microsoft updates (I have not updated the Adobe pages nor any other vendor pages, as I don't needs this on a daily basis).

Note that I have added all bulletins named MS15-001 to MS15-099, so updating the layout file every month is very simple (find the beginning and end bulletin, append the month header at the beginning of the line, make sure it's correctly comma sepearted and you are done).

One nice trick is that the bulletins won't show up if no update exist (so the microsoft-2015-unreleased entry should never show unless you are unable to update the site-layout.txt in time ;).

 

Jan 09, 2015 09:30 AM

Ludovic,

Any chance that this could be modified to only display updates of a particular set of severities?  For example, for my purposes, I care primarily about "Critical" and "Important" updates.  I tried to find the stored procedure to poke around, but couldn't find it.

Thanks!

-Prep

Nov 14, 2014 05:50 AM

Hi Ludovic,

I have been using in my org for a while and it is fantastic... however been want to check on how do i go about having the custom compliance view to show "up to date" links ... for me it still show up till November 2013.

altiris_custom_patchtrend_1.JPG

Apr 16, 2014 04:40 PM

Hello Preppie,

I got this workig in hierarchy however it was non trivial.

I ended up linking the SQL server from the 3 child SMP's and replicating the trending data up to the parent, where I could then use the site builder to generate a global view.

This had a few caveats I worked around or documented. For example to make sure I had clean data I forced the DB collection to ensure 3 data points were present (one for each child server).

This de-facto removes all localised updates that are not present on each SMP. So, I would say you can do it, but I'm not in a position to document the process in a safe manner (one that would ensure it is undestandable and easily usable).

Apr 07, 2014 10:01 AM

This is an excellent feature (not sure why such trending graphs aren't native to Altiris in the first place!).  Any chance that this could work with a hierarchy?  Since it appears to rely on the native patch compliance reports, I imagine that it wouldn't work (since that data isn't replicated up in hierarchy).  But a guy can dream, right?

Dec 13, 2013 11:46 AM

Hi Ludovic,

I love this this, thank you by the way.

I was wondering however, if you had any insight as to why the scheduled task of running "SiteBuilder.exe" does not seem to update the webpages, or graphs.  Even the date time on the main webpage is the orginal date.

If I run the "SiteBuilder.exe" manually, from a CMD Prompt.  It updates the webpage, and a new "Generated by {CWoC} Patch Trending..." is displayed, and the graphs then update and display trending information.

I've even attempted making the a Scheduled task in Windows, that is being run by our Altiris Service Account.

Same results... no update on the date or time on the main page.  Manually run... page updated.

 

Any help you could provide would be great.

 

Thanks again, this is a marvelous tool!

-Kev

 

 

----------------------------------

 

Update:  I just figured it out.

In the Task, since we installed to a different drive other then C:

We orginally had this:

REM Move to the running folder - by default we run under /Altiris/NS/PatchTrending
cd "D:\Program Files\Altiris\Notification Server\Web\PatchTrending\"
SiteBuilder.exe
 
 
Once I added the Drive letter before it, it now works.
 
REM Move to the running folder - by default we run under /Altiris/NS/PatchTrending
D:
cd "D:\Program Files\Altiris\Notification Server\Web\PatchTrending\"
SiteBuilder.exe
 

 

Dec 04, 2013 03:51 AM

Hello Intern-L,

I'm glad to hear that you like the tool. :D

The bulletins added to site-layout.txt will be added to the site only if there is data available for them.

In the current release the store procedure will only capture data for bulletins that are active (enabled, it doesn't need to have a policy attached).

Also there's another not well documented catch on the site builder results: we do not generate JS files or links on the site for updates that have not current data (no longer enabled). This is because I found that many bulletins had tens of update and scanning thru them when most are showing old data was hard and confusing.

For your last question, yes you can customise the collection used for trending the data. This is part of the SQL.

You can call the store procedure with the parameter @collectionguid = '<your collection guid>'.

Take a note when you change the trended computer group (collection) as it could show a drmatic change in your global compliance.

Dec 03, 2013 09:19 AM

Hi Ludovic,

Everyone that I work with loves Patch Trending and its features! We were looking to customize the reports and trending/tracking a little by adding the patches and updates that we deploy. However, after adding the vendor name and bulletins to the "site-layout.txt" file as described in the Help section, the web pages and the Javascript files aren't being created. Is there something else that we need to do?
Also, I was wondering if there's a way to track compliance by computer based on a Collection GUID?

Thanks in advance! 

Nov 08, 2013 05:00 AM

I have uploaded the site-layout.txt version containing the October 2013 bulletins. Note that I only cater for Microsoft updates there, but I'll post an update every month once I have crafted the file for my customer.

And I'll try to post a comprehensive pack that contains everything needed to get this running with the least possible amount of work :D.

Nov 07, 2013 12:23 PM

I went through this with a customer and we had it all working nicely and automated but there is a still a manual process to update the sitebuilder.txt csv file going forward. Do you think this always will be a manual step every month?

Oct 16, 2013 05:16 PM

Thanks for the tip it all seems to work now just needed to wait as yous said.

Oct 16, 2013 03:19 AM

Hello oeghagha1,

It looks like you only have 1 data point in the table.

Have you scheduled the stored procedure / report to run every day? This will populate the table over time and you'll have many more points available to show you the trend lines :D.

Oct 15, 2013 11:02 PM

Hi, I have tryed use the Sitebuilder solution in my environment ,the main issue I am having, is that the matrix  data generated by the sitebuilder .exe dose not seem to populate the graphs. this is what i get on the landing page- see atteched image:   I have run the prequisite and can see the tables in the database but for some reason the information does not come through, can you help.

 

Aug 21, 2013 05:15 AM

You got that perfectly right Michael.

Note that due to customer demand I'm working on adding Patch Compliance by Computer data.

It wouldn't show in the same manner (generating and tracking data by computer would be silly) but we could keep track of the state by percent (so see at any given time how many computers on the estate are at x % compliance etc).

Aug 21, 2013 04:46 AM

So - create the linked report, automate it to run daily, run the exe in the folder you want these HTML pages and then view them. 

The HTML pages are portable after creation are they not?

Related Entries and Links

No Related Resource entered.