14 Replies Latest reply on May 14, 2018 9:12 AM by iXion

    API / Powershell to update Baseline

    iXion Rookie

      Hello,

       

      I would like to update my baselines automatically or by clicking a Powershell Script. It seems that the only way could be to use the new API and Powershell.

       

      There is a Add-PatchGroupItem cmdlet that seems to be the key but I have to specify bulletin or KB name. There is no way to get the Ivanti Patches by date or a list of all of the patches.

       

      Is there any way to query the Ivanti Patches with arguments (date before, date after) in order to add them in a baseline with the Add-PatchGroupItem ?

       

      Thanks in advance

       

      Best regards

       

      Gabriel Maret

        • 1. Re: API / Powershell to update Baseline
          cwinning CommunityTeam

          Hello,

           

          We don't have anything created, I'll research and reply back on whether this is possible.

           

          Thanks,

          Charles

          • 2. Re: API / Powershell to update Baseline
            cwinning CommunityTeam

            Hello,

             

            I have SQL query that will list Qnumbers released between 2 dates.  You can also specify which type of patches are listed, the default is Security and Non-Security Patches.  The details of the script will show you how to change this.  You'll have to figure out how to input this list into your PowerShell script used to add these to the Patch Group.

             

            Let me know if you have any questions.

             

            Thanks,

            Charles

            • 3. Re: API / Powershell to update Baseline
              iXion Rookie

              Hi Charles,

               

              Many thanks for the code, I will integrate it with my powershell script in the next days.

               

              Best regards

              • 4. Re: API / Powershell to update Baseline
                iXion Rookie

                Hi Charles,

                 

                I updated my script and your Query and it work fine now, thanks !

                 

                Is there any way to exclude some patches of the query (ie java patches) ?

                 

                Thanks in advance


                Best regards

                 

                Gabriel

                • 5. Re: API / Powershell to update Baseline
                  cwinning CommunityTeam

                  Hello,

                   

                  I'll attempt to come up with a method to exclude patches-products-etc from the queried list, I'll let you know.  The best solution would be to allow exclusion and inclusion Patch Groups in the same scan. Unfortunately, you can't use a baseline and exclusion list at the same time.  Customers have requested this feature, you may want to add your vote to them.  Feature Requests

                   

                  Thanks for copy of the PS script!

                   

                  Best regards,

                  Charles

                  • 6. Re: API / Powershell to update Baseline
                    cwinning CommunityTeam

                    Hello,

                     

                    I've attached a new SQL query that includes some product filtering. There are 3 sections that are commented out, they won't be active until you Uncomment them:

                     

                    1. Temp table search criteria
                    2. Temp table in the FROM section
                    3. The Include or Exclude in the WHERE section. You cannot Include and Exclude at the same time

                     

                    • This is the search criteria section: (Search - 1) Temp table search criteria. Uncomment this section to enable Product filtering.)

                    */

                    if OBJECT_ID('tempdb..#Search') is not null drop table #Search

                    CREATE TABLE #Search 

                        Expression nvarchar(30)  NOT NULL, 

                    );

                     

                    /*

                        You can add more Product name by coping the following and changing ProductName ,(N'ProductName%')

                    */

                    INSERT INTO #Search VALUES  

                    (N'.NET Framework%')

                    ,(N'Google Chrome%')

                    ;

                    • This is the FROM section: (Search - 2) Uncomment this section to enable searching. Temp table in the FROM section)

                    */

                    , #Search

                    • This is from the Include or Exclude section: (Search - 3) Choose one of these. The Include or Exclude in the WHERE section. You cannot Include and Exclude at the same time)

                    AND product.Name LIKE #Search.Expression collate SQL_Latin1_General_CP1_CI_AS

                    AND NOT EXISTS (SELECT 1 FROM #Search WHERE product.Name LIKE #Search.Expression collate SQL_Latin1_General_CP1_CI_AS)

                     

                    Basic testing has been performed, please spot check the output before using.

                    I hope that makes sense.

                     

                    Thanks,

                    Charles

                    • 7. Re: API / Powershell to update Baseline
                      iXion Rookie

                      Charles,

                       

                      Thanks for the help.

                       

                      Your .sql file is empty, could you post the new one ? :-)

                       

                      Best regards

                      • 8. Re: API / Powershell to update Baseline
                        cwinning CommunityTeam

                        Hello,

                         

                        Not sure what happened there, I've attached query to this thread. 

                         

                        Thanks,

                        Charles

                        • 9. Re: API / Powershell to update Baseline
                          iXion Rookie

                          Thanks Charles,

                           

                          It's working fine :-)

                           

                          Best regards

                          • 10. Re: API / Powershell to update Baseline
                            cwinning CommunityTeam

                            Hello,

                             

                            Happy to help!

                             

                            Charles

                            • 11. Re: API / Powershell to update Baseline
                              iXion Rookie

                              BTW this is the two scripts I am using now.

                               

                              Best regards

                              1 of 1 people found this helpful
                              • 12. Re: API / Powershell to update Baseline
                                cwinning CommunityTeam

                                Greatly appreciated!

                                 

                                Thank you,

                                Charles

                                • 13. Re: API / Powershell to update Baseline
                                  iXion Rookie

                                  Hi Charles,

                                   

                                  I am implementing extended logs on my scripts and has a problem with the query that select more than one field.

                                   

                                  DECLARE
                                  @firstDate Datetime,
                                  @secondDate Datetime
                                  
                                  
                                  -- Date time format yyyy-MM-dd HH:mm:ss
                                  SET @firstDate = '2018-04-27 00:00:00'
                                  SET @secondDate = '2018-05-02 23:59:59'
                                  
                                  
                                  SELECT
                                  
                                  
                                  DISTINCT
                                  product.[Name],
                                  product.[ServicePackName] as spID,
                                  patch.[Bulletin] as patchBulletinID,
                                  patch.[QNumber] as patchQNumbers,
                                  patch.[ReleasedOn],
                                  patch.[PatchTypeId]
                                  
                                  FROM
                                  [Reporting].[Patch] patch
                                  INNER JOIN [Reporting].[PatchAppliesTo] pat ON
                                  pat.PatchId = patch.[id]
                                  INNER JOIN [Reporting].[Product] product ON
                                  product.[id] = pat.ProductId
                                  
                                  
                                  WHERE
                                  patch.[Bulletin] <> N'' AND
                                  patch.[QNumber] <> N'' AND
                                  patch.PatchTypeId in (0, 1) AND -- Change to add or remove patch types
                                  patch.[ReleasedOn] BETWEEN @firstDate AND @secondDate
                                  
                                  
                                  ORDER BY
                                  patch.[QNumber]
                                  

                                   

                                  This request return :

                                   

                                  Name spID patchBulletinID patchQNumbers ReleasedOn PatchTypeId
                                  7-Zip 3 x86 Gold 7ZIP-011 Q7ZIP1805 2018-05-01 00:00:00.000 1
                                  7-Zip 9 x86 Gold 7ZIP-011 Q7ZIP1805 2018-05-01 00:00:00.000 1
                                  7-Zip 16 x86 Gold 7ZIP-011 Q7ZIP1805 2018-05-01 00:00:00.000 1
                                  7-Zip 18 x64 Gold 7ZIP-011 Q7ZIP1805 2018-05-01 00:00:00.000 1
                                  7-Zip 4 x86 Gold 7ZIP-011 Q7ZIP1805 2018-05-01 00:00:00.000 1
                                  7-Zip 9 x64 Gold 7ZIP-011 Q7ZIP1805 2018-05-01 00:00:00.000 1
                                  7-Zip 15 x64 Gold 7ZIP-011 Q7ZIP1805 2018-05-01 00:00:00.000 1
                                  7-Zip 15 x86 Gold 7ZIP-011 Q7ZIP1805 2018-05-01 00:00:00.000 1
                                  7-Zip 16 x64 Gold 7ZIP-011 Q7ZIP1805 2018-05-01 00:00:00.000 1
                                  7-Zip 4 x64 Gold 7ZIP-011 Q7ZIP1805 2018-05-01 00:00:00.000 1
                                  7-Zip 18 x86 Gold 7ZIP-011 Q7ZIP1805 2018-05-01 00:00:00.000 1
                                  7-Zip 3 x64 Gold 7ZIP-011 Q7ZIP1805 2018-05-01 00:00:00.000 1
                                  

                                   

                                  The SELECT DISTINCT seems to be not working because each of these column are distinct.

                                   

                                  Do you have an Idea ?

                                   

                                  Thanks

                                   

                                  Best regards

                                  • 14. Re: API / Powershell to update Baseline
                                    iXion Rookie

                                    Hello,

                                     

                                    Any idea regarding the SQL query to get distinct informations regarding the patches ?

                                     

                                    Best regards