Service Desk Practitioners Forum
cancel

Another SQL Statement

SOLVED
Go to solution
Highlighted
Fepelof_1
Respected Contributor.

Another SQL Statement

Hi there,

our CMDB categories is disposed in a multi-level tree.
Example: Level 1 - Server
Level 2 - Windows OS
Level 2 - UNIX OS

When a Windows Server is registered, its category is saved with the child category (Level 2 Category). I want to make a SQL Query that brings me all Servers (level 1) in my CMDB. How can I make it ? Where is the field that records the parent category of a CI ?

thanks
Fep
1 REPLY
George M. Meneg
Acclaimed Contributor.
Solution

Re: Another SQL Statement

Hello Fep,

The parent code is stored on the column RCD_RCD_OID on rep_codes. As an example, the following query will fetch CI ID, CI Searchcode, CI Category and CI Category Parent

select cit.CIT_ID as ID, cit.CIT_SEARCHCODE as SEARCHCODE, rct1.RCT_NAME as CATEGORY, rct2.RCT_NAME as CATEGORY_PARENT
from ITSM_CONFIGURATION_ITEMS cit inner join rep_codes_text rct1 on cit.CIT_CAT_OID = rct1.RCT_RCD_OID
inner join rep_codes rcd on rct1.RCT_RCD_OID=rcd.RCD_OID
inner join rep_codes_text rct2 on rcd.rcd_rcd_oid=rct2.rct_rcd_oid
menes fhtagn