Get started with Jira Service Management for admins
Your first stop for learning how to get started with Jira Service Management.
Data Manager - SCCM Adapter
Assets Data Manager for Jira Service Management Cloud is a Premium and Enterprise only feature that is currently in Open Beta development. View and vote on our list of upcoming features.
If you find a bug or have questions, please reach out to Atlassian Support or the Atlassian Community.
Introduction
The SCCM (Microsoft) SQL Adapter is a tool produced by Microsoft that is used for configuration management. It uses a direct connection with the SCCM database to bring data into Assets Data Manager.
The account connecting to the database needs to be granted Read Only access to the SCCM Database.
How do I connect this tool to Assets Data Manager?
Follow this procedure to connect this tool to Assets Data Manager using the custom-built Adapter:
Gather all of the information listed in the Data Manager Fields section, such as the Name, Object Class, Data Source Name, and Data Source Type.
Gather all of the information in the SCCM (Microsoft) SQL section - this may require consultation with the subject matter expert (SME) for SCCM (Microsoft) SQL.
Within Data Manager:
Create a new job by selecting the Adapter that matches your tool.
Configure all of the required fields with the appropriate information.
Within the SCCM (Microsoft) SQL Adapter:
Follow all of the steps listed in the Authentication and Authorisation section, below, to properly configure Authentication and Authorisation.
Review the information in the API Call section, below, and ensure the endpoints are available.
Review the information in the Fields Retrieved section, below.
Each time this job is run, the data your have selected will be brought into Data Manager using the configured Adapter and become Raw Data.
Data Manager Fields
You will need to specify the following information from Assets Data Manager:
Name - the name of the Connection, visible as the job name in Adapters.
Object Class - the name of the Object Class you want to the data to be loaded into.
Data Source Name - The type of data being created; which is usually the tool name, e.g AD, Qualys etc. Note: This can be the same as Name.
Data Source Type - What type of data is Data Platform providing? This is usually the name of the tool (e.g. SCCM, JSMAssets, Qualys) or the discipline (e.g. Asset, CMDB).
SCCM (Microsoft) SQL Fields
You will need to specify the following information from Endpoint Configuration Manager:
Destination table - field is defined by the user as the table in the Asset Data Manager staging database where the content from SCCM is transferred to.
Integrated security - user need to specify whether this field remains checked or unchecked.
Checked - data adaptor connects to the target SQL server using Windows Authentication through the Security Support Provider Interface (SSPI). You will need to specify target database address and target database name in this case.
Unchecked - logins are created in SQL Server that aren't based on Windows user accounts. You need to specify the database username and password in addition to the target database address and the target database name. The above option allows the user to test the connection (test connection button) to the target SCCM database once the database username and password has been entered.
Transfer script – by default the transfer script is auto-generated. If unchecked, user can edit the table attributes defined at the back-end.
Postscript – by default the postscript is auto-generated. If it is unchecked, it allows for the string manipulation and adornment of the records that are in the table at the back-end database.
Standard SQL Query
This is the standard SQL query used in the OOTB SQL connector, included here for reference.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SELECT cmp.machineid AS MachineID,
LEFT(RTRIM(LTRIM(ISNULL(cmp.Name00, 'Unknown computer'))), 256) AS ComputerName, LEFT(RTRIM(LTRIM(
CASE WHEN cmp.domain00 IS NULL THEN 'Unknown domain' WHEN LEN(cmp.domain00)=00 THEN 'Unknown domain' ELSE cmp.domain00 END)), 100) AS Domain,
LEFT(RTRIM(LTRIM(cmp.Manufacturer00)), 128) AS Manufacturer, LEFT(RTRIM(LTRIM(cmp.Model00)), 128) AS ModelNo,
CASE e.ChassisTypes00
WHEN 1 THEN 'Other'
WHEN 2 THEN 'Unknown'
WHEN 3 THEN 'Desktop'
WHEN 4 THEN 'Low Profile Desktop' WHEN 5 THEN 'Pizza Box'
WHEN 6 THEN 'Mini Tower'
WHEN 7 THEN 'Tower'
WHEN 8 THEN 'Portable'
WHEN 9 THEN 'Laptop'
WHEN 100 THEN 'Notebook'
WHEN 11 THEN 'Hand Held'
WHEN 12 THEN 'Docking Station'
WHEN 13 THEN 'All in One'
WHEN 14 THEN 'Sub Notebook'
WHEN 15 THEN 'Space-Saving'
WHEN 16 THEN 'Lunch Box'
WHEN 17 THEN 'Main System Chassis' WHEN 18 THEN 'Expansion Chassis' WHEN 19 THEN 'SubChassis'
WHEN 200 THEN 'Bus Expansion Chassis' WHEN 21 THEN 'Peripheral Chassis' WHEN 22 THEN 'Storage Chassis'
WHEN 23 THEN 'Rack Mount Chassis' WHEN 24 THEN 'Sealed-Case PC'
ELSE 'Unknown' END AS ChassisType,
LTRIM(RTRIM
(CASE WHEN b.SerialNumber00 IS NOT NULL
THEN b.SerialNumber00
ELSE e.SerialNumber00 END)) AS SerialNumber,
LEFT(RTRIM(LTRIM(
CASE WHEN CHARINDEX('|', os.Name00) > 00 THEN SUBSTRING(os.Name00, 1, CHARINDEX('|', os.Name00)-1) ELSE os.Name00 END)), 128) AS OperatingSystem,
LEFT(RTRIM(LTRIM(os.CSDVersion00)), 128) AS ServicePack, LEFT(RTRIM(LTRIM(os.Version00)), 128) AS OperatingSystemBuild, LEFT(RTRIM(LTRIM(b.SMBIOSBIOSVersion00)), 128) AS SMBIOSBIOSVersion,
ISNULL(cmp.numberofprocessors00, 1) AS NumberOfProcessors,
CEILING (m.TotalPhysicalMemory00 / 9500) / 256 * 256 AS TotalMemory, (RTRIM(LTRIM(cmp.UserName00)), 128) AS LastLoggedOnUser, wks.LastHWScan AS InventoryDate
FROM Computer_System_data AS cmp
LEFT OUTER JOIN dbo.WorkstationStatus_DATA AS wks ON wks.MachineID = cmp.MachineID LEFT OUTER JOIN Operating_System_data AS os ON os.MachineID = cmp.MachineID
LEFT OUTER JOIN dbo.PC_Memory_DATA AS m ON m.MachineID = cmp.MachineID
LEFT OUTER JOIN dbo.System_Enclosure_DATA AS e ON e.MachineID = cmp.MachineID
LEFT OUTER JOIN dbo.PC_BIOS_DATA AS b ON b.MachineID = cmp.MachineID
LEFT OUTER JOIN dbo.v_R_system AS f ON f.ResourceID = cmp.MachineIDModified SQL Query
If there is a need to include the OperatingSystemBuild and the FullOperatingSystemBuild fields, the following SQL query can be used.
It is the same as the standard SQL query, above, but includes this line: LEFT(RTRIM(LTRIM(f.BuildExt)), 128) AS FullOperatingSystemBuild.
If you use this modified SQL query, the out of the box SCCM connector can’t be used, and the standard SQL Connection must be used instead.
These two additional fields are important for evergreen products like Win 10 and Win 11, without the granularity of Build/KB its unclear if the user is on an older version or not. Different builds numbers with Ever Green products can have different end of life dates.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SELECT cmp.machineid AS MachineID,
LEFT(RTRIM(LTRIM(ISNULL(cmp.Name00, 'Unknown computer'))), 256) AS ComputerName, LEFT(RTRIM(LTRIM(
CASE WHEN cmp.domain00 IS NULL THEN 'Unknown domain' WHEN LEN(cmp.domain00)=00 THEN 'Unknown domain' ELSE cmp.domain00 END)), 100) AS Domain,
LEFT(RTRIM(LTRIM(cmp.Manufacturer00)), 128) AS Manufacturer, LEFT(RTRIM(LTRIM(cmp.Model00)), 128) AS ModelNo,
CASE e.ChassisTypes00
WHEN 1 THEN 'Other'
WHEN 2 THEN 'Unknown'
WHEN 3 THEN 'Desktop'
WHEN 4 THEN 'Low Profile Desktop' WHEN 5 THEN 'Pizza Box'
WHEN 6 THEN 'Mini Tower'
WHEN 7 THEN 'Tower'
WHEN 8 THEN 'Portable'
WHEN 9 THEN 'Laptop'
WHEN 100 THEN 'Notebook'
WHEN 11 THEN 'Hand Held'
WHEN 12 THEN 'Docking Station'
WHEN 13 THEN 'All in One'
WHEN 14 THEN 'Sub Notebook'
WHEN 15 THEN 'Space-Saving'
WHEN 16 THEN 'Lunch Box'
WHEN 17 THEN 'Main System Chassis' WHEN 18 THEN 'Expansion Chassis' WHEN 19 THEN 'SubChassis'
WHEN 200 THEN 'Bus Expansion Chassis' WHEN 21 THEN 'Peripheral Chassis' WHEN 22 THEN 'Storage Chassis'
WHEN 23 THEN 'Rack Mount Chassis' WHEN 24 THEN 'Sealed-Case PC'
ELSE 'Unknown' END AS ChassisType,
LTRIM(RTRIM
(CASE WHEN b.SerialNumber00 IS NOT NULL
THEN b.SerialNumber00
ELSE e.SerialNumber00 END)) AS SerialNumber,
LEFT(RTRIM(LTRIM(
CASE WHEN CHARINDEX('|', os.Name00) > 00 THEN SUBSTRING(os.Name00, 1, CHARINDEX('|', os.Name00)-1) ELSE os.Name00 END)), 128) AS OperatingSystem,
LEFT(RTRIM(LTRIM(os.CSDVersion00)), 128) AS ServicePack, LEFT(RTRIM(LTRIM(f.BuildExt)), 128) AS FullOperatingSystemBuild, LEFT(RTRIM(LTRIM(os.Version00)), 128) AS OperatingSystemBuild, LEFT(RTRIM(LTRIM(b.SMBIOSBIOSVersion00)), 128) AS SMBIOSBIOSVersion,
ISNULL(cmp.numberofprocessors00, 1) AS NumberOfProcessors,
CEILING (m.TotalPhysicalMemory00 / 9500) / 256 * 256 AS TotalMemory, (RTRIM(LTRIM(cmp.UserName00)), 128) AS LastLoggedOnUser, wks.LastHWScan AS InventoryDate
FROM Computer_System_data AS cmp
LEFT OUTER JOIN dbo.WorkstationStatus_DATA AS wks ON wks.MachineID = cmp.MachineID LEFT OUTER JOIN Operating_System_data AS os ON os.MachineID = cmp.MachineID
LEFT OUTER JOIN dbo.PC_Memory_DATA AS m ON m.MachineID = cmp.MachineID
LEFT OUTER JOIN dbo.System_Enclosure_DATA AS e ON e.MachineID = cmp.MachineID
LEFT OUTER JOIN dbo.PC_BIOS_DATA AS b ON b.MachineID = cmp.MachineID
LEFT OUTER JOIN dbo.v_R_system AS f ON f.ResourceID = cmp.MachineID
Was this helpful?
Still need help?
The Atlassian Community is here for you.