10 Replies · Latest reply on Jan 13, 2018 2:36 AM by Göran Lundqvist

    Group By in service portal widget

      We have "Service Status" widget in service portal as shown below, how to group by categorywise , as you can see Health & wealth showing two times.

       

       

      I tried to add group by and order by in server side script BUT it is not working..

       

      data.categories = [];

      var svs = new GlideRecord("cmdb_ci_service");

      svs.addQuery("sys_class_name", "cmdb_ci_service");

      svs.addQuery("parent!=NULL");

      svs.setLimit(options.number_of_services || 50);

      //svs.orderBy("category");

      //svs.groupBy('category.name');

      svs.orderBy("name");

      svs.query();

      var currentCategory = "-";

      var catIndex = -1;

      while (svs.next()) {

      var cat = svs.getValue("category");

        • Re: Group By in service portal widget
          Puneet Goel

          Try using GlideAggregate instead of gliderecord

          • Re: Group By in service portal widget
            Venkat KK

            Thanks Puneet, Can you help me with that since iam new to service portal

              • Re: Group By in service portal widget
                Puneet Goel

                Something like this

                 

                var svs = new GlideAggregate("cmdb_ci_service");

                svs.addQuery("sys_class_name", "cmdb_ci_service");

                svs.addQuery("parent!=NULL");

                ga.addAggregate('COUNT', 'category');

                svs.setLimit(options.number_of_services || 50);

                svs.groupBy('category');

                svs.orderBy("category");

                svs.query();

                 

                 

                var currentCategory = "-";

                 

                 

                var catIndex = -1;

                 

                 

                while (svs.next()) {

                 

                 

                var cat = svs.category.getDisplayValue();

                }

                1 of 1 people found this helpful
                  • Re: Group By in service portal widget
                    Venkat KK

                    Thanks again BUT the result is same, category is not grouping. any help

                      • Re: Group By in service portal widget
                        Puneet Goel

                        I ran this in bacground script and it works for me. Can you try it in background script once

                         

                        var svs = new GlideAggregate("cmdb_ci_service");

                        svs.addAggregate('COUNT', 'category');

                        svs.groupBy('category');

                        svs.orderBy("category");

                        svs.query();

                         

                         

                        while (svs.next()) {

                             var cat = svs.category.getDisplayValue();

                             gs.info('Category:' + cat);

                        }

                        1 of 1 people found this helpful
                          • Re: Group By in service portal widget
                            Venkat KK

                            Thanks puneet in BG it works fine but not on service portal , do i need to set anything on the HTML ? I pasted full widget scripts. HTML:-

                            HTML:-

                            <div class="panel panel-{{::c.options.color}} b">

                              <div class="panel-heading">   

                                <div class="hidden-xs row">

                                  <span class="col-sm-7">${Availability History}</span>

                                  <span ng-repeat="date in ::data.dates" class="col-sm-1 status-hr" ng-bind="::date"></span>

                                </div>

                                <div class="visible-xs row">   

                                  <div class="col-xs-9">${Availability History}</div>     

                                  <div class="col-xs-3 status-column">       

                                    <i class="fa fa-arrow-left" ng-click="c.left()" ng-class="{disabled: (c.index == 0)}"></i>

                                    <span ng-bind="data.dates[c.index]"></span>

                                    <i class="fa fa-arrow-right" ng-click="c.right()" ng-class="{disabled: (c.index == 4)}"></i>             

                                  </div>   

                                </div>                   

                              </div>

                              <!-- mobile view -->

                              <div ng-if="c.updated" class="visible-xs panel-body">          

                                <div ng-repeat="category in ::c.data.categories">   

                                  <h5 ng-bind="::category.label"></h5>                    

                                  <div ng-repeat="service in ::category.services" class="row service">       

                                    <div class="col-xs-9">

                                      <small ng-if="::service.subscribed" class="subscribed" title="Subscribed to updates"><i class="fa fa-envelope"></i></small>       

                                      <a ng-href="?id=service_status_alight&service={{::service.sys_id}}">{{::service.name}}</a>

                                    </div>       

                                    <div class="col-xs-3 status-column">

                                      <span class="fa" ng-class="service.outages[4-c.index].icon" tooltips tooltip-template="{{::service.outages[4-c.index].msg + c.data.dates[c.index]}}"></span>

                                    </div>       

                                  </div>            

                                </div>

                              </div>

                              <!-- desktop view -->

                              <div class="hidden-xs panel-body" ng-repeat="category in ::c.data.categories">

                                <h5 ng-bind="::category.label"></h5>                    

                                <div ng-repeat="service in ::category.services | limitTo:service" class="row service">

                                  <div class="col-sm-7">

                                    <small ng-if="::service.subscribed" class="subscribed" title="{{data.subscribedMsg}}"><i class="fa fa-envelope"></i></small>       

                                    <a ng-href="?id=service_status_alight&service={{::service.sys_id}}">{{::service.name}}</a>

                                  </div>

                                  <div ng-repeat="n in [0,1,2,3,4] track by $index" class="col-sm-1 status-column">

                                    <span class="fa" ng-class="::service.outages[4-$index].icon" tooltips tooltip-template="{{::service.outages[4-$index].msg + data.dates[$index]}}"></span>

                                  </div>

                                </div>

                              </div>

                              <br>

                             

                             

                               <table id=t02 align="center">

                                <td colspan="3" dropzone="true" valign="bottom" id="dropzone100" class="cms_layout_content_footer"><div class="drop-zone-content"><span style="display: none;">IE BUMPER</span><p><font color="#FFFFFF"> </font></p>

                            <p><font color="#428bca"><font size="2"><center><a target=_blank ng-href="?id=all_business_services_status">${Click here to view all services in alphabetical order}</a></center></font></font></p>

                            <p><font color="#428bca"> </font></p></div><div class="drop-zone-content"><span style="display: none;">IE BUMPER</span>

                               

                            </div></td>

                              </table>

                             

                            </div>

                             

                            Server Side :-

                             

                            data.subscribedMsg = gs.getMessage("Subscribed to updates");

                             

                             

                            data.categories = [];

                            var svs = new GlideRecord("cmdb_ci_service_discovered");

                            //svs.addQuery("sys_class_name", "cmdb_ci_service_discovered");

                            //query.addOrCondition("sys_class_name", "cmdb_ci_service_discovered");

                            svs.addAggregate('COUNT', 'category');

                            svs.addQuery("parent!=NULL");

                            svs.setLimit(options.number_of_services || 50);

                            svs.groupBy('category');

                            svs.orderBy("category");

                            //svs.orderBy("category");

                            svs.query();

                            var currentCategory = "-";

                            var catIndex = -1;

                            while (svs.next()) {

                            var cat = svs.getValue("category");

                            if (cat != currentCategory) {

                            catIndex++;

                            currentCategory = cat;

                            data.categories[catIndex] = {};

                            data.categories[catIndex].name = cat;

                            data.categories[catIndex].label = svs.getDisplayValue("category");

                             

                            if (data.categories[catIndex].label == "")

                            data.categories[catIndex].label = gs.getMessage("Service");

                            data.categories[catIndex].services = [];

                            }

                            var svc = {};

                            svc.sys_id = svs.getUniqueValue();

                            svc.name = svs.getDisplayValue();

                            svc.subscribed = isSubscribed(svc.sys_id);

                            var outs = [];

                            for (var i = 0; i <= 4; i++) {

                            var out = new GlideAggregate("cmdb_ci_outage");

                            out.addQuery("cmdb_ci", svs.getUniqueValue());

                            out.addQuery("end", ">=", gs.daysAgoStart(i));

                            out.addQuery("begin", "<=", gs.daysAgoEnd(i));

                            out.addAggregate('COUNT', 'type');

                            out.query();

                            var svcOutageDay = {};

                            svcOutageDay.count = 0;

                             

                             

                            while (out.next()) {

                            var type = out.type;

                            var typeCount = out.getAggregate('COUNT', 'type');

                            svcOutageDay[type] = typeCount;

                            svcOutageDay.count += typeCount;

                            }

                            svcOutageDay.icon = "fa-check-circle";

                            svcOutageDay.msg = gs.getMessage("{0} - no outage - ", svc.name);

                            if (svcOutageDay.count > 1) {

                            svcOutageDay.icon = "fa-plus-circle";

                            svcOutageDay.msg = gs.getMessage("{0} - multiple issues - ", svc.name);

                            } else if (svcOutageDay.outage > 0) {

                            svcOutageDay.icon = "fa-exclamation-circle";

                            svcOutageDay.msg = gs.getMessage("{0} - outage - ", svc.name);

                            } else if (svcOutageDay.degradation > 0) {

                            svcOutageDay.icon = "fa-minus-circle";

                            svcOutageDay.msg = gs.getMessage("{0} - degradation of service - ", svc.name);

                            } else if (svcOutageDay.planned > 0) {

                            svcOutageDay.icon = "fa-info-circle";

                            svcOutageDay.msg = gs.getMessage("{0} - planned maintenance - ", svc.name);

                            }

                            outs.push(svcOutageDay);

                            }

                            svc.outages = outs;

                            data.categories[catIndex].services.push(svc);

                            }

                            data.dates = [];

                            for (var i = 5; i > 0; i--)

                            data.dates.push(new GlideDateTime(gs.daysAgo(i - 1)).getLocalDate().getDisplayValueInternal());

                             

                             

                            function isSubscribed(id) {

                            var subs = new GlideRecord("m2m_sp_status_subscription");

                            subs.addQuery("sys_user", gs.getUserID());

                            subs.addQuery("cmdb_ci_service_discovered", id);

                            subs.query();

                            return subs.hasNext();

                            }