Cross Table Device Counts

[Daniel Needles1]

I want to get a simple count of devices across multiple tables.  For  example, using Class in Network Gear and Servers tables and ideally also get printers, load balancers, etc.   After a year of searching I haven't found a good answer outside of an OAuth connection and reporting in another database.  I also got ODBC working, but it is way too slow and imposes too much over head.

Specifically, what tool can be used to perform mildly complex queries on more than one table?  I've tried Query Builder, Views and Reports.  All seem to fall down once you start doing anything in multiple tables or have to do the mildest post processing such as grouping several Operating System values into Linux and windows using simple substrings and/or regular expressions..   Further, I find the community is shy when it comes to saying - no, we don't do that well and this is what we use instead.  

For example, in theory if I could use Class in cmdb_ci_server and Class in cmdb_ci_netgear, I could get something close to this if I could do "minor" post processing and or mild text parsing on the select

Ideally, I would like to expand it and abstract it to something like this:

 Is anyone doing any level of complexity like this when it comes to the CMDB within the ServiceNow tool set?   If not, what do you use to do decent reporting?

I mean counting device totals by type is one of the most basic CMDB functions, no?  Or am I missing something completely here?

[emir]

Hi, the CMDB in itself is a hierarchical collection of tables. You can view the hierarchy by viewing the CI Class Manager.

If you go to the Server Table, you can see all Servers there. They are further classified into Linux servers, etc by being placed into child tables of the Server Class. Example:

So, the 152 Linux Servers are contributing to the total of 520 Servers, who are contributing to the total of 40595 Computers, etc..

In the Sample you provided above, one could think there are 15K Servers and 20K Computers, but since Servers are Computers, the number is included.

Now, having said all of this. One could simply run a report on Hardware and group by class and get this data by the actual class.
However, you will not be able to group to a higher classification in that level of report as you seem to be wanting.

A Dashboard that shows a widget per major class you are interested is probably a much better out of the box solution.

Sorry if I have over explained this, but wasn't sure of your familiarity with the CMDB.

Cheers!

ee

[Daniel Needles1]

Emir,

    Thanks for the feed back!  My question is how can I create a simple report either in ServiceNow or elsewhere via export that provides a simple CMDB Inventory of Devices including Servers and Network devices. 

This seems like a basic ask for any CMDB.  I understand the hierarchy but still do not have a way to make this very basic CDMB data into a single report.

Do ServiceNOw folk simply go around not having the ability to get this data via a report?  How do they handle it?  I got to think for everything from purchasing to licensing this sort of thing would be needed.

[Daniel Needles1]

There are time when using a single table just won't do it.  This is a normal and trivial issue with any SQL enabled database.  For example, if I use ODBC against ServiceNOW I can do a simple SELECT with SubStr() and a simple UNION between the different tables to get what I want. It takes all of 10 minutes to configure and is completely reusable. How do you do the same with multiple tables and string modification in Reports, Query Builder, Views, or anything else that can be used in ServiceNOW? Or is this impossible or not feasible. If not feasible, what do people do to get basic device counts?

[emir]

People use Views, List Filters, Reports, Dashboards, CI Class Manager, Health Dashboards, Licensing Pages,Workspaces, etc, depending on the use case.

If you simply want to know the all the Servers and Network Gear, you can write a report to do that (see below for steps).

Physical and Logical model of the CMDB is not the same. Tables are partitioned, optimized for performance, etc, and the system knows how to best combine them for querying. You can achieve the same via JDBC but you need to know exactly how to best query the schema (see end of this post).

When I was a Customer my internal Security team advised we should never allow direct SQL access to the DB, NOW has listened and has deprecated those APIs (other than the JDBC). Direct access will bypass field audits, etc...

To get you your data, let's create a Report, let's make it a Pivot Table. We will start at Hardware table, as that is a common parent for Servers and Network Gear. No need to go to the root class as that can be a rather large collection of classes.

Then lets set our Rows and Columns

At this time you should see something like this

But since you want only Servers and Network Gear lets add 2 more filters to our data:

It is important to use IS A operator as that will bring Server and any of its descendants.

After my filter the Data looks like this:

Now you have a query you could use from wherever you are accessing the system(JBDC), but why not just do it form here, since you can drill into the data. You can add this to a Dashboard or publish the report periodically.

There is no need for joins or any other SQL magic.

I am still not sure if I completely understand your ask, but that is what I would do if someone asked me how many Servers and Network Gear I have in the CMDB.

I hope this helps!