How do I filter a report based on count?

raprohaska · ‎03-02-2017

I need a client facing report that can be filtered based on a count. Specifically we are looking for duplicate configuration items so we want to get where count is greater than 1.

I know we can use script but we are hoping for a report based solution that doesn't require interacting with a developer.

Thanks,

AA

Chuck Tomasi · ‎03-02-2017

You could do a list, grouped by CI. (here's an example of active incidents grouped by caller)

You could also do a pivot where the rows are the CI.

raprohaska · ‎03-03-2017

Right, but I can't say filter where count > 1.

chrissteinkep5 · ‎03-03-2017

Using a Report Grouping is one way to show duplicates but I would hate to be the person who has to go through the report line by line and click up and down arrows. There are other approaches that might be more efficient:

1) Are you on Helsinki or later? Details on duplicate CI's and more is provided out of the box with the CMDB Dashboard

CMDB Health Dashboard for Helsinki | Overview - YouTube

2) scott111 posted this code last year, which would also sort you out. You can put this on a UI Page and then make it available to users via a Dashboard or Menu.

<?xml version="1.0" encoding="utf-8" ?>

<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">

<body>

<div><b><u>Duplicate CI List</u></b>

<br />

<tr bgcolor="#ed0404"><th><b>CI Name</b></th><th><b>CI Count</b></th></tr>

<g2:evaluate jelly="true">

var ciRec = new GlideAggregate("cmdb_ci_hardware");

ciRec.addAggregate('COUNT', 'name');

ciRec.groupBy('name');

ciRec.addHaving('COUNT', 'name', '>', '1');

ciRec.query();

ciRec;

</g2:evaluate>

<j2:while test="$[ciRec.next()]">

<j2:set var="jvar_ci_link" value="cmdb_ci_hardware_list.do?sys_id=$[ciRec.sys_id]"/>

<j2:set var="jvar_ci_list_link" value="cmdb_ci_hardware_list.do?sysparm_query=name=$[ciRec.name]"/>

<tr><td><a href="$[jvar_ci_list_link]" class="linked" style="padding-right:px;">$[ciRec.name]</a></td><td>$[ciRec.getAggregate('COUNT', 'name')]</td></tr>

</j2:while>

</table>

<br />

</div>

</body>

</j:jelly>

raprohaska · ‎03-03-2017

The instance was just upgraded so I'm going to look into the dashboard, right now it shows "No Data to Display". I've created a couple custom pages to do these sorts of things, was just hoping there was something more out of the box.

This was really asked for multiple reasons. Our boss wants to be able to write reports based on aggregates and I thought maybe this would lead to an applied answer to that request.