How to find the duplicate CIs in a table using Reporting

PraveenPadmanab
Kilo Explorer

‎05-03-2012 04:55 AM

Hi All,

I want to know whether any duplicate entries(CIs) are there in my table. I want to run a query which shows me only the duplicate entries. Is there a way to create a report with only the duplicate entries.

Please let me know whether i can create a script to fetch the duplicate entries from the tables. If yes, where to deploy this script to generate a report which only contains the duplicate entries.

Labels:
0 Helpfuls
  • 7,172 Views
7 REPLIES 7

Sachin Jain1
Giga Contributor

‎11-10-2014 02:05 PM

Hello PraveenPadmanabhan



You can create an UI PAGE using following script and include it as a widget/guage in your hompage/Dashboard.



    

  1. I've created an UI page to display LIST of duplicate CI's using below code     
    2. 

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

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

  4.    <body>   
    5. 

  5.      <div><b><u>Duplicate CI List</u></b>   
    6. 

  6.      <br />   
    7. 

  7.    <table id="sTable"   align="center" style="width:600px">   
    8. 

  8.      <tr bgcolor="#dbe5f1"><th><b>CI Name</b></th><th><b>CI Count</b></th><th><b>Environment</b></th></tr>   
    9. 

  9.      <g2:evaluate jelly="true">   
    10. 

  10.          var ciRec = new GlideAggregate("cmdb_ci");   
    11. 

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

  12.    ciRec.groupBy('name');   
    13. 

  13.    ciRec.groupBy('u_environment');   
    14. 

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

  15.          ciRec.query();   
    16. 

  16.          ciRec;   
    17. 

  17.      </g2:evaluate>   
    18. 

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

  19.    <tr><td>$[ciRec.name]</td><td>$[ciRec.getAggregate('COUNT', 'name')]</td><td>$[ciRec.u_environment]</td></tr>         
    20. 

  20.      </j2:while>   
    21. 

  21.    </table>   
    22. 

  22.      <br />   
    23. 

  23.      </div>   
    24. 

  24.    </body>   
    25. 

  25. </j:jelly> 
    26.

Mr khan
Tera Contributor
In response to Sachin Jain1

‎10-19-2023 02:56 AM

what is sTable???

 

0 Helpfuls

samwallace881
Giga Expert

‎04-27-2017 10:09 AM

You can also reuse Amado's script and print out the list of duplicated CI's with a background script:


function getDuplicateCISerialNumbers() {  


  var answer = new Array();  


  var i = 0;  


  var count = new GlideAggregate('cmdb');  


  count.addNotNullQuery('name');  


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


  count.query();  


       


  while (count.next()) {  


        var sn = count.name;  


        var snCount = count.getAggregate('COUNT', 'name');  


        if (snCount > 1) {  


              answer[i++] = new String(sn);  


        }  


  }  


    gs.print("There are " + i);


  gs.print(answer);  


}


getDuplicateCISerialNumbers()



Then you can copy and paste them into an excel sheet.