Intro

Server CI data are collected in two ways:

periodic SCCM data import (federated data about Windows servers)
periodic SNMP discovery (instrumented data about UNIX servers)

Use Cases

The initial requirement is to:

deliver an updated list of servers (Infrastructure groups, Windows and UNIX)

...to which ITIL users can associate incidents, changes, or problems, as with other CI types.

Fields

These are the target fields, and are subject to the following conditions:

Servers

Business Service Field Label	Rules
"Serial Number"	text, must be unique. Not normalized for case, characters, etc, so must be exact.
"Name"	text
"State"	assumed 'Active' if inserted/updated
"Functional Sponsor"	reference to a sys_user record in ServiceNow
"Assignment Group"	reference to a sys_user_group record, assumed to be the first matching 'INF Windows Systems'
"Service Owner"	assumed manager of the 'INF Windows Systems' group

Data Sources

CMDB: SCCM Servers

URL

jdbc:sqlserver://SCCM;databaseName=SCCM_ITS

Query (initial)

SELECT     Upper(v_GS_SYSTEM.Name0) + '.' +  Upper(v_gs_System.Domain0) 'Name',   u_Operating_Systems.OperatingSystem   'Operating System Version', v_GS_OPERATING_SYSTEM.CSDVersion0 'Patch Level', .
                       V_GS_PC_BIOS.SerialNumber0 'SerialNumber'
FROM         v_GS_SYSTEM INNER JOIN  v_GS_OPERATING_SYSTEM ON v_GS_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
                   inner JOIN
             v_GS_PC_BIOS on v_GS_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID
             join
              u_Operating_Systems on substring(v_GS_OPERATING_SYSTEM.Version0,0,4 ) =  u_Operating_Systems.VersionNumber
order by 'Operating System Version'

User

s_Service-Now2SCCM (see Windows group re: credentials)

Data Contract

The 'INF Windows Systems' department (Infrastructure group) is responsible for the source data source; the technical POC is Julio Valdes.
data will be loaded daily at midnight

Transformation Logic

serial number, os version, and name are directly mapped.
coalesce on serial number (target table enforces uniqueness).
name, per the query, is the host + first FQDN suffix of the machine.
os family is always 'Windows'
group is always 'INF Windows Systems'
ci owner is always manager of 'INF Windows Systems'