SFTP software deployment is implemented, and this feature is supported in “DENALI.SP3.HF14” onwards.

• If Customer is using less than DENALI.SP3.HF14 and using higher version of SAM Agent which is v2.5.6.15 or above, then apply the below two SPs in customer instance to work with Agent based software deployment feature without any issues.

   Software-Deployment-Fix-forAgent_v2.5.6.15.sql

  /* 
      -If Customer is using less than DENALI.SP3.HF14 and using higher version of SAM Agent which is v2.5.6.15 or above, then apply the below two SPs in customer instance.
      1. [USP_AM_GetPatchDetailsForExecution]
      2. USP_AM_GetPatchSessionDetails

  */
  ALTER PROCEDURE [dbo].[USP_AM_GetPatchDetailsForExecution] (@IPAddress NVARCHAR(255), @MachineName NVARCHAR(500))
  AS
  BEGIN

  
  DECLARE @MasterPatchDetails TABLE(PatchId INT,IsConditionMatched BIT)
  DECLARE @RBATicketDetails TABLE(PatchID INT, TicketId INT, ModuleType NVARCHAR(10),IsDependent BIT,SmartActionQueueID INT,AssetID INT)
  DECLARE @DependentTickets Table(RID int Identity(1,1),PatchID Int,TicketId INT, ModuleType NVARCHAR(10),SmartActionQueueID INT,DependentActionOutput NVARCHAR(MAX),
  DependentActionStatus NVARCHAR(100),Module NVARCHAR(50),ConditionCriteria NVARCHAR(100),ConditionValue NVARCHAR(500), Isvalid BIT,NextRunDate DATETIME,ExecutedTime DATETIME)
   

  ---- Patch details wich has no reference to RBA
  INSERT INTO @MasterPatchDetails(patchid,IsConditionMatched)
  SELECT Distinct PSS.Session_ID,1 
  FROM Patch_Session_Status PSS
  INNER JOIN Patch_master PM ON PSS.PatchID = PM.PatchID 
  INNER JOIN AM_PatchScan_Master PSM ON PSM.PatchScan_ID = PSS.Session_ID AND PSM.PushPatchBy = 'Agent' 
  WHERE 1 = 1 and PSM.Active = 1 AND (PSS.IPAddress = @IPAddress or PSS.IPAddress = @MachineName) AND PSS.Status IN('Not Started','ReStart') and ISNULL(RefType,'') = '' and ISNULL(RefID,0) = 0

  
  INSERT INTO @RBATicketDetails(PatchID, TicketId, ModuleType,AssetID)
  Select SS.Session_ID,PSM.RefID,RefType,SS.Asset_ID from Patch_Session_Status SS
  INNER JOIN AM_PatchScan_Master PSM ON PSM.PatchScan_ID = SS.Session_ID AND PSM.PushPatchBy = 'Agent' 
  WHERE 1 = 1 and PSM.Active = 1 AND (SS.IPAddress = @IPAddress or SS.IPAddress = @MachineName) AND SS.Status IN('Not Started','ReStart') and ISNULL(RefType,'') <> '' and ISNULL(RefID,0) > 0

  if exists (Select 1 from @RBATicketDetails)
  BEGIN
      ---- filter condition order 
      UPDATE AM SET IsDependent = 1, AM.SmartActionQueueID = ISQ.SmartActionQueueID 
      from  @RBATicketDetails AM
      LEFT JOIN  IM_SmartAction_Queue ISQ ON AM.PatchID = ISQ.PatchScanMasterUID 
      LEFT JOIN Adm_Selfhealing_actions ASA with(nolock) ON ASA.UID=ISQ.SelfHealingActionID and isnull(ASA.IsActive,1)=1
      Where ISNULL(ASA.ConditionOrder,0) > 0 and ISNULL(ASA.ConditionCriteria,'') <> '' 

      Insert into @MasterPatchDetails (PatchID,IsConditionMatched)
      Select PatchID,1 from @RBATicketDetails Where ISNULL(IsDependent,0) = 0 
      
      INSERT INTO @DependentTickets(PatchID,TicketId,ModuleType,SmartActionQueueID,DependentActionOutput,DependentActionStatus,Module,ConditionCriteria,ConditionValue,NextRunDate,ExecutedTime)
      
      Select DISTINCT AM.PatchID,AM.TicketId,AM.ModuleType,AM.SmartActionQueueID, tbl.Output as DependentActionOutput,tbl.Status as DependentActionStatus,IsNull(ISQ.Module,'IM') Module
      ,ASA.ConditionCriteria,ASA.ConditionValue,tbl.NextRunDate,tbl.ExecutedTime
      from  @RBATicketDetails AM
      INNER JOIN  IM_SmartAction_Queue ISQ ON  ISQ.SmartActionQueueID = Am.SmartActionQueueID 
      LEFT JOIN Adm_Selfhealing_actions ASA with(nolock) ON ASA.UID=ISQ.SelfHealingActionID and isnull(ASA.IsActive,1)=1
      LEFT OUTER JOIN (select ISQ1.Output ,ISQ1.Status,ISQ1.TICKETID, ISQ1.ExecutionOrder,ISQ1.SelfHealingMasterID,ISQ1.SmartActionQueueID,ISQ1.CI_ID,ISQ1.NextRunDate,ISQ1.ExecutedTime
          from IM_SmartAction_Queue ISQ1 with(nolock) 
          INNER JOIN Adm_Selfhealing_actions ASQ1 with(nolock) ON ASQ1.SelfHealingMasterID=ISQ1.SelfHealingMasterID AND ASQ1.ConditionOrder=ISQ1.ExecutionOrder
      ) as tbl on tbl.ExecutionOrder=ASA.ConditionOrder and tbl.TicketID=ISQ.TicketID and tbl.SelfHealingMasterID=ISQ.SelfHealingMasterID and ISNULL(tbl.CI_ID,0)=ISNULL(ISQ.CI_ID,0)
      Where IsDependent = 1 and isnull(ASA.IsActive,1)=1 and ISNULL(ConditionCriteria,'') <> '' and ISNULL(ConditionValue,'') <> ''
      
      
      UPDATE @DependentTickets SET IsValid = Case When ConditionCriteria = 'CONTAINS' then CASE WHEN DependentActionOutput like '%'+ConditionValue+'%' then 1 Else 0 End  
           When ConditionCriteria = 'EQ' then CASE When DependentActionOutput = ConditionValue Then 1 Else 0 End
           When ConditionCriteria = 'NEQ' then CASE When DependentActionOutput <> ConditionValue Then 1 Else 0 End
           When ConditionCriteria = 'GT' then CASE When DependentActionOutput > ConditionValue Then 1 Else 0 End
           When ConditionCriteria = 'LT' then CASE When DependentActionOutput < ConditionValue Then 1 Else 0 End
      END

      Insert into @MasterPatchDetails (PatchID,IsConditionMatched)
      Select PatchID,Isvalid from @DependentTickets Where ISNULL(NextRunDate,'') = '' and DependentActionStatus not in ('New','In-Progress') 

  END

  ---Select * from @MasterPatchDetails

  SELECT Distinct PSS.Session_ID ,PSS.IPAddress ,PSS.Status
  ,PSS.Messages ,PM.PatchID ,PM.Org_ID ,PM.P_Type ,PM.P_Name
  ,PM.P_Args ,PM.P_Version ,PM.P_Description ,PM.P_Vendor
  ,PM.P_FilePath ,PM.Active ,PM.InsertDT ,PM.InsertBy ,PM.UpdateDT, PM.IsRemotePath,PM.StorageServer,PM.PathType,PM.ProxyServer,PM.FTPUserName,PM.FTPPassword
  ,PM.UpdateBy,PSS.UID as PatchSessionUID,UM.Domain as Domain,UM.PWD as Password,UM.UID as UserName , PSM.RefID,MPD.IsConditionMatched
  ,''  SFTPPassword,''  SFTPUserName, '' SFTPServerName, '' SFTPProtocol
  FROM Patch_Session_Status PSS
  INNER JOIN @MasterPatchDetails MPD ON MPD.PatchId = PSS.Session_ID
  INNER JOIN Patch_master PM ON PSS.PatchID = PM.PatchID 
  INNER JOIN AM_PatchScan_Master PSM ON PSM.PatchScan_ID = PSS.Session_ID AND PSM.PushPatchBy = 'Agent' 
  LEFT JOIN UID_Master UM ON UM.WMI_Usr_ID = PSM.Patch_UserID 

  WHERE 1 = 1 and PSM.Active = 1 AND (PSS.IPAddress = @IPAddress or PSS.IPAddress = @MachineName)  AND PSS.Status IN('Not Started','ReStart')

  END
  GO

  ALTER PROC [dbo].[USP_AM_GetPatchSessionDetails] (@SessionID INT,@PatchID INT,@IPAddress NVARCHAR(255))
  AS
  BEGIN

  DECLARE @SQL NVARCHAR(MAX) 
  SET @SQL = ''

  SET @SQL = ' DECLARE @AllPatchDetails TABLE(SessionUID INT,SessionID INT,PatchId INT,IPAddress NVARCHAR(300),AssetID INT)
  DECLARE @MasterPatchDetails TABLE(SessionUID INT,SessionID INT,PatchId INT,IPAddress NVARCHAR(300),AssetID INT,IsConditionMatched BIT)
  DECLARE @RBATicketDetails TABLE(SessionUID INT,SessionID INT,PatchId INT,IPAddress NVARCHAR(300),AssetID INT,PatchScanID INT, TicketId INT, ModuleType NVARCHAR(10),IsDependent BIT,SmartActionQueueID INT)
  DECLARE @DependentTickets Table(SessionUID Int,SessionID INT,PatchId INT,IPAddress NVARCHAR(300),AssetID INT,TicketId INT, ModuleType NVARCHAR(10),SmartActionQueueID INT,DependentActionOutput NVARCHAR(MAX),
  DependentActionStatus NVARCHAR(100),Module NVARCHAR(50),ConditionCriteria NVARCHAR(100),ConditionValue NVARCHAR(500), Isvalid BIT,NextRunDate DATETIME,ExecutedTime DATETIME)
   '
  ----- All Session Assets Details into parent Table
  SET @SQL = @SQL + ' INSERT INTO @AllPatchDetails(SessionUID,SessionID,PatchId,IPAddress,AssetID)
  SELECT PSS.UID,PSS.Session_ID ,PSS.PatchID ,PSS.IPAddress, PSS.Asset_ID 
  FROM Patch_Session_Status PSS
  INNER JOIN Patch_master PM ON PSS.PatchID = PM.PatchID
  WHERE 1 = 1 '

  IF(ISNULL(@SessionID,0) > 0)
  BEGIN
      SET @SQL = @SQL + ' AND Session_ID =' + CONVERT(NVARCHAR(100), @SessionID)    
  END
  IF(ISNULL(@PatchID,0) > 0)
  BEGIN
      SET @SQL = @SQL + ' AND PSS.PatchID =' + CONVERT(NVARCHAR(10), @PatchID)    
  END
  IF(ISNULL(@IPAddress,'') != '')
  BEGIN
      SET @SQL = @SQL + ' AND PSS.IPAddress =''' + @IPAddress + ''''
  END

  ---- Take Only Patches not belonged to RBA 
  SET @SQL = @SQL + ' INSERT INTO @MasterPatchDetails (SessionUID,SessionID,PatchId,IPAddress,AssetID,IsConditionMatched)
  Select SessionUID,SessionID,PatchId,IPAddress,AssetID,1 from @AllPatchDetails Where ISNULL(AssetID,'''') = '''' '

  ---- Take Only Patches belonged to RBA 
  SET @SQL = @SQL + ' INSERT INTO @RBATicketDetails (SessionUID,SessionID,PatchId,IPAddress,AssetID)
  Select SessionUID,SessionID,PatchId,IPAddress,AssetID from @AllPatchDetails Where ISNULL(AssetID,'''') <> '''' '

  ---- RBA Condition Check -----
  SET @SQL = @SQL + ' IF EXISTS(Select 1 from @RBATicketDetails)
  BEGIN

      UPDATE AM SET AM.PatchScanID =  PM.PatchScan_ID,TicketId = RefID,ModuleType = RefType
      from @RBATicketDetails AM 
      INNER JOIN AM_PatchScan_Master PM ON PM.PatchScan_ID = SessionID

      UPDATE AM SET IsDependent = 1, AM.SmartActionQueueID = ISQ.SmartActionQueueID 
      from  @RBATicketDetails AM
      LEFT JOIN  IM_SmartAction_Queue ISQ ON AM.PatchScanID = ISQ.PatchScanMasterUID 
      LEFT JOIN Adm_Selfhealing_actions ASA with(nolock) ON ASA.UID=ISQ.SelfHealingActionID and isnull(ASA.IsActive,1)=1
      Where ISNULL(ASA.ConditionOrder,0) > 0 and ISNULL(ASA.ConditionCriteria,'''') <> '''' 

      Insert into @MasterPatchDetails (SessionUID,SessionID,PatchId,IPAddress,AssetID,IsConditionMatched)
      Select SessionUID,SessionID,PatchId,IPAddress,AssetID,1 from @RBATicketDetails Where ISNULL(IsDependent,0) = 0

  
      INSERT INTO @DependentTickets(SessionUID,SessionID,PatchId,IPAddress,AssetID,TicketId,ModuleType,SmartActionQueueID,DependentActionOutput,DependentActionStatus,Module,ConditionCriteria,ConditionValue,NextRunDate,ExecutedTime)
      Select DISTINCT AM.SessionUID,AM.SessionID,AM.PatchId,AM.IPAddress,AM.AssetID,AM.TicketId,AM.ModuleType,AM.SmartActionQueueID, tbl.Output as DependentActionOutput,tbl.Status as DependentActionStatus,IsNull(ISQ.Module,''IM'') Module
      ,ASA.ConditionCriteria,ASA.ConditionValue,tbl.NextRunDate,tbl.ExecutedTime
      from  @RBATicketDetails AM
      INNER JOIN  IM_SmartAction_Queue ISQ ON  ISQ.SmartActionQueueID = Am.SmartActionQueueID 
      LEFT JOIN Adm_Selfhealing_actions ASA with(nolock) ON ASA.UID=ISQ.SelfHealingActionID and isnull(ASA.IsActive,1)=1
      LEFT OUTER JOIN (select ISQ1.Output ,ISQ1.Status,ISQ1.TICKETID, ISQ1.ExecutionOrder,ISQ1.SelfHealingMasterID,ISQ1.SmartActionQueueID,ISQ1.CI_ID,ISQ1.NextRunDate,ISQ1.ExecutedTime
          from IM_SmartAction_Queue ISQ1 with(nolock) 
          INNER JOIN Adm_Selfhealing_actions ASQ1 with(nolock) ON ASQ1.SelfHealingMasterID=ISQ1.SelfHealingMasterID AND ASQ1.ConditionOrder=ISQ1.ExecutionOrder
      ) as tbl on tbl.ExecutionOrder=ASA.ConditionOrder and tbl.TicketID=ISQ.TicketID and tbl.SelfHealingMasterID=ISQ.SelfHealingMasterID and ISNULL(tbl.CI_ID,0)=ISNULL(ISQ.CI_ID,0)
      Where IsDependent = 1 and isnull(ASA.IsActive,1)=1 and ISNULL(ConditionCriteria,'''') <> '''' and ISNULL(ConditionValue,'''') <> ''''

      UPDATE @DependentTickets SET IsValid = Case When ConditionCriteria = ''CONTAINS'' then CASE WHEN DependentActionOutput like ''%''+ConditionValue+''%'' then 1 Else 0 End  
           When ConditionCriteria = ''EQ'' then CASE When DependentActionOutput = ConditionValue Then 1 Else 0 End
           When ConditionCriteria = ''NEQ'' then CASE When DependentActionOutput <> ConditionValue Then 1 Else 0 End
           When ConditionCriteria = ''GT'' then CASE When DependentActionOutput > ConditionValue Then 1 Else 0 End
           When ConditionCriteria = ''LT'' then CASE When DependentActionOutput < ConditionValue Then 1 Else 0 End
      END

      Insert into @MasterPatchDetails (SessionUID,SessionID,PatchId,IPAddress,AssetID,IsConditionMatched)
      Select SessionUID,SessionID,PatchId,IPAddress,AssetID,Isvalid from @DependentTickets Where ISNULL(NextRunDate,'''') = '''' and DependentActionStatus not in (''New'',''In-Progress'') 
  END '

  
  

  ----- All Session Patches  
  SET @SQL = @SQL + ' SELECT PSS.UID,PSS.Session_ID ,PSS.PatchID ,PSS.IPAddress ,PSS.Status,PSS.Messages ,PM.PatchID ,PM.Org_ID ,PM.P_Type ,PM.P_Name
  ,PM.P_Args ,PM.P_Version ,PM.P_Description ,PM.P_Vendor    ,PM.P_FilePath ,PM.Active ,PM.InsertDT ,PM.InsertBy ,PM.UpdateDT, PM.IsRemotePath,
  PM.StorageServer,PM.PathType,PM.ProxyServer,PM.FTPUserName,PM.FTPPassword ,PM.UpdateBy,PM.InteractiveDesktop ,IsConditionMatched,AssetID
  ,'' SFTPPassword,'' SFTPUserName, '' SFTPServerName, '' SFTPProtocol
  FROM Patch_Session_Status PSS
  INNER JOIN @MasterPatchDetails MAS ON MAS.SessionUID = PSS.UID
  INNER JOIN Patch_master PM ON PSS.PatchID = PM.PatchID

  WHERE 1 = 1 '

  
  ----Select (@SQL)

  EXEC(@SQL)

  
  END

  GO

  • [USP_AM_GetPatchDetailsForExecution]
  • [USP_AM_GetPatchSessionDetails