9 Replies Latest reply on May 19, 2017 7:09 AM by phoffmann

    How to group in a query/report

    RobLent Apprentice

      So I have a query that pulls back information I need about patching.

       

      Great!

       

      But how do I group the results by server name?

       

      Currently it replicates the server name for each line of the results.

       

      There is a button in the query window that says group but it is always greyed out.

       

      LDMS 2016.1

        • 1. Re: How to group in a query/report
          phoffmann SupportEmployee

          A bit more information / screenshots to know what you're talking about / what you're after would be useful here .

          • 2. Re: How to group in a query/report
            RobLent Apprentice

            Ah...  good point.

             

            So I have a query that returns what I need.  (Ignore the date field at the moment as I am testing)

             

             

            I have the columns I need in the results part below:

             

             

            Now the results are obviously not quite what I would like as each result is on its own line thus:

             

             

            What I would like to do is have a 'grouping' of all DRDC01 results like I have seen in some of the standard reports.

             

            I can load this query in a report but obviously this just returns the same effect.

             

            Hope I am making sense?

            • 3. Re: How to group in a query/report
              RobLent Apprentice

              So I have now realised that this does not return what I wanted dues to the 'OR' fro the codes. 

               

              Is there a way to put the codes on one line?

              • 4. Re: How to group in a query/report
                phoffmann SupportEmployee

                No - the problem isn't due to multiple values in multiple lines. That sort of report can't be grouped in that fashion.

                 

                The report is "accurate" based on your conditions. It's a case of:

                - Show me everything that has a status of "0"

                - Show me everything that has a status of "1"

                - Show me everything that has a status of "2"

                - Show me everything that has a status of "30"

                 

                (Which is fine) ... the Core then does that, and gives you a result along the lines of:

                 

                Device NameStatus CodeOther Information
                Computer_11Message_1
                Computer_11Message_2
                Computer_130Message_456

                 

                ... since the "unique element" is the combination of status/action code & the message, it makes prefect sense that you'd get multiple lines of data for each single device potentially.

                 

                If you right-click on the query and click INSPECT, you will see the SQL we generate for the query ... you can (ab-)use that as a starting basis to play / fiddle with (or bribe your local DBA with a coffee or whatever they take) to help you with what you're after.

                 

                If you're looking for a "collapsible tree" type functionality -- the LDMS Console doesn't do that. That'd be more something for XTraction or so.

                 

                Let's flip this around ... what is it you're trying to do / achieve? Give us a fuller story and maybe we can help you better .

                 

                ===============

                 

                P.S.: - If you're trying to get the "Current Date - 30 days" type function to work in the Console, this is the Syntax to use:

                "COMPUTER"."Last Updated by Inventory Server" > "GetDate()-30"

                 

                ... that way you don't have to edit your LD query all the tome .

                • 5. Re: How to group in a query/report
                  avasile Apprentice

                  You can change the ORs to ANDs. Click Edit for the line you want to change to an AND and then from the drop down select AND.

                  2017-05-18_10-50-55.png

                  • 6. Re: How to group in a query/report
                    phoffmann SupportEmployee

                    ... just be careful with your logic.

                     

                    A single field (i.e. - "Status") cannot be both "1" AND "30" at the same time.

                     

                    If you need help building your query, let us know what you're trying to do / pull from the DB & we'll try to help you along a bit.

                     

                    (Also - getting friendly with your local DBA and bribing them with doughnuts or whatnot ... a good way to have someone local to explain to you those things) .

                    • 7. Re: How to group in a query/report
                      RobLent Apprentice

                      Really appreciate all the replies and advice.

                       

                      If I was dedicating more time to this I reckon I could pick it up faster but I am having to dip in and out as I get a few minutes. 

                       

                      avasile thanks. I did know that but then with an 'AND' it need to match all criteria.

                       

                      phoffmann this is what I am trying to do but maybe I am trying the wrong way.

                       

                      We have now got our patching of servers, well our test ones anyway, working pretty automatically with a script that snapshots and patches the servers etc.

                       

                      What we want now is for LDMS to then generate a report for that day, which is where I was using GetDate(), of servers patched with successes and failures hence me wanting to put in the codes I referred to. I also included 'needs a reboot' code too.

                       

                      I was then going to schedule this to run each night and email to the relevant people who need it.

                       

                      The logic of what I want to do is there but maybe not the logic of how to do it in LDMS! 

                      • 8. Re: How to group in a query/report
                        RobLent Apprentice

                        So I have found a report in the standard one that almost does what I want but I need to change some of the options/fields.

                         

                        The Remediated Vulnerabilities Report looks almost bang on but I would love to be able to change this to have defaults instead of needing some input such as Group by Device and the dates fields.

                         

                        Time to try and work out the code behind this I suppose. 

                        • 9. Re: How to group in a query/report
                          phoffmann SupportEmployee

                          Remember - as long as you COPY a report first, you can then edit it / change it as per your heart's content.

                           

                          Also - a fair few reports use raw SQL (in the reporting engine) to get to their data. May be a good starting point, so you don't have to start from 0 .