Getting CI count on the basis of class(server,switch etc.,)

[amaradiswamy]

Hi All,

we have a location field in our CMDB table and we are loading CIs to the corresponding tables like cmdb_ci_server,cmdb_ip_switch et.,. Now i want to count the total number of CIs present in a location and per class. For example, if i have 3 locations A, B, C and classes sever, ip switch, now i want to find the no.of servers present in A, No. of IP switches present in Location A and so on. Is there any way to group the data by two attributes(location and class) and get the row count.

Please advice on this. i would be very greatful!

Thanks & Regards,

Swamy

[sergiu_panaite]

Hello Amaradi,

How about a script like this using GlideAggregate for counting:

var gr = new GlideAggregate('cmdb_ci');

gr.addAggregate('count');

gr.addAggregate('count(distinct','location');

gr.groupBy('sys_class_name');

gr.query();

while (gr.next()) {

 gs.print('Locations in class:   ' + gr.sys_class_name + ' = ' + gr.getAggregate('count(distinct','location'));

}

On my own instance this script has the following output:

[0:00:00.027] Script completed in scope global: script

*** Script: Locations in class:   cmdb_ci_aix_server = 1

*** Script: Locations in class:   cmdb_ci_appl = 0

*** Script: Locations in class:   cmdb_ci_app_server_java = 1

*** Script: Locations in class:   cmdb_ci_cluster = 0

*** Script: Locations in class:   cmdb_ci_cluster_node = 0

*** Script: Locations in class:   cmdb_ci_computer = 52

*** Script: Locations in class:   cmdb_ci_computer_room = 0

*** Script: Locations in class:   cmdb_ci_database = 0

*** Script: Locations in class:   cmdb_ci_datacenter = 1

*** Script: Locations in class:   cmdb_ci_db_mssql_instance = 0

*** Script: Locations in class:   cmdb_ci_db_mysql_catalog = 0

*** Script: Locations in class:   cmdb_ci_db_mysql_instance = 0

*** Script: Locations in class:   cmdb_ci_db_ora_catalog = 0

*** Script: Locations in class:   cmdb_ci_disk = 0

*** Script: Locations in class:   cmdb_ci_disk_partition = 0

*** Script: Locations in class:   cmdb_ci_email_server = 0

*** Script: Locations in class:   cmdb_ci_file_system = 0

*** Script: Locations in class:   cmdb_ci_hyper_v_instance = 0

*** Script: Locations in class:   cmdb_ci_hyper_v_network = 0

*** Script: Locations in class:   cmdb_ci_hyper_v_resource_pool = 0

*** Script: Locations in class:   cmdb_ci_hyper_v_server = 0

*** Script: Locations in class:   cmdb_ci_ip_address = 0

*** Script: Locations in class:   cmdb_ci_linux_server = 1

*** Script: Locations in class:   cmdb_ci_memory_module = 0

*** Script: Locations in class:   cmdb_ci_msd = 1

*** Script: Locations in class:   cmdb_ci_netgear = 5

*** Script: Locations in class:   cmdb_ci_network_adapter = 0

*** Script: Locations in class:   cmdb_ci_peripheral = 1

*** Script: Locations in class:   cmdb_ci_printer = 4

*** Script: Locations in class:   cmdb_ci_rack = 5

*** Script: Locations in class:   cmdb_ci_server = 2

*** Script: Locations in class:   cmdb_ci_service = 5

*** Script: Locations in class:   cmdb_ci_snc_component = 0

*** Script: Locations in class:   cmdb_ci_spkg = 0

*** Script: Locations in class:   cmdb_ci_unix_server = 1

*** Script: Locations in class:   cmdb_ci_vcenter = 0

*** Script: Locations in class:   cmdb_ci_vcenter_datacenter = 0

*** Script: Locations in class:   cmdb_ci_vcenter_datastore = 0

*** Script: Locations in class:   cmdb_ci_vmware_instance = 0

*** Script: Locations in class:   cmdb_ci_web_application = 0

*** Script: Locations in class:   cmdb_ci_web_server = 0

*** Script: Locations in class:   cmdb_ci_web_site = 0

*** Script: Locations in class:   cmdb_ci_win_server = 1

*** Script: Locations in class:   cmdb_ci_zone = 0

*** Script: Locations in class:   service_offering = 5

Further manipulation to the data can be done, but this is one way of doing it.

Let me know if this helps.

Regards,

Sergiu

[Alex North]

Note: If you are intending on running this type of glideAggregate frequently, or you have a particularly large CMDB, it is probably worth making sure the column which you are performing the aggregation on (in this case cmdb.location) is indexed a the DB level.

[amaradiswamy]

Hi Penaite,

Thank you for you reply. But i want to count how many Cis are there with the same location in a class. It is giving only location count. Is there any way to find the no.of CIs present in a particular location and in particular class.

Regards,

Swamy

[sergiu_panaite · Accepted solution]

What about this kind of script:

var gr = new GlideRecord('cmn_location');

gr.addNotNullQuery('name');

gr.query();





while (gr.next()){

       var grn = new GlideAggregate('cmdb_ci');

       grn.addQuery('location', gr.sys_id);

       grn.addAggregate('count', 'sys_class_name'); 

       grn.orderByAggregate('count', 'sys_class_name');   

       grn.groupBy('sys_class_name');   

       grn.query(); 

       while (grn.next()) {   

         gs.log('Location:   ' + gr.name.getDisplayValue() + ' has = ' + grn.getAggregate('count', 'sys_class_name') + ' CIs in class: ' + grn.sys_class_name.getDisplayValue());   

       } 

}

gives on my own instance:

[0:00:00.804] Script completed in scope global: script

---

*** Script: Location: 2-10-1 Yurakucho, Chiyoda-ku, Tokyo has = 37 CIs in class: Computer
*** Script: Location: 2-10-1 Yurakucho, Chiyoda-ku, Tokyo has = 1 CIs in class: Rack
*** Script: Location: 755 Hank Aaron Dr SW, Atlanta GA has = 2 CIs in class: Computer
*** Script: Location: San Diego has = 1 CIs in class: JavaServer
*** Script: Location: San Diego has = 1 CIs in class: Business Service
*** Script: Location: 2-12-1 Ookayama, Meguro-ku, Tokyo has = 24 CIs in class: Computer
*** Script: Location: 248 Abbotsford Road, Brisbane has = 20 CIs in class: Computer
*** Script: Location: 248 Abbotsford Road, Brisbane has = 1 CIs in class: Rack
*** Script: Location: Karmelitska 2, Lesser Town, Prague has = 25 CIs in class: Computer
*** Script: Location: Karmelitska 2, Lesser Town, Prague has = 1 CIs in class: Rack
*** Script: Location: 3385 Ross Clark Cir, Dothan AL has = 2 CIs in class: Computer
*** Script: Location: 1390 S Colorado Blvd, Denver CO has = 10 CIs in class: Computer
*** Script: Location: 194 S Dupont Hwy, New Castle DE has = 1 CIs in class: Network Gear
*** Script: Location: 8695 NW 13th Ter, Doral FL has = 4 CIs in class: Computer
*** Script: Location: 3015 GRAND AVE, Coconut Grove FL has = 1 CIs in class: Network Gear
*** Script: Location: 8609 Mills Drive, Miami FL has = 30 CIs in class: Computer
*** Script: Location: 5464 Wendy Bagwell Pkwy, Hiram GA has = 1 CIs in class: Computer
*** Script: Location: 2102 Old 41 Hwy Nw, Kennesaw GA has = 4 CIs in class: Computer
*** Script: Location: 6785 Tara Blvd, Jonesboro GA has = 2 CIs in class: Computer
*** Script: Location: 9159 S Cicero Ave, Oak Lawn IL has = 4 CIs in class: Computer
*** Script: Location: 4395 Fox Valley Center Dr, Aurora IL has = 5 CIs in class: Computer
*** Script: Location: 660 N Wells St, Chicago IL has = 2 CIs in class: Computer
*** Script: Location: 222 Friend St, Boston MA has = 2 CIs in class: Computer
*** Script: Location: 6411 Nw Barry Rd, Kansas City MO has = 5 CIs in class: Computer
*** Script: Location: 3030 High Point Rd, Greensboro NC has = 5 CIs in class: Computer
*** Script: Location: 3030 High Point Rd, Greensboro NC has = 2 CIs in class: Computer Peripheral
*** Script: Location: 3030 High Point Rd, Greensboro NC has = 1 CIs in class: Printer
*** Script: Location: 211 W 56th St, New York NY has = 12 CIs in class: Computer
*** Script: Location: 211 W 56th St, New York NY has = 4 CIs in class: Linux Server
*** Script: Location: 453 Miamisburg Centerville Road, Dayton OH has = 1 CIs in class: Computer
*** Script: Location: 10536 N Kings Hwy, North Myrtle Beach SC has = 4 CIs in class: Computer
*** Script: Location: 750 N Riverside Dr, Clarksville TN has = 14 CIs in class: Computer
*** Script: Location: 4224 Preston Rd, Frisco TX has = 2 CIs in class: Computer
*** Script: Location: 5101 Belt Line Rd, Dallas TX has = 2 CIs in class: Computer
*** Script: Location: 2519 Southwest Fwy, Houston TX has = 3 CIs in class: Computer
*** Script: Location: SHS quadra 5, Bloco E., Brasilia has = 11 CIs in class: Computer
*** Script: Location: Carretera Cancun-Chetumal KM 22, Cancun has = 11 CIs in class: Computer
*** Script: Location: Carrera 54 No. 49 - 31, Medellin has = 20 CIs in class: Computer
*** Script: Location: Rome has = 1 CIs in class: Printer
*** Script: Location: 150 Kennedy Road, Hong Kong has = 8 CIs in class: Computer
*** Script: Location: 150 Kennedy Road, Hong Kong has = 1 CIs in class: Rack
*** Script: Location: Bockenheimer Landstraße 223, Frankfurt has = 28 CIs in class: Computer
*** Script: Location: 75-85 York Street, Melbourne has = 8 CIs in class: Computer
*** Script: Location: 140 George Street, Sydney has = 10 CIs in class: Computer
*** Script: Location: 30 Katharinenstr, Hamburg has = 32 CIs in class: Computer
*** Script: Location: 750 3rd Ave, New York NY has = 2 CIs in class: Computer
*** Script: Location: 750 3rd Ave, New York NY has = 2 CIs in class: Business Service
*** Script: Location: 750 3rd Ave, New York NY has = 1 CIs in class: Data Center
*** Script: Location: 750 3rd Ave, New York NY has = 1 CIs in class: Network Gear
*** Script: Location: 750 3rd Ave, New York NY has = 1 CIs in class: Service Offering
*** Script: Location: 8500 Andrew Carnegie Boulevard, Charlotte NC has = 1 CIs in class: Network Gear
*** Script: Location: 8500 Andrew Carnegie Boulevard, Charlotte NC has = 1 CIs in class: Business Service
*** Script: Location: Denver has = 1 CIs in class: Business Service
*** Script: Location: Via Nomentana 56, Rome has = 28 CIs in class: Computer
*** Script: Location: Via Nomentana 56, Rome has = 8 CIs in class: Windows Server
*** Script: Location: 2500 West Daming Road, Shanghai has = 25 CIs in class: Computer
*** Script: Location: 3 Whitehall Court, London has = 78 CIs in class: Computer
*** Script: Location: 3 Whitehall Court, London has = 1 CIs in class: Rack
*** Script: Location: 27, Boulevard Vitton, Paris has = 78 CIs in class: Computer
*** Script: Location: Bockenheimer Landstraße 123, Frankfurt has = 42 CIs in class: Computer
*** Script: Location: Bockenheimer Landstraße 123, Frankfurt has = 7 CIs in class: UNIX Server
*** Script: Location: Salem has = 1 CIs in class: Business Service
*** Script: Location: 123 West Plaza, Solana Beach CA has = 12 CIs in class: Computer
*** Script: Location: Americas has = 3 CIs in class: Service Offering
*** Script: Location: Emea has = 1 CIs in class: Service Offering
*** Script: Location: Apac has = 1 CIs in class: Service Offering
*** Script: Location: Paradise Road, Richmond, London has = 42 CIs in class: Computer
*** Script: Location: Paradise Road, Richmond, London has = 2 CIs in class: Service Offering
*** Script: Location: 820 E St., Central, San Diego CA has = 33 CIs in class: Computer
*** Script: Location: 3795 Fairmount Ave., City Heights/Weingart, San Diego CA has = 9 CIs in class: Server
*** Script: Location: 3795 Fairmount Ave., City Heights/Weingart, San Diego CA has = 3 CIs in class: Mass Storage Device
*** Script: Location: 3795 Fairmount Ave., City Heights/Weingart, San Diego CA has = 2 CIs in class: AIX Server
*** Script: Location: 2920 Burgener Blvd., Clairemont, San Diego CA has = 3 CIs in class: Computer
*** Script: Location: 925 W. Washington St., Mission Hills, San Diego CA has = 5 CIs in class: Computer
*** Script: Location: 4616 Clairemont Drive, North Clairemont, San Diego CA has = 10 CIs in class: Server
*** Script: Location: 101 W. San Ysidro Blvd., San Ysidro, San Diego CA has = 1 CIs in class: Network Gear
*** Script: Location: 5148 Market St., Valencia Park/Malcolm X, San Diego CA has = 4 CIs in class: Computer
*** Script: Location: 200 West 1st Street, Santa Ana, CA has = 29 CIs in class: Computer
*** Script: Location: 200 West 1st Street, Santa Ana, CA has = 21 CIs in class: Printer
*** Script: Location: 832 Imperial Beach Blvd, San Diego, CA has = 13 CIs in class: Computer
*** Script: Location: 4430 Camino De La Plaza, San Ysidro, CA has = 10 CIs in class: Computer
*** Script: Location: 13518 Poway Road, Poway, CA has = 13 CIs in class: Computer
*** Script: Location: 12305 Dennery Road, San Diego, CA has = 17 CIs in class: Computer
*** Script: Location: 542 Crescent Road, Escondido CA has = 7 CIs in class: Computer
*** Script: Location: 100 Park Boulevard, San Diego, CA has = 19 CIs in class: Computer
*** Script: Location: 100 Park Boulevard, San Diego, CA has = 5 CIs in class: Printer

Regards,

Sergiu