Kaseya Community

How to notify users that a patch installation is beginning.

  • My main complaint from people regarding patching is that people want to know when patch installations are beginning (If they are logged-in). Some people do not remember to leave their computers on , so I do not use the 'Skip if Offline' option for patch management. Keeping computers up-to-date, especially against security vulnerabilities, is just too important... so here it is!

    I wrote the following Kaseya script and SQL code to facilitate that.

    WARNING: If you do not understand SQL, I would advise that you DO NOT use this. This has only been tested on Kaseya 2008 SP1 w/SQL 2005.

    1) Create script in Kaseya. (Don't modify the code below except to change the message displayed.)

    Script Name: Notify Patch Start
    Script Description: Notify Patch Start

    IF User Is Logged In
    Parameter 1 :
    THEN
    Send Message
    Parameter 1 : Your computer is downloading and installing patches. You may continue to work during this process. You may notice an effect on performance. You will be notified when it is completed. Please reboot if prompted.
    Parameter 2 : 1
    OS Type : 0
    ELSE
    Get Variable
    Parameter 1 : 6
    Parameter 2 :
    Parameter 3 : machineid
    OS Type : 0
    Schedule Script
    Parameter 1 : 56996832
    Parameter 2 : 3
    Parameter 3 : #machineid#
    OS Type : 0



    2) Edit the Kaseya script you just created. Make sure that the step 2 in the ELSE section refers to the script you just created.

    3) Make note of the SCRIPTID of the script you just created. (Hover your mouse pointer over the script's name in the left menu. Look at your browser's status bar. It's there. (If you can't see the entire scriptid, then right-click the link for the script and choose "Copy Shortcut" and then paste it into notepad so that you can read it)

    4) Copy the SQL script below into a new query in SQL manager. EDIT THE SCRIPT ID IN THE SQL SCRIPT BELOW TO MATCH THE SCRIPTID OF THE SCRIPT YOU JUST CREATED! Make sure the query is running against your Kaseya Database not another database! (Usually ksubscriber, I think) Execute the script.

    This is the SQL script to add the Patch Notification ability:

    --Do not schedule the "Notify Patch Start" script manually using Kaseya.
    --Any schedule you set in Kaseya will be erased by this SQL Trigger/Procedure.
    --MAKE SURE YOU CHANGE the scriptid below to match the scriptid of the Patch Notify Start script!
    --Change the line below to match your Kaseya Database Name (make sure you run the script against your Kaseya Database)

    USE [ksubscribers]
    GO

    IF OBJECT_ID ('customTrigPatchNotifySchedule', 'TR') IS NOT NULL
    DROP TRIGGER [dbo].[customTrigPatchNotifySchedule];
    GO

    IF OBJECT_ID ('customTrigPatchNotifyUnSchedule', 'TR') IS NOT NULL
    DROP TRIGGER [dbo].[customTrigPatchNotifyUnSchedule];
    GO

    IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[customProcPatchNotify]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[customProcPatchNotify];
    GO


    CREATE PROCEDURE [dbo].[customProcPatchNotify]
    @schedule BIT,
    @varAgentGUID NUMERIC(26, 0)
    AS
    BEGIN


    SET NOCOUNT ON;
    BEGIN TRY

    BEGIN TRANSACTION PatchNotify;

    DECLARE @varScriptID INT

    --
    -- CHANGE THIS TO MATCH THE SCRIPTID FOR THE NOTIFICATION SCRIPT!!!
    --
    SELECT @varScriptID = 56996832

    DECLARE @varScriptName VARCHAR(260)
    SELECT @varScriptName = (SELECT TOP 1 scriptName FROM [dbo].[scriptIdTab] WHERE scriptId=@varScriptID)

    IF @schedule = 1
    BEGIN


    -- Unschedule any previously scheduled instances of this script for this machine.
    DELETE FROM [dbo].[scriptAssignment]
    WHERE agentGuid = @varAgentGUID AND scriptId = @varScriptID;


    -- Schedule script to execute immediately on this machine.
    INSERT INTO [dbo].[scriptAssignment] (scriptId, agentGuid, runCount, logLevel, execPeriod, execScriptTime, actionAdmin, runAtTime, monthPeriod)
    VALUES (@varScriptID, @varAgentGUID, 1, 0, 0, CURRENT_TIMESTAMP, '', 0, 0);


    -- Log the fact that the script was scheduled by this trigger.
    INSERT INTO [dbo].[scriptlog] (agentGuid, eventTime, scriptName, description, actionAdmin, scriptId)
    VALUES (@varAgentGUID, CURRENT_TIMESTAMP, @varScriptName, 'Custom SQL Trigger: Detected patch installation and scheduled script ' + @varScriptName + ' to run now.', '*System*', @varScriptID);


    END



    IF @schedule = 0
    BEGIN


    -- Unschedule any previously scheduled instances of this script for this machine.
    DELETE FROM [dbo].[scriptAssignment]
    WHERE agentGuid = @varAgentGUID AND scriptId = @varScriptID;


    -- Log the fact that the script was un-scheduled by this trigger.
    INSERT INTO [dbo].[scriptlog] (agentGuid, eventTime, scriptName, description, actionAdmin, scriptId)
    VALUES (@varAgentGUID, CURRENT_TIMESTAMP, @varScriptName, 'Custom SQL Trigger: Detected reboot for patch installation and un-scheduled any instances of ' + @varScriptName + '.', '*System*', @varScriptID);

    END

    COMMIT TRANSACTION PatchNotify;

    END TRY

    BEGIN CATCH

    IF (XACT_STATE()) = -1
    BEGIN
    ROLLBACK TRANSACTION PatchNotify;
    BEGIN TRY
    BEGIN TRANSACTION LogError;
    -- Log the fact that the script had an error in the configlog.
    INSERT INTO [dbo].[configlog] (agentGuid, eventTime, description)
    VALUES (@varAgentGUID, CURRENT_TIMESTAMP, 'Custom SQL Trigger: There is a problem with the custom SQL trigger.');
    COMMIT TRANSACTION LogError;
    END TRY
    BEGIN CATCH
    ROLLBACK TRANSACTION LogError;
    END CATCH
    END
    IF (XACT_STATE()) = 1
    BEGIN
    COMMIT TRANSACTION PatchNotify;
    END

    END CATCH

    END;
    GO



    CREATE TRIGGER [dbo].[customTrigPatchNotifyUnschedule]
    ON [dbo].[scriptlog]
    FOR INSERT
    AS
    IF UPDATE(eventtime) AND UPDATE(agentGuid) AND UPDATE(scriptname) AND UPDATE(scriptId) AND UPDATE(actionAdmin) AND UPDATE(description)
    BEGIN

    SET NOCOUNT ON

    BEGIN TRY

    BEGIN TRANSACTION UnPatchTrigger;

    DECLARE @varAgentGUID NUMERIC(26, 0)

    -- Check the script log entry.
    SELECT @varAgentGUID = (select ins.agentGuid from inserted ins where ins.scriptname like '$inst$ptc%' and ins.description like '%patch%reboot% was successfully scheduled to run on % in 0 minutes%')


    -- Install completed. Unschedule any Patch Notify scripts that have not yet run.
    IF @varAgentGUID IS NOT NULL and @varAgentGUID > 0
    BEGIN
    exec [dbo].[customProcPatchNotify] 0, @varAgentGUID;
    END

    COMMIT TRANSACTION UnPatchTrigger;

    END TRY

    BEGIN CATCH

    IF (XACT_STATE()) = -1
    BEGIN
    ROLLBACK TRANSACTION UnPatchTrigger;
    BEGIN TRY
    BEGIN TRANSACTION UnPatchError;
    -- Log the fact that the script had an error in the configlog.
    INSERT INTO [dbo].[configlog] (agentGuid, eventTime, description)
    VALUES (@varAgentGUID, CURRENT_TIMESTAMP, 'Custom SQL Trigger: There is a problem with the custom SQL trigger.');
    COMMIT TRANSACTION UnPatchError;
    END TRY
    BEGIN CATCH
    ROLLBACK TRANSACTION UnPatchError;
    END CATCH
    END
    IF (XACT_STATE()) = 1
    BEGIN
    COMMIT TRANSACTION UnPatchTrigger;
    END

    END CATCH
    END
    GO


    CREATE TRIGGER [dbo].[customTrigPatchNotifySchedule]
    ON [dbo].[patchInstScriptStatus]
    FOR INSERT
    AS
    IF UPDATE(agentGuid) AND UPDATE(scriptID) AND UPDATE(scriptName)
    BEGIN

    SET NOCOUNT ON

    BEGIN TRY
    BEGIN TRANSACTION PatchTrigger;

    DECLARE @varAgentGUID NUMERIC(26, 0)

    -- Patch installation beginning. Get the Agent GUID.
    SELECT @varAgentGUID = (select ins.agentGuid from inserted ins where ins.scriptname like '$inst$ptc%')

    -- Schedule Patch Notify Start script for this machine.
    -- If user is not logged-in, the notification will wait to display to the first user to log-in during the patch install.
    IF @varAgentGUID IS NOT NULL and @varAgentGUID > 0
    BEGIN
    exec [dbo].[customProcPatchNotify] 1, @varAgentGUID;
    END

    COMMIT TRANSACTION PatchTrigger;

    END TRY

    BEGIN CATCH

    IF (XACT_STATE()) = -1
    BEGIN
    ROLLBACK TRANSACTION PatchTrigger;
    BEGIN TRY
    BEGIN TRANSACTION PatchError;
    -- Log the fact that the script had an error in the configlog.
    INSERT INTO [dbo].[configlog] (agentGuid, eventTime, description)
    VALUES (@varAgentGUID, CURRENT_TIMESTAMP, 'Custom SQL Trigger: There is a problem with the custom SQL trigger.');
    COMMIT TRANSACTION PatchError;
    END TRY
    BEGIN CATCH
    ROLLBACK TRANSACTION PatchError;
    END CATCH
    END
    IF (XACT_STATE()) = 1
    BEGIN
    COMMIT TRANSACTION PatchTrigger;
    END

    END CATCH

    END
    GO


    The below code is how to REMOVE these changes from your SQL server if you ever decide to:

    IF OBJECT_ID ('customTrigPatchNotifySchedule', 'TR') IS NOT NULL
    DROP TRIGGER [dbo].[customTrigPatchNotifySchedule];
    GO

    IF OBJECT_ID ('customTrigPatchNotifyUnSchedule', 'TR') IS NOT NULL
    DROP TRIGGER [dbo].[customTrigPatchNotifyUnSchedule];
    GO

    IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[customProcPatchNotify]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[customProcPatchNotify];
    GO


    Legacy Forum Name: How to notify users that a patch installation is beginning.,
    Legacy Posted By Username: Richard Williams
  • Looks and works good, thanks!

    is it also possible to to notify the users with a custom message popup when the installation has finished?

    Legacy Forum Name: Patch Management,
    Legacy Posted By Username: MarkvanEtten
  • What we do here is configure the "Reboot Action" under patch management to the following: "If user is logged in ask to reboot every 10 minutes until the reboot occurs. Reboot if user not logged in." (For servers we handle reboots differently)

    That will let them know that the patch install is completed and they need to reboot. We set it to 10 minutes so that it's annoying enough that people actually reboot soon. We used to have it set longer but people would forget to reboot and then call us saying "this or that doesn't work" when actually all they needed to do was reboot after the patch install. Setting it for 10 minutes makes it annoying enough and keeps it fresh in their mind. Now they reboot before calling Smile

    Also, a quick note on how this script works:
    1) The SQL trigger looks for log entries to detect that a patch install has begun.
    2) It then schedules the patch notify script in Kaseya to run immediately.
    3) The patch notify script checks to see if a user is logged-in. If not, the script reschedules itself to check again in a couple minutes. This is to make sure that people get notified even if they are not logged-in at the exact moment the patch install starts. This makes sure that they will be notified if they log-in anytime during the patch install.

    4) The SQL trigger then continues to look for kaseya to enter a log entry saying that the a reboot script is running. When it sees it, it assumes the patch install is finished (pretty safe bet), and it will unschedule the kaseya notify script. This is to make sure that people do not get notified of a patch installation STARTING when in fact it is not. If you look at what's happeing in step 3 above (if a user is not logged-in to the computer) then you could see how that if they DO login, even after a patch install was completed, then they would be notified that a patch install has started. This inconsistency is solved by this step (step 4).... it makes sure that users will only get notified about a patch install when the install is either starting, or when they login in the middle of the install.

    The point of all this (I think) is that you probably want some sort of reboot action configured for this whole thing to work smoothly. (The only consequence of not having a reboot action set would be that people may get notified of a patch install starting when in fact it had already completed)

    Sorry if I'm not being clear - I'll be happy to clarify anything.

    Legacy Forum Name: Patch Management,
    Legacy Posted By Username: Richard Williams
  • A custom reboot message is probably not possible because it's "hard coded" into Kaseya..

    But if you were to set kasyea's patch management reboot action to "no reboot" then it'd be theoretically possible to code a SQL trigger that would be able to handle the notification and reboot process seperately but it'd certainly take some effort. Basically, the answer is no... without some research, looking into the code, and some experiementation ultimately resulting in a custom-coded solution similar to this one Smile

    Legacy Forum Name: Patch Management,
    Legacy Posted By Username: Richard Williams
  • Richard,

    Hello. I was really intriged and impresses by your ideas and script.

    I have many customers that have users with laptop computers. Sometimes these folks are at the office on the LAN, othertimes that are in the field on aircards. They have asked for a way to be prompted for whather or not it is okay to install patches - prior to beingging the pacth installs, as opposed to a reboot rompt AFTER the patches are installed.

    So I like you idea of lettign them know that patchings are being installed. But would it be possibel to change the logic to include a YES/NO prompt/ Somethign like "There are important patches that need to be installed on your system. Is it okay to install these patches now? NO (IF), YES (ELSE)" with a default to YES/Else if there is no response for 5 minutes?

    It would be really great if this could somehow be accomplsihed.

    Lloyd

    Legacy Forum Name: Patch Management,
    Legacy Posted By Username: lwolf
  • Yes, it can be done and I will start working on it. It's going to take some work but basically I'm going to redesign my original approach so that we can do this.

    I noticed some interesting things when I looked into this. One of the things I think I may be able to do is actually seperate the download/install process so that the downloads will run only if an internet connection is available and then the installation will run only after everything has been successfully downloaded. We could prompt users at start of both/either. Anyway, I'm getting ahead of myself but I wanted to let you know I'm working on it because I see alot of interesting possibilities with this. I'm tired of getting failed patch install/download messages and this effort may be the path that lets us get rid of them!! We'll see... it will take me a while because I can't focous on coding this because I have users to support. But it's on my higher-priority list... you know how it goes Smile

    Legacy Forum Name: Patch Management,
    Legacy Posted By Username: Richard Williams
  • Richard,

    Thanks for the reply. It would be awsome if a before-hand Yes/No prompt could be implemented. Or I suppose a No/Yes to be exact, with it defaulting to Yes if no response after X minutes.

    If you care to chat in person about this, please let me know and I can give you my contact info.

    Lloyd

    Legacy Forum Name: Patch Management,
    Legacy Posted By Username: lwolf
  • Richard,

    FYI... I was speaking with Joe Paquette (a Kaseya developer) yesterday on another issue.

    I mentioned your idea of a "Patches have started" popup message, and my add-on idea of a "There are patches that need to be installed. is it okay to install now? No/Yes" popup message. he liked both ideas as feature requests, and said they would not be extremely hard to implement.

    He asked that I submit a feature request, which I will do.

    Lloyd

    Legacy Forum Name: Patch Management,
    Legacy Posted By Username: lwolf
  • MarkvanEtten:
    As I was digging into some things, I found where the reboot message is stored for patches. If you run this query, you will see where the reboot message is stored. Just edit the part in ifFuncParam1 after the last set of +++ marks. You'll need to write an update query to do it, just be carefull Smile Here's the query to find the patch reboot scripts:

    SELECT [si].[scriptId], [sit].[scriptName], [si].[ifFuncParam1], [si].[scriptDescription] FROM [dbo].[scriptIf] [si] LEFT JOIN [dbo].[scriptIdTab] [sit] ON [si].[scriptId] = [sit].[scriptId] where ([sit].[scriptName] LIKE '%reboot%' AND [si].[ifFuncParam1] LIKE '%YES%NO%') AND ([si].[scriptDescription] NOT LIKE '%DO NOT SCHEDULE%')


    Once you find the script ID for the notice you want to modify, and copy the current ifFuncParam1 contents, you could modify ifFuncParam1 using an update query such as the one below:

    UPDATE [dbo].[scriptIf] [si] SET [si].[ifFuncParam1] = '+++YES:Reboot Now++++++NO:Continue Working+++This is a custom patch reboot request. Reboot now?' WHERE [si].[scriptId] = 'enter-script-id-here'


    You could repeat this process for each of them.
    [/COLOR][/COLOR]

    Legacy Forum Name: Patch Management,
    Legacy Posted By Username: Richard Williams
  • So, from your findings, it seems that Kaseya should be able to include this in the patching process. I also have users in similar situations, and would like to give them the option to delay up to 3 times, before updates are forcibly installed. Did we ever get results from Kaseya on this? Thanks!

    Legacy Forum Name: Patch Management,
    Legacy Posted By Username: toolman5774
  • All of this is very interesting. I would definately like the ability to have the users postpone a few times. The notification is at least an improvement.

    Legacy Forum Name: Patch Management,
    Legacy Posted By Username: mmoore
  • I have a simple script I've created that I schedule to run at the same time as user's patches.

    During a patch-update process, UPDATE.EXE runs. I usually stagger my workstation patches at each location. This script runs a few minutes before my client's scheduled patch installation.


    Essentially, it's:

    UpdateNotify Script:

    IF update.exe is running, THEN Send Message Immediately
    ELSE execute UpdateNotifyScript in 2 minutes.

    In situations that you schedule workstation patch installations, it works out well. I'm not sure whether this script times out after 2 hours or not, but perhaps someone else might have another idea along those lines.

    Script Name: UpdateNotify
    Script Description: This script should be scheduled to run on all machines in an environment approximately (or very shortly before) when patches are being deployed (or staggered). This script looks for UPDATE.EXE to execute, and upon execution immediately informs the user that updates are running.

    IF Application is Running
    Parameter 1 : update.exe
    THEN
    Send Message
    Parameter 1 : Your computer has begun installing security updates will seem slower during this process. You may continue working and will be prompted for a reboot once updates are completed.
    Parameter 2 : 1
    OS Type : 0
    ELSE
    Execute Script
    Parameter 1 : UpdateNotify (NOTE: Script reference is NOT imported. Correct manually in script editor.
    Parameter 2 : 2
    Parameter 3 : 1
    OS Type : 0



    Legacy Forum Name: Patch Management,
    Legacy Posted By Username: jvanber