How to run a report on model category table to get all the attached models to the every model category

[kkswamy]

Hi,

I have to run a report on Model category[cmdb_model_category] table which should give the data about all the models attached to the individual model category.

PFA screenshot for the reference.

When i run the report i should able to see 316 model names under "Consumable" model category.

[Daniel Slocum]

Good Day,

I'm not sure I follow your question.  To see all of your consumable product models you can navigate to Product Catalog -> Product Models -> Consumable Models.  

Similarly to see a full list of hardware product models you'd navigate to Product Catalog -> Product Models -> Hardware Models.  And, likewise for Software Product Models.  Navigate to Product Catalog -> Product Models -> Software Models.

Do you have a use case which I've misunderstood?  If so, can you reply with a bit more detail?

[kkswamy]

Hi Daniel,

I would like to run a report on the model category table along with to get the related list information(models).

Example: I have "consumable" as one model category, under this model category i have 316 models attached.

Same like i have 250 model categories for 40000 models,

When i run the report, i have to get all the models attached to the individual model categories in a report.

Regards,

KK 

[Daniel Slocum]

Thanks for the additional information.

What you are looking for is somewhat difficult because dot walking to the child records extended from the Product Model table isn't possible.  Also, the Model Category link on the product model table allows for more than one model category to be associated with a product model. 

I think the best option for you is to create a script include which returns all models from the extended model tables (hardware, software, and consumable) into an array.  You can then use the data in the array to query for the associated model categories to each record and once again place those results into an array.  Lastly, you can use the script output as the basis for a report and return the results in the format you desire.  This is cumbersome but doable and will give you the most flexibility.

There are two other options which you may want to consider.

First, you can create a report using the ServiceNow reporting module.  Build the report off of the Product_Model table.  Be sure to include the Model Catagories attribute so you can see the relationship between model categories and the models.  You will be limited however, to data held on the product model table, additional data on the extended tables will not be available to you.  This report is reflected in the attached image.

Second, you can use the ODBC connector and utilize an external RDBMS to execute SQL Queries against your instance to return the data into the desired format.

Daniel

[kkswamy · Accepted solution]

Hi,

I found the solution by myself,

I exported a report from model[cmdb_model] and rest of the things i done in excel sheet,

After i exported the report in excel with first column(A) as Model and second column(B) as "Model categories"

I delimited by(,) on model categories column,

Then i created empty columns between model category columns and copied the model column and paste in the empty columns,

Now i filter out empty cells and copy and paste the cells with data in column A and B

Now i have all the models attached to all the model categories in the system.