Can I make a join with Insight SCCM Import app?

[Johannes Buverud]

With the database import configuration in Insight, I set the IP address as a string attribute on my computer objects using a sql join.

With the Insight SCCM Import app, the IPs (recource types) are created as standalone objects, and related to the computers.

For several reasons I want to have them as a text attribute, but it does not seem like the "Selector" in the SCCM Import app take joins.

How does the SCCM Import app query the database? What tables are queried? What are my options in the "Selector" field?

[Christian_Solle · Accepted solution]

Hi Johannes,

let me try to answer your questions.

With "join" I think you mean to add values from one ObjectType to another during the import.
In that case the IP from the NetworkInterface to the System.

At first: It is not possible to do that during an import, because the objects are read to the memory stored and then the references where created.
And actually it is not possible to "cross"-access that data.
(A will explain a possible solution for your use case...)

At second: I can't recommend the way to see an "IP" as an static value for a System-Object,
because a system can have more then one NetworkInterface with more then one IP configured.

If you will go on that way you can try the following:
Combine two import configurations.

Which means,
- you setup the regular SCCM import configuration.
- you add an IP Text attribute to the System
- then you need to prepare an SQL-Statement that result the Id, Name, RessourceId and IP Address that you like to add
- create an Database Import that will point to the "SCCM"-System Object
and use the Id and RessourceId as Identifier

the result will be that the "general" System Object will be created from the SCCM-Import
and the Database Import will update the "IP-Adress"

but you need to handle the case of multiple NetworkInterfaces in the SQL Statement.

To complete your question of which "table" we are using:
As MS recommended we are fetching the data from the Database-View (this will not change during minor SCCM-Updates)

For System we are using: [v_R_System]
For NetworkInterface we are using: [v_GS_NETWORK_ADAPTER] and [v_GS_NETWORK_ADAPTER_CONFIGURATION]

Here are the full statements that we are using:
System:

SELECT RS.SMS_Unique_Identifier0,RS.ResourceID,RS.ResourceType,RS.Creation_Date0,
RS.Name0,RS.Netbios_Name0,RS.Resource_Domain_OR_Workgr0,RS.Active0,RS.Client0,RS.Client_Version0,RS.Decommissioned0,
CS.Domain0,CS.DomainRole0,CS.InstallDate0,CS.Manufacturer0,CS.Model0,CS.Roles0,CS.Status0,
PCB.SerialNumber0, OS.LastBootUpTime0, RAM = (SELECT SUM([Capacity0])
FROM [v_GS_PHYSICAL_MEMORY] WHERE ResourceID = RS.ResourceID) FROM
[v_R_System] RS LEFT OUTER JOIN [v_GS_COMPUTER_SYSTEM] CS
ON RS.ResourceID = CS.ResourceID LEFT OUTER JOIN [v_GS_PC_BIOS] PCB
ON RS.ResourceID = PCB.ResourceID LEFT OUTER JOIN [v_GS_OPERATING_SYSTEM] OS
ON RS.ResourceID = OS.ResourceID

NetworkInterface:

SELECT NIC.*, CONF.DefaultIPGateway0,CONF.DHCPEnabled0,
CONF.[DHCPLeaseExpires0],CONF.[DHCPLeaseObtained0],
CONF.[DHCPServer0],CONF.[IPEnabled0],CONF.[IPAddress0],
CONF.[IPFilterSecurityEnabled0],CONF.[IPPortSecurityEnabled0], CONF.[IPSubnet0]
FROM [v_GS_NETWORK_ADAPTER] NIC
LEFT OUTER JOIN [v_GS_NETWORK_ADAPTER_CONFIGURATION] CONF ON NIC.MACAddress0 = CONF.MACAddress0

I hope that will answer your questions and will help you to solve your situation

// Christian

[Johannes Buverud]

Hi Christian and thanks for your excelent answer!

The reason I do not want IP addresses as objects is because we only use the IP to determine the location of the equipment. (Some have more interfaces, but they still belongs to the same location.) We solve this by an automation rule trigging on object create/update, running a groovy script which takes the IP value, matches it with an IP range and then retrieveing the related Site. The IP address itself is not interesting for us. Secondly, less objects and object types in Insight makes everything run smoother/demands less recources. And finally, the Insight SCCM Network Interface import creates an outofmemory on our servers, taking down Jira. (I guess this last issue will probably be solved by you soon:)

I am no SQL guru, but my plan was initially using a regular database import. This query does what I want, and makes me import and map both the equipment and IPs at the same time. (I would of course extend the query to retrieve more attributes etc.):

select CS.Name0 as Name, IP.IP_Addresses0 as IP
from v_GS_COMPUTER_SYSTEM CS, v_RA_System_IPAddresses IP
where IP.ResourceID = CS.ResourceID

I allready do a similar thing with another database import, but since the Insight SCCM import app looks nice, I would love to use it if I can:)

[Christian_Solle]

I recommend to use the mentioned Views as you need the UniqueId of the System that needs to match for the import.

[Johannes Buverud]

Ok

Regarding the problems we have with Jira chrashing/getting outofmemory when using the SCCM import, I see now that the same thing happens when I create a database import with your NetworkInterface statement as Selector and then saves the job (without running it). It does not happen if I use your System statement.

(v_GS_NETWORK_ADAPTER has about 50K rows and our two nodes has 16GB JVM each, so number of objects created should not be an issue.)

[Christian_Solle]

Hi Johannes,

maybe the communication about that will be better at a Support Ticket then in a community question.

Yes, 50k of objects shouldn't be an problem and I know the we have customers that importing a lot more objects than 50k.

As I know that a colleague had picked up that ticket that you have created.

// Christian 

[Johannes Buverud]

Yes, I'll probably get an answer from your support pretty soon. I just posted here what I experienced so that mayby somebody with more sql knowledge than me could see if there is something not quite right with the statement being used.