6 Replies Latest reply on Jul 26, 2018 8:36 AM by phoffmann

    Biggest policy task

    cosmin Rookie

      Hi everybody,

       

      Does anyone of you have a patch management task, Policy type, on more than 32k devices?

      If yes, does it working properly?

       

      If so, could you please share your DB type, DB size and LDMS version?

       

      Thank you!

        • 1. Re: Biggest policy task
          phoffmann SupportEmployee

          Yes - we can (and do) have estates with over 50k+ nodes that are patching quite happily.

           

          The questions you should look at / answer are ...

          • HOW do you currently approach your patching? (My guess is "scheduled task" rather than Autofix, the latter being what you SHOULD use with that nodecount).

            Background here is that a scheduled TASK (i.e. "go forth and repair this") only tends to handle about 60 devices in parallel ... it's slow & generally not advised for large environments. What you want to do is to make use of Autofix (which is client driven) ... and maybe have a policy task for "run vulnerability scan now" (and that'll talk to clients fast) so that your clients scan for & fix your vulnerability quickly.

            In order to make things even faster, you can use a specific vulnerability setting which - rather than scanning for ALL vulnerabilitise, scans for "the 5-10 (or whatever) you care about)" and then autofix those.

            There's a lot to learn (and get right / get wrong) in the patching world - so having more context & background from you here would be quite helpful.

           

          • What's the problem / not working exactly? That'd help a lot in understanding what your issue actually is.
          • Why is DB size relevant here exactly? Larger node counts will have larger databases. You can expect roughly 5-10 MB of data PER CLIENT ... potentially more, depending on what you use & what you configure (ESPECIALLY if you use the "Gather Historical" function that'll blaot your DB something fierce).

           

          ... also please get into a habit of always posting your major & minor version as that can always help (i.e. "I'm on LDMS/EPM 2017.3 + SU2" for instance).

          • 2. Re: Biggest policy task
            cosmin Rookie

            Thanks a lot for answer!

             

            Maybe we do not provide many details, I agree. It was a general question, addressed mostly to administrators, maybe to exchange a bit some experience.

            Your answer here is more than we expected!

             

            However, to complete a bit missing informations, here it is:

             

            1. We are running LDMS 2016.3 SU6 on a w2012 server,

            2. DB is on SQL 2017.

            3. We do schedule and not autofix - there is a long discussion, mostly internal one and hopefully we will change the approach to what you've suggested.

            4. The problem is that we got with one of our task which target 35000 devices the next issue:

             

            https://docs.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-2017

             

            The message is: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

             

            5. This task is targeting 2 console queries which outputs are together around 35000 devices.

            6. Because looks like we have to many values in the IN clause using current approach, we thing to split current task based on those two console queries. So, one task per one console query.

            7. Our DB colleagues did some tests and see that it is ok if IN clause have less than 31000 entries and not ok if have more. This task generate 32473 values in the IN clause.

            8. Don't tell me that we are undersized with our SQL server resources.

            • 3. Re: Biggest policy task
              cosmin Rookie

              Currently we are working with DB colleagues to correct some of our resource limitations on DB side. We will post here our findings.

              • 4. Re: Biggest policy task
                phoffmann SupportEmployee

                Yeah - so SQL Server runs into a bit of a brick-wall if it has to handle large "IN" clauses. That's one way to do it for sure.

                 

                As it so happens, we've modified behaviour around this (so as not to bring SQL Server to its knees) as of EPM 2018.1 - which limits / breaks queries up into results of 1,000 at a time. That won't do you much good at present since you're on 2016, but at least "we've done something on our end" to help you once you upgrade.

                 

                You MAY well want to break up your massive queries. That will certainly help things along.

                 

                I don't know your SQL resources, so I don't know if (/how badly) they're undersized.

                 

                But yes - rather than a "REPAIR TASK" you will really want to move over to AutoFix (it's a steady class & theme we're educating on @ interchanges, for instance), because it's a LOT faster (doesn't require the Core to do much other than handle policies) and most of the work is offloaded onto the clients. Plus, you're not using "old tech" that's limited to handling only 60-odd connections at once.

                 

                Between those two, you should have your recipe for success (and you can further speed things up by using Custom Groups for vulnerabilities to scan for/against, so you don't scan against all 5,000+ (OK - I'm exagerating) vulnerabilities for the relevant Windows OS, if all you care about is "those 5 things") .

                • 5. Re: Biggest policy task
                  cosmin Rookie

                  For the moment we will break the task in smaller tasks, we will try to work on resources on SQL server. Hopefully this will work for now.

                  It looks that it is a strong correlation between setting up a max allocation size of RAM and this SQL issue(The query processor ran out of internal resources and could not produce a query plan.). In this case, setting the max allocated memory to 250GB it looks like is raising also the max value for IN clause. Maybe is an internal trigger of SQL. We'll see.

                   

                  Next -> prepare Autofix scenario(one of the most important stops here is that many computers from production lines or test labs need as many as possible computing power and this Autofix can affect them) -> maybe we can use an Autofix approach for normal office computers and for production/test labs computer to use the old fashion way.

                   

                  Last but not least, we plan to upgrade this autumn to 2017.x and in first quarter of 2019 to EPM 2018.1. Not sure yet if worth to skip 2017.x and going directly to 2018.1.

                   

                  Thank you again for taking your time to answer to us!

                  I hope these kind of discussions are helping others as well.

                  • 6. Re: Biggest policy task
                    phoffmann SupportEmployee

                    There's not a lot of computing power involved in autofixing.

                     

                    • Hey, I need to scan those 5 vulnerabilities.
                    • Oh, I'm vulnerable to 3 of them.
                    • Oh, I need to fix those right away - okie dokie ...

                     

                    ... it just makes life for the CORE a lot simpler. The clients - by and large - aren't going to see a profound change. Scanning for 5-10 vulnerabilities isn't exactly going to tank a box. And installing the patch(es) will be the same no matter how you get to that point, really.

                     

                    So I don't think it should be a super-massive impact.

                     

                    But yeah - sounds like you've got a plan on how to progress. Solid .