How to combine CMDB tables?

[navb]

We have two CMDB tables logs in to Splunk

1.  CMDB Business application - Business related info

2. CMDB Rel - Relationship table

We need business application info along with host name

CMDB Business application table fields are,

AppNumber  AppName    AppOwner     ServerStatus

     1                          APP 1                                 X                                 Operational
     2                          APP 2                                 Y                                 Operational
     3                          APP 3                                 Z                                 Operational

CMDB Relationship table fields are,

AppParent       AppChild

    APP 1             APP 1 Production
    APP 2            App 2 Dev
    APP 3            APP 3 Test
                            HostName 1
                            HostName 2 
                            HostName 3

The common Fields  from these tables are ApplicationName  and AppParent(Which is the application name). We need to combine the Application number from the business application with hostnames. Is there any way to combine these two tables via Splunk search?

There is no direct relationship between  AppNumber or AppName  with HostNames.

 These two CMDB tables are in different sourcetype from same Index.

[yuanliu]

First, the illustrated CMDB Relationship table is nonsensical.  This is more obvious if I put your illustration in strict table format:

AppParent	AppChild
APP 1	APP 1 Production
APP 2	APP 2 Dev
APP 3	APP 3 Test
	HostName 1
	HostName 2
	HostName 3

The last three rows contain no key other than hostname; and HostName appears in AppChild column.  There is no solution to your problem under these conditions.

Now, If the table is like the following, you can hope to establish some sort of relationship between the Business application table and this table.

AppParent	AppChild	HostName
APP 1	APP 1 Production	HostName 1
APP 2	APP 2 Dev	HostName 2
APP 3	APP 3 Test	HostName 3

Let us know.

[navb]

Hello yuanliu,

Here is the updated Rel table fields,

AppParent	AppChild
APP 1	APP 1 Production
APP 2	APP 2 Dev
APP 3	APP 3 Test
APP 1 Production	HostName 1
APP 2 Dev	HostName 2
APP 3 Test	HostName 3
APP 1 Production	HostName 4
APP 2 Dev	HostName 5
APP 3 Test	HostName 6

[yuanliu]

OK.  Now I can see the intention.  But if these tables are in production, the designer needs to be fired😏.  The second table cannot possibly be a single table.

In completely normalized form, the intended CMDB can be represented in three tables like these:

Table 1

AppNumber	AppName	AppOwner	ServerStatus
1	APP 1	X	Operational
2	APP 2	Y	Operational
3	APP 3	Z	Operational

Table 2

AppParent	AppChild
APP 1	APP 1 Production
APP 2	APP 2 Dev
APP 3	APP 3 Test

Table 3

AppChild	host
APP 1 Production	HostName 1
APP 2 Dev	HostName 2
APP 3 Test	HostName 3
APP 1 Production	HostName 4
APP 2 Dev	HostName 5
APP 3 Test	HostName 6

Because (Dev, Test, Production) is an extremely limited set, you can probably merge Table 1 and Table 2 into one slightly bloated one without sacrificing much efficiency.  But Table 2 and Table 3 cannot easily merge, and definitely not in the way illustrated.

Table 2 is a static table best represented in a lookup.  It can be file based or KV store based.  I'll call this lookup table2.  Dynamic tables 1 and 3 are represented in the same index as different sourcetypes.  I'll call them sourcetypes table1 and table3.  I'll call the index cmdb.

The following is a prototype to associate table 1 with table 3 (via table 2):

index=cmdb sourcetype IN (table1, table3)
| lookup table2 AppParent AS AppName ``` OUTPUT AppChild ```
| stats values(host) as host values(AppNumber) as AppNumber values(AppName) as AppName values(AppOwner) as AppOwner values(ServerStatus) as ServerStatus by AppChild
| mvexpand host

 I now have doubts whether ServerStatus should be in Table 1.  But that's not a Splunk question.  The difference is: If ServerStatus is also in Table 3, Table 1 is also static and should be put in a CSV file or KV store as lookup.