SCCM integration

As far as I know we will be using all 3 because of some features that are specific to each program. We definitely want to avoid duplicate devices so I'm thinking that automatic device creation will be disabled and we will have to map the info from SCCM to our current inventory.

Be advised that the default script for SCCM integration looks at topuser0 to assign primary user within IIQ.  So even if you have an audit policy in place, you may likely have a number of mismatches.

Our User Device Affinity policy within SCCM uses a time criteria to assign primary users.  A user must connect for at least 1440 minutes across 7 days. If the criteria isn’t met, they drop off as primary user within SCCM.  Likewise if they cross that threshold, they become the new primary user in SCCM.

However since topuser0 only looks at who has logged on with the MOST number of hours, we have devices that were exchanged for repair, fixed, and then assigned to a new, different user within IIQ.  SCCM shows that new user as the primary as well.   

However because the IIQ SCCM integration script thinks the user with the most number of logged hours is primary, it overwrites whatever updates are made putting the former user back as the owner. 

We had to disable the “assign user” portion of SCCM because it was overwriting valid owners. The integration is a one way feed only from SCCM to IIQ - there is no way to push back primary user.

To fix this, the sql script within the integration app needs to be modified.  We don’t have an SCCM guru on staff to see which SCCM schema and field the UDA populates for Primary User and so far, IIQ support hasn’t been able to suggest changes.

The issue we are now running into is that we don’t what to open our SQL server running SCCM to the web. We need to come up with a way to pull the SQL data to our SFTP server and then send it to IIQ if we have any hope of making this work. What kills me is that the data we are chasing after is already available in Intune, but we are not able to use it yet in Auditing on IIQ. I’ve submitted an idea post asking for this, my only hope is that it gains some traction because SCCM is being phased out and we need to be using the data we are already pulling from Intune more effectively. 

@Zane Groven The SQL server running SCCM should not need to be open to the web and the query is adjustable within the manage app settings.  It is difficult for iiQ to provide a query template that works for all districts due to the SCCM schema differences and other customizations between district use cases, but you can certainly reach out to support for some guidance. 

I’ve pasted a couple of custom queries below that may help.  

WITH DEVICES AS(
SELECT 
SYS.ResourceID AS ExternalId,
SYS.Name0 AS AssetTag,
CS.Manufacturer0 AS Manufacturer,
CS.Model0 AS Model,
BIOS.SerialNumber0 AS SerialNumber,
SAS.SMS_Assigned_Sites0 AS LocationName,
ISNULL((SELECT TOP 1 Mail0 FROM v_R_User WHERE UM.TopConsoleUser0 = v_R_User.Unique_User_Name0), UM.TopConsoleUser0) AS OwnerEmail,  
ISNULL((SELECT TOP 1 Mail0 FROM v_R_User WHERE UM.TopConsoleUser0 = v_R_User.Unique_User_Name0), UM.TopConsoleUser0) AS PrimaryUser,    
OS.InstallDate0 AS CreatedDate,
ISNULL(OS.Timestamp, GETUTCDATE()) AS UpdatedDate,
SYS.Distinguished_Name0 AS OU,

[dbo].ToJSON((SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Idx, dbo.RemoveInvalidXMLCharacters(IP.IP_Addresses0) AS IP_Addresses0, dbo.RemoveInvalidXMLCharacters(NET.MACAddress0) AS MACAddress0, dbo.RemoveInvalidXMLCharacters(NET.Description0) AS Description0 FROM dbo.v_RA_System_IPAddresses IP LEFT JOIN dbo.v_RA_System_IPSubnets IPSUB ON IPSUB.ResourceID = IP.ResourceID LEFT JOIN dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION NETW ON IP.ResourceID = NETW.ResourceID LEFT JOIN dbo.v_GS_NETWORK_ADAPTER NET ON IP.ResourceID = NET.ResourceID WHERE IP.ResourceID=sys.ResourceID AND (NET.ResourceID IS NULL OR ((NOT (NETW.DefaultIPGateway0 = 'NULL')) AND (NOT (IP.IP_Addresses0 = 'NULL')) AND (NOT (IP.IP_Addresses0 = '0.0.0.0')) and (NET.AdapterType0 = 'Ethernet 802.3') AND (Not (NET.Description0 LIKE '%MiniPort%')) and (not (NET.Description0 LIKE '%1394%')))) ORDER BY NET.MACAddress0, IP.IP_Addresses0 FOR XML RAW)) AS IPAddresses,
[dbo].ToJSON((SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Idx, dbo.RemoveInvalidXMLCharacters(Description0) AS Description0, dbo.RemoveInvalidXMLCharacters(DeviceID0) AS DeviceID0, DriveType0, dbo.RemoveInvalidXMLCharacters(FileSystem0) AS FileSystem0, FreeSpace0, Size0, dbo.RemoveInvalidXMLCharacters(VolumeName0) AS VolumeName0, dbo.RemoveInvalidXMLCharacters(VolumeSerialNumber0) AS VolumeSerialNumber0 FROM v_GS_LOGICAL_DISK AS [link] WITH(NOLOCK) WHERE [link].ResourceID=sys.ResourceID FOR XML RAW)) AS Disks,
[dbo].ToJSON((SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Idx, dbo.RemoveInvalidXMLCharacters(DisplayName0) AS DisplayName0, InstallDate0, dbo.RemoveInvalidXMLCharacters(Publisher0) AS Publisher0, dbo.RemoveInvalidXMLCharacters(Version0) AS Version0 FROM v_Add_Remove_Programs AS [link] WITH(NOLOCK) WHERE [link].ResourceID=sys.ResourceID FOR XML RAW)) AS InstalledSoftware,
[dbo].ToJSON((SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Idx, TimeStamp, LastConsoleUse0, NumberOfConsoleLogons0, dbo.RemoveInvalidXMLCharacters(SystemConsoleUser0) AS SystemConsoleUser0, TotalUserConsoleMinutes0 FROM v_GS_SYSTEM_CONSOLE_USER AS [link] WITH(NOLOCK) WHERE [link].ResourceID=sys.ResourceID ORDER BY LastConsoleUse0 FOR XML RAW)) AS DeviceUsers,
[dbo].ToJSON((SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Idx, dbo.RemoveInvalidXMLCharacters(Name0) AS Name0, NormSpeed0 FROM v_GS_PROCESSOR AS [link] WITH(NOLOCK) WHERE [link].ResourceID=sys.ResourceID FOR XML RAW)) AS CPUs,

--v_R_System
SYS.AD_Site_Name0 AS ADSiteName, SYS.Client_Version0 AS ClientVersion, SYS.CPUType0 AS CPUType, SYS.Creation_Date0 AS CreatedDate1, SYS.Distinguished_Name0 AS DistinguishedName, SYS.Full_Domain_Name0 AS FullDomainName, SYS.Is_Assigned_To_User0 AS IsAssignedToUser, SYS.Is_Virtual_Machine0 AS IsVirtualMachine, SYS.Last_Logon_Timestamp0 AS LastLoginDate, SYS.Netbios_Name0 AS NetbiosName, SYS.Hardware_ID0 AS HardwareId, SYS.Operating_System_Name_and0 AS OSAndVersion, SYS.OSBranch01 AS OSBranch, SYS.SID0 AS SID, 

--v_GS_OPERATING_SYSTEM
OS.BootDevice0 AS BootDevice, OS.Caption0 AS OperatingSystem, LastBootUpTime0 AS LastBootUpTime, OS.OSLanguage0 AS OSLanguage, OS.TotalVirtualMemorySize0 AS TotalVirtualMemorySize, OS.TotalVisibleMemorySize0 AS TotalVisibleMemorySize, OS.Version0 AS OSVersioNumber, OS.WindowsDirectory0 AS WindowsDirectory, OS.CSDVersion0 AS ServicePackLevel,

--v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP
UM.SecurityLogStartDate0, UM.TotalConsoleTime0, UM.TotalConsoleUsers0, UM.TotalSecurityLogTime0,

--v_GS_COMPUTER_SYSTEM
CS.NumberOfProcessors0 AS NumberOfProcessors,

--v_GS_PC_BIOS
BIOS.BIOSVersion0 AS BIOSVersion, BIOS.Description0 AS BIOSDescription,

--v_GS_SYSTEM_ENCLOSURE
SE.SMBIOSAssetTag0 AS SMBIOSAssetTag, SE.SerialNumber0 AS EnclosureSerialNumber, 

(SELECT SUM(Capacity0) from v_GS_PHYSICAL_MEMORY PM WHERE PM.ResourceID = SYS.ResourceID) AS PhysicalMemory

FROM v_R_System SYS 
LEFT OUTER JOIN v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = SYS.ResourceID 
LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP UM ON UM.ResourceID = SYS.ResourceID
LEFT OUTER JOIN v_GS_COMPUTER_SYSTEM CS ON CS.ResourceID = SYS.ResourceID
LEFT OUTER JOIN v_GS_PC_BIOS BIOS ON BIOS.ResourceID = SYS.ResourceID
LEFT OUTER JOIN v_GS_SYSTEM_ENCLOSURE_UNIQUE SE ON SE.ResourceID = SYS.ResourceID
LEFT OUTER JOIN v_RA_System_SmsAssignedSites SAS ON SAS.ResourceID = SYS.ResourceID

  WHERE ResourceType=5 --System (select * from v_ResourceMap)
)

SELECT [ExternalId], [AssetTag], [Manufacturer], [Model], [SerialNumber], [LocationName], CASE WHEN [OwnerEmail] LIKE '%.%@%' OR [OwnerEmail] LIKE '%\%.%' THEN [OwnerEmail] ELSE NULL END AS [OwnerEmail], CASE WHEN [PrimaryUser] LIKE '%.%@%' OR [PrimaryUser] LIKE '%\%.%' THEN [PrimaryUser] ELSE NULL END AS [PrimaryUser], [CreatedDate], [UpdatedDate], [OU], [IPAddresses], [Disks], [InstalledSoftware], [DeviceUsers], [CPUs], [ADSiteName], [ClientVersion], [CPUType], [CreatedDate1], [DistinguishedName], [FullDomainName], [IsAssignedToUser], [IsVirtualMachine], [LastLoginDate], [NetbiosName], [HardwareId], [OSAndVersion], [OSBranch], [SID], [BootDevice], [OperatingSystem], [LastBootUpTime], [OSLanguage], [TotalVirtualMemorySize], [TotalVisibleMemorySize], [OSVersioNumber], [WindowsDirectory], [ServicePackLevel], [SecurityLogStartDate0], [TotalConsoleTime0], [TotalConsoleUsers0], [TotalSecurityLogTime0], [NumberOfProcessors], [BIOSVersion], [BIOSDescription], [SMBIOSAssetTag], [EnclosureSerialNumber], [PhysicalMemory]
 FROM DEVICES

WITH DEVICES AS(

SELECT

SYS.ResourceID AS ExternalId,

SYS.Name0 AS AssetTag,

CS.Manufacturer0 AS Manufacturer,

CS.Model0 AS Model,

BIOS.SerialNumber0 AS SerialNumber,

SAS.SMS_Assigned_Sites0 AS LocationName,

/*UM.TopConsoleUser0 AS OwnerEmail,*/

ISNULL((SELECT TOP 1 User_Principal_Name0 FROM v_R_User WHERE UM.TopConsoleUser0 = v_R_User.Unique_User_Name0), UM.TopConsoleUser0) AS OwnerEmail,

UM.TopConsoleUser0 AS PrimaryUser,

OS.InstallDate0 AS CreatedDate,

ISNULL(OS.Timestamp, GETUTCDATE()) AS UpdatedDate,

SYS.Distinguished_Name0 AS OU,



[dbo].ToJSON((SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Idx, dbo.RemoveInvalidXMLCharacters(IP.IP_Addresses0) AS IP_Addresses0, dbo.RemoveInvalidXMLCharacters(NET.MACAddress0) AS MACAddress0, dbo.RemoveInvalidXMLCharacters(NET.Description0) AS Description0 FROM dbo.v_RA_System_IPAddresses IP LEFT JOIN dbo.v_RA_System_IPSubnets IPSUB ON IPSUB.ResourceID = IP.ResourceID LEFT JOIN dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION NETW ON IP.ResourceID = NETW.ResourceID LEFT JOIN dbo.v_GS_NETWORK_ADAPTER NET ON IP.ResourceID = NET.ResourceID WHERE IP.ResourceID=sys.ResourceID AND (NET.ResourceID IS NULL OR ((NOT (NETW.DefaultIPGateway0 = 'NULL')) AND (NOT (IP.IP_Addresses0 = 'NULL')) AND (NOT (IP.IP_Addresses0 = '0.0.0.0')) and (NET.AdapterType0 = 'Ethernet 802.3') AND (Not (NET.Description0 LIKE '%MiniPort%')) and (not (NET.Description0 LIKE '%1394%')))) ORDER BY NET.MACAddress0, IP.IP_Addresses0 FOR XML RAW)) AS IPAddresses,

[dbo].ToJSON((SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Idx, dbo.RemoveInvalidXMLCharacters(Description0) AS Description0, dbo.RemoveInvalidXMLCharacters(DeviceID0) AS DeviceID0, DriveType0, dbo.RemoveInvalidXMLCharacters(FileSystem0) AS FileSystem0, FreeSpace0, Size0, dbo.RemoveInvalidXMLCharacters(VolumeName0) AS VolumeName0, dbo.RemoveInvalidXMLCharacters(VolumeSerialNumber0) AS VolumeSerialNumber0 FROM v_GS_LOGICAL_DISK AS [link] WITH(NOLOCK) WHERE [link].ResourceID=sys.ResourceID FOR XML RAW)) AS Disks,

[dbo].ToJSON((SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Idx, dbo.RemoveInvalidXMLCharacters(DisplayName0) AS DisplayName0, InstallDate0, dbo.RemoveInvalidXMLCharacters(Publisher0) AS Publisher0, dbo.RemoveInvalidXMLCharacters(Version0) AS Version0 FROM v_Add_Remove_Programs AS [link] WITH(NOLOCK) WHERE [link].ResourceID=sys.ResourceID FOR XML RAW)) AS InstalledSoftware,

[dbo].ToJSON((SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Idx, TimeStamp, LastConsoleUse0, NumberOfConsoleLogons0, dbo.RemoveInvalidXMLCharacters(SystemConsoleUser0) AS SystemConsoleUser0, TotalUserConsoleMinutes0 FROM v_GS_SYSTEM_CONSOLE_USER AS [link] WITH(NOLOCK) WHERE [link].ResourceID=sys.ResourceID ORDER BY LastConsoleUse0 FOR XML RAW)) AS DeviceUsers,

[dbo].ToJSON((SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Idx, dbo.RemoveInvalidXMLCharacters(Name0) AS Name0, NormSpeed0 FROM v_GS_PROCESSOR AS [link] WITH(NOLOCK) WHERE [link].ResourceID=sys.ResourceID FOR XML RAW)) AS CPUs,



--v_R_System

SYS.AD_Site_Name0 AS ADSiteName, SYS.Client_Version0 AS ClientVersion, SYS.CPUType0 AS CPUType, SYS.Creation_Date0 AS CreatedDate1, SYS.Distinguished_Name0 AS DistinguishedName, SYS.Full_Domain_Name0 AS FullDomainName, SYS.Is_Assigned_To_User0 AS IsAssignedToUser, SYS.Is_Virtual_Machine0 AS IsVirtualMachine, SYS.Last_Logon_Timestamp0 AS LastLoginDate, SYS.Netbios_Name0 AS NetbiosName, SYS.Hardware_ID0 AS HardwareId, SYS.Operating_System_Name_and0 AS OSAndVersion, SYS.OSBranch01 AS OSBranch, SYS.SID0 AS SID,



--v_GS_OPERATING_SYSTEM

OS.BootDevice0 AS BootDevice, OS.Caption0 AS OperatingSystem, LastBootUpTime0 AS LastBootUpTime, OS.OSLanguage0 AS OSLanguage, OS.TotalVirtualMemorySize0 AS TotalVirtualMemorySize, OS.TotalVisibleMemorySize0 AS TotalVisibleMemorySize, OS.Version0 AS OSVersioNumber, OS.WindowsDirectory0 AS WindowsDirectory, OS.CSDVersion0 AS ServicePackLevel,



--v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP

UM.SecurityLogStartDate0, UM.TotalConsoleTime0, UM.TotalConsoleUsers0, UM.TotalSecurityLogTime0,



--v_GS_COMPUTER_SYSTEM

CS.NumberOfProcessors0 AS NumberOfProcessors,



--v_GS_PC_BIOS

BIOS.BIOSVersion0 AS BIOSVersion, BIOS.Description0 AS BIOSDescription,



--v_GS_SYSTEM_ENCLOSURE

SE.SMBIOSAssetTag0 AS SMBIOSAssetTag, SE.SerialNumber0 AS EnclosureSerialNumber,



(SELECT SUM(Capacity0) from v_GS_PHYSICAL_MEMORY PM WHERE PM.ResourceID = SYS.ResourceID) AS PhysicalMemory



FROM v_R_System SYS

LEFT OUTER JOIN v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = SYS.ResourceID

LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP UM ON UM.ResourceID = SYS.ResourceID

LEFT OUTER JOIN v_GS_COMPUTER_SYSTEM CS ON CS.ResourceID = SYS.ResourceID

LEFT OUTER JOIN v_GS_PC_BIOS BIOS ON BIOS.ResourceID = SYS.ResourceID

LEFT OUTER JOIN v_GS_SYSTEM_ENCLOSURE_UNIQUE SE ON SE.ResourceID = SYS.ResourceID

LEFT OUTER JOIN v_RA_System_SmsAssignedSites SAS ON SAS.ResourceID = SYS.ResourceID



WHERE ResourceType = 5 --System(select * from v_ResourceMap)

)



SELECT * FROM DEVICES