A new core_company record is created for new software publishers identified during this import. This creates some problems for us and I'm wondering what the best solution is.
The problem is some publisher values are being created with kanji characters (example: Trend 簼䮠言Micro). This happens for trend, intel and vmware products only. Each time this happens (several each day when the SCCM import is run), a new core_company record is created. See attached image
I have updated the SQL query on the import to exclude any products containing Trend, Intel or Vmware. See attached code
SELECT
t.GroupID as GroupID,
t.DisplayName as DisplayName,
t.Version as Version,
t.InstallDate as InstallDate,
t.ProdID as ProdID,
t.Publisher as Publisher,
t.arTimeStamp as arTimeStamp,
t.revisionid as revisionid,
t.resourceID as resourceID,
v_GS_COMPUTER_SYSTEM.name0 as name0 FROM
(SELECT
groupID + 32000000 as GroupID,
displayname0 as DisplayName,
version0 as Version,
installdate0 as InstallDate,
prodID0 as ProdID,
publisher0 as Publisher,
CONVERT(datetime, SWITCHOFFSET(TODATETIMEOFFSET(timestamp,datepart(tz,SYSDATETIMEOFFSET())),0)) as arTimeStamp,
revisionid as revisionid,
resourceID as resourceID
FROM v_GS_Add_Remove_Programs
WHERE displayname0 <> '' AND NOT (UPPER(publisher0) LIKE '%TREND MICRO%' OR UPPER(publisher0) LIKE '%INTEL CORPORATION%' OR UPPER(publisher0) LIKE '%VMWARE, INC%')
UNION
SELECT
groupID + 64000000 as GroupID,
displayname0 as DisplayName,
version0 as Version,
installdate0 as InstallDate,
prodID0 as ProdID,
publisher0 as Publisher,
CONVERT(datetime, SWITCHOFFSET(TODATETIMEOFFSET(timestamp,datepart(tz,SYSDATETIMEOFFSET())),0)) as arTimeStamp,
revisionid as revisionid,
resourceID as resourceID
FROM v_GS_Add_Remove_Programs_64
WHERE displayname0 <> '' AND NOT (UPPER(publisher0) LIKE '%TREND MICRO%' OR UPPER(publisher0) LIKE '%INTEL CORPORATION%' OR UPPER(publisher0) LIKE '%VMWARE, INC.%')
)t
join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.resourceid = t.resourceid
I still get entries in the first image however, so something isn't quite right about the query where Intel values are coming in.
Does anyone have any other suggestions or ideas? Has anyone had the same issue? I'm always keen on best practice so what would that be in this case?
Thank you in advance
