CMDBng

CMDBng.sql

-- Schema
CREATE TABLE [dbo].[objectStore](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[source] [varchar](50) NULL,
	[source_key] [varchar](255) NULL,
	[jsonblob] [varchar](max) NULL,
	[metablob] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

-- Data
INSERT INTO [dbo].[objectStore]
           ([source]
           ,[source_key]
           ,[jsonblob])
     VALUES
	('racktables','1','{"id":"1","object_name": "ryglap01","fqdn":"ryglap01.labdomain.net","object_type":"VM","description":"test"}'),
	('racktables','2','{"id":"2","object_name":"ryglap02","fqdn":"ryglap01.labdomain.net","object_type":"VM","description":"test"}'),
	('racktables','3','{"id":"3","object_name":"ryglap03","fqdn":"ryglap01.labdomain.net","object_type":"VM","description":"test"}'),
	('racktables','4','{"id":"4","object_name":"ryglap04","fqdn":"ryglap01.labdomain.net","object_type":"VM","description":"test"}'),
	('racktables','5','{"id":"5","object_name":"ryglap05","fqdn":"ryglap01.labdomain.net","object_type":"VM","description":"test"}'),
	('racktables','6','{"id":"6","object_name":"rygmdc05","fqdn":"rygmdc05.labdomain.net","object_type":"BareMetal","description":"ADDC","os":"windows 2012 R2","os_indicator_version":"6.3.9600.18693"}'),
	('racktables','7','{"id":"7","object_name":"rygmdc19","fqdn":"rygmdc19.labdomain.net","object_type":"BareMetal","description":"ADDC","os":"windows 2016","os_indicator_version":"10.0.14393.1480"}'),
	('racktables','8','{"id":"8","object_name":"ryglov49","fqdn":"ryglov49.labdomain.net","object_type":"VM","description":"Icinga 2 Master","os":"Debian 8.6","os_indicator_version":"3.16.36-1+deb8u2"}'),
	('racktables','9','{"id":"9","object_name":"ryglap04","fqdn":"ryglov49.labdomain.net","object_type":"VM","description":"Icinga 2 Master","os":"Debian 9","os_indicator_version":"4.9.18-1"}'),
	('racktables','10','{"id":"10","object_name":"ryglov50","fqdn":"ryglov49.labdomain.net","object_type":"VM","description":"Icinga 2 Satellite","os":"Debian 8.8","os_indicator_version":"3.16.43-2+deb8u2"}'),
	('racktables','11','{"id":"11","object_name":"www","fqdn":"www.labdomain.net","description":"Public Website","object_type":"Instance","hosts":["rygwap01","rygmap02"]}'),
	('racktables','12','{"id":"12","object_name":"rygwap01","fqdn":"rygwap01.labdomain.net","object_type":"VM","description":"IIS Host for Public websites","os":"windows 2012 R2","os_indicator_version":"6.3.9600.18693"}'),
	('racktables','13','{"id":"13","object_name":"rygwap02","fqdn":"rygwap02.labdomain.net","object_type":"VM","description":"IIS Host for Public websites","os":"windows 2012 R2","os_indicator_version":"6.3.9600.18693"}'),
	('racktables','14','{"id":"14","object_name":"rygmap128","fqdn":"rygmap128.labdomain.net","object_type":"VM","description":"IIS Host for Private websites","os":"windows 2008 R2","os_indicator_version":"6.1.7601.23841"}'),
	('ciscoprime','2394772','{"id":"2394772","deviceName":"switch2","displayName":"switch2.inf.labdomain.net","deviceType":"29xx","cdpNeighbors":[{"switchname":"switch1","nearEndInterface":"Gi1/0/1"}]}'),
	('ciscoprime','2394776','{"id":"2394776","deviceName":"switch1","displayName":"switch1.inf.labdomain.net","deviceType":"29xx","cdpNeighbors":[{"switchname":"switch2","nearEndInterface":"Gi1/0/1"}]}'),
	('puppet','5','{"networking":[{"eth1":{"address":"172.17.13.6"}},{"eth2":{"address":"192.168.95.2"}}],"fqdn":"ryglov49.labdomain.net","os":"Debian 8.6","apache2_version":"2.4","haproxy_version":"1.7.3"}')

--Sample MSSQL Query to return data as a table based on the json blob.
select source
	,source_key
	,JSON_VALUE(jsonblob,'$.fqdn') As fqdn
	,JSON_VALUE(jsonblob,'$.description') As Description
	,JSON_VALUE(jsonblob,'$.object_type') As ObjectType
	,JSON_VALUE(jsonblob,'$.os') As OS
	,JSON_VALUE(jsonblob,'$.os_indicator_version') As ObjectType
	,JSON_VALUE(jsonblob,'$.cpu') As CPU
	,JSON_QUERY(jsonblob,'$.networking') As networking
	,JSON_QUERY(metablob,'$.uptime') As Uptime
FROM ObjectStore