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?

[Daniel Needles1]

This is extremely helpful and I lucked out because things are not strictly normalized and the hardware table will cover everything.  

However, this doesn't address how you query fields from two related tables.  There are a lot of means to report via Reports, views, query builder, etc.  However, all of these have issues and really excel only from reporting about one table.  The closest was Query Builder, but it is not at all intuitive and there is no "map" of how the tables relate and there appears to be relationships missing.  

What concerns me the most is the answers go out of their way to ignore this problem.  After a year the implicit answers I have gotten:

1. You never need more than 1 table.

2. A list of means via SNOW is given without specifics.

3. A workaround using 1 table is given due to weak normalization of data in SNOW.

I really wish the culture would be more authentic and acknowledge gaps so it presents the possibility of coming up with a solution in the future.  For now it seems SNOW views the world as all nails for its hammer.  Cut a board?  Just pound through.  Saw?  We need no saw! 

[Daniel Needles1]

OK.  I had a week to mull over this and kick the tires.  So a couple obvious but avoided concepts that I needed to understand.

1.  Reports work on a single table.  You can use views as a workaround but it is imperfect.

2.  Rather than fixing the messed up table join situation, SNOW broke relational database normalization and duplicated certain fields, such as Status and Operational Status all over the place.  These fields are NOT sychronized, which makes double checking and doing all the updates important to maintain CMDB integrity. For example,  cmdb_ci, cmdb_ci_computer, cmdb_ci_server, and cmdb_ci_linux_server all need to be individually updated, otherwise the status info for that CI is corrupt.

3.  That said, the Reports GUI is really quick and easy to deal with.  Also, there is a mostly complete PDF of the SNOW CMDB here - https://www.snow-mirror.com/wp-content/uploads/2016/07/ServiceNow-Data-Model-v3.4.pdf  

4.  With all this I could get all Network stuff via the Network_Gear table and most Servers and workstations via computers.  Popping up a level (thanks to suggestions here) I used the hardware table to get the overview of both.  With tremendous filtering, cmdb_ci would likely work too but the hardware table was near complete with one exception... 

5.  Unfortunately, AWS stuff doesn't seem to be integrated.  For now I used a separate Report and threw things together in a dashboard.  I am hopeful the views will work better than Query Builder did in providing a means to Report on both these sources, but I haven't got there yet. 

[Daniel Needles1 · Accepted solution]

Oops one update.. Couldn't edit the existing post so put the update here...

OK.  I had a week to mull over this and kick the tires.  So a couple obvious but avoided concepts that I needed to understand.

1.  Reports work on a single table.  You can use views as a workaround but it is imperfect.

2.  Rather than fixing the messed up table join situation, SNOW broke relational database normalization and duplicated certain fields, such as Status and Operational Status all over the place.  These fields are references to the same value and kept in sync.

3.  The Reports GUI is really quick and easy to deal with.  Also, there is a mostly complete PDF of the SNOW CMDB here - https://www.snow-mirror.com/wp-content/uploads/2016/07/ServiceNow-Data-Model-v3.4.pdf  

4.  With all this I could get all Network stuff via the Network_Gear table and most Servers and workstations via computers.  Popping up a level (thanks to suggestions here) I used the hardware table to get the overview of both.  With tremendous filtering, cmdb_ci would likely work too but the hardware table was near complete with one exception... 

5.  Unfortunately, AWS stuff doesn't seem to be integrated.  For now I used a separate Report and threw things together in a dashboard.  I am hopeful the views will work better than Query Builder did in providing a means to Report on both these sources, but I haven't got there yet.