Displaying the Full Version and Build for Windows 10 Workstations

NEW EDIT – THIS IS NO LONGER RELEVANT. Please see https://sccmnotes.wordpress.com/2019/07/18/version-and-build-for-windows-10-in-the-sccm-database/

EDIT – The KB match to find the full build is no longer necessary, since the BuildExt field is in v_r_system. The only thing now I have is a OSBuild Version table to match the five digit build to the four digit version:

OSBuild Version
18362 1903
17763 1809
17134 1803
16299 1709
15063 1703
14393 1607
10586 1511
10240 1507

Then I just query the version with the following:

select distinct v.Version + ' - ' + s.BuildExt VersionBuild from v_R_System s
 inner join CM_Supplemental.dbo.Windows10BuildVersion v on s.Build01 like '%' + v.OSBuild
 where s.Name0 = @Name0

For reference: https://technet.microsoft.com/en-gb/windows/mt679505.aspx

THE FOLLWING IS OLD INFORMATION, NO LONGER RELEVANT.

I was asked to add the build version to my simplified deployment report.  Easy enough, I could use the Version0 column in v_gs_operating_system.  I added it to the report and sent the link to the requestor:

This wasn’t satisfactory, and they were actually looking for the complete version/build, like shown in the ver command:

They wanted something like this:

It’s not readily available in SCCM, so I created a table in a secondary database:

CREATE TABLE [dbo].[Windows10Versions] (
 [HotfixID0] [varchar](50) NOT NULL,
 [VersionBuild] [varchar](50) NOT NULL
)

and using the data from https://technet.microsoft.com/en-gb/windows/mt679505.aspx (Windows 10 release information), I added the data thusly:

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4073291', '1709 - 10.0.16299.201')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4073290', '1709 - 10.0.16299.194')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4056892', '1709 - 10.0.16299.192')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4054517', '1709 - 10.0.16299.125')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4051963', '1709 - 10.0.16299.98')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4048955', '1709 - 10.0.16299.64')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4043961', '1709 - 10.0.16299.19')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4057144', '1703 - 10.0.15063.877')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4056891', '1703 - 10.0.15063.850')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4053580', '1703 - 10.0.15063.786')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4055254', '1703 - 10.0.15063.729')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4048954', '1703 - 10.0.15063.726')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4049370', '1703 - 10.0.15063.675')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4041676', '1703 - 10.0.15063.674')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4040724', '1703 - 10.0.15063.632')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4038788', '1703 - 10.0.15063.608')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4034674', '1703 - 10.0.15063.540')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4032188', '1703 - 10.0.15063.502')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4025342', '1703 - 10.0.15063.483')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4022716', '1703 - 10.0.15063.447')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4022725', '1703 - 10.0.15063.413')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4020102', '1703 - 10.0.15063.332')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4016871', '1703 - 10.0.15063.296')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4016240', '1703 - 10.0.15063.250')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4015583', '1703 - 10.0.15063.138')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4057142', '1607 - 10.0.14393.2035')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4056890', '1607 - 10.0.14393.2007')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4053579', '1607 - 10.0.14393.1944')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4051033', '1607 - 10.0.14393.1914')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4048953', '1607 - 10.0.14393.1884')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4052231', '1607 - 10.0.14393.1797')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4041688', '1607 - 10.0.14393.1794')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4041691', '1607 - 10.0.14393.1770')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4038801', '1607 - 10.0.14393.1737')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4038782', '1607 - 10.0.14393.1715')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4039396', '1607 - 10.0.14393.1670')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4034661', '1607 - 10.0.14393.1613')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4034658', '1607 - 10.0.14393.1593')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4038220', '1607 - 10.0.14393.1537')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4025334', '1607 - 10.0.14393.1532')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4025339', '1607 - 10.0.14393.1480')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4022723', '1607 - 10.0.14393.1378')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4022715', '1607 - 10.0.14393.1358')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4019472', '1607 - 10.0.14393.1198')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4015217', '1607 - 10.0.14393.1066')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4015438', '1607 - 10.0.14393.969')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4013429', '1607 - 10.0.14393.953')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3213986', '1607 - 10.0.14393.693')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3206632', '1607 - 10.0.14393.576')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3201845', '1607 - 10.0.14393.479')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3200970', '1607 - 10.0.14393.447')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3197954', '1607 - 10.0.14393.351')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3194798', '1607 - 10.0.14393.321')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3194496', '1607 - 10.0.14393.222')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3189866', '1607 - 10.0.14393.187')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3193494', '1607 - 10.0.14393.187')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3176938', '1607 - 10.0.14393.105')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3176934', '1607 - 10.0.14393.82')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3176495', '1607 - 10.0.14393.51')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3176929', '1607 - 10.0.14393.10')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4075200', '1511 - 10.0.10586.1358')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4056888', '1511 - 10.0.10586.1356')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4053578', '1511 - 10.0.10586.1295')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4048952', '1511 - 10.0.10586.1232')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4052232', '1511 - 10.0.10586.1177')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4041689', '1511 - 10.0.10586.1176')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4038783', '1511 - 10.0.10586.1106')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4034660', '1511 - 10.0.10586.1045')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4025344', '1511 - 10.0.10586.1007')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4032693', '1511 - 10.0.10586.965')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4022714', '1511 - 10.0.10586.962')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4019473', '1511 - 10.0.10586.916')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4015219', '1511 - 10.0.10586.873')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4013198', '1511 - 10.0.10586.839')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3210721', '1511 - 10.0.10586.753')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3205386', '1511 - 10.0.10586.713')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3198586', '1511 - 10.0.10586.679')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3192441', '1511 - 10.0.10586.633')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3185614', '1511 - 10.0.10586.589')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3176493', '1511 - 10.0.10586.545')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3172985', '1511 - 10.0.10586.494')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3163018', '1511 - 10.0.10586.420')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3156421', '1511 - 10.0.10586.318')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3147458', '1511 - 10.0.10586.218')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3140768', '1511 - 10.0.10586.164')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3140743', '1511 - 10.0.10586.122')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3135173', '1511 - 10.0.10586.104')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3124262', '1511 - 10.0.10586.71')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3124263', '1511 - 10.0.10586.63')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3124200', '1511 - 10.0.10586.36')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3116900', '1511 - 10.0.10586.29')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3116908', '1511 - 10.0.10586.17')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3120677', '1511 - 10.0.10586.14')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3118754', '1511 - 10.0.10586.11')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3105211', '1511 - 10.0.10586.3')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4075199', '1507 - 10.0.10240.17741')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4056893', '1507 - 10.0.10240.17738')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4053581', '1507 - 10.0.10240.17709')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4048956', '1507 - 10.0.10240.17673')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4042895', '1507 - 10.0.10240.17643')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4038781', '1507 - 10.0.10240.17609')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4034668', '1507 - 10.0.10240.17533')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4025338', '1507 - 10.0.10240.17488')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4032695', '1507 - 10.0.10240.17446')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4022727', '1507 - 10.0.10240.17443')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4019474', '1507 - 10.0.10240.17394')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4015221', '1507 - 10.0.10240.17354')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB4012606', '1507 - 10.0.10240.17319')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3210720', '1507 - 10.0.10240.17236')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3205383', '1507 - 10.0.10240.17202')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3198585', '1507 - 10.0.10240.17190')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3192440', '1507 - 10.0.10240.17146')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3185611', '1507 - 10.0.10240.17113')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3193821', '1507 - 10.0.10240.17113')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3176492', '1507 - 10.0.10240.17071')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3163912', '1507 - 10.0.10240.17024')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3163017', '1507 - 10.0.10240.16942')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3156387', '1507 - 10.0.10240.16854')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3147461', '1507 - 10.0.10240.16771')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3140745', '1507 - 10.0.10240.16725')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3135174', '1507 - 10.0.10240.16683')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3124266', '1507 - 10.0.10240.16644')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3116869', '1507 - 10.0.10240.16601')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3105213', '1507 - 10.0.10240.16590')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3105210', '1507 - 10.0.10240.16566')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3097617', '1507 - 10.0.10240.16549')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3093266', '1507 - 10.0.10240.16520')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3081455', '1507 - 10.0.10240.16487')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3081448', '1507 - 10.0.10240.16463')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3081444', '1507 - 10.0.10240.16445')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3081438', '1507 - 10.0.10240.16433')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3081436', '1507 - 10.0.10240.16430')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3081424', '1507 - 10.0.10240.16413')

insert into Windows10Versions (HotfixID0, VersionBuild) values ('KB3074683', '1507 - 10.0.10240.16405')

Then, I created a new dataset in the report:

select v.VersionBuild from v_R_System s
inner join v_GS_QUICK_FIX_ENGINEERING qfe on qfe.ResourceID = s.ResourceID
inner join CM_Supplemental.dbo.Windows10Versions v on v.HotfixID0 = qfe.HotFixID0
where s.Name0 = @Name0

Now I need to periodically check the TechNet page to add additional rows…

This will show the latest hotfix (if Microsoft proceeds by incrementing the KB numbers)…

select * from CM_Supplemental.dbo.Windows10Versions where HotfixID0 in
(
select max (HotfixID0) from CM_Supplemental.dbo.Windows10Versions
group by left (versionbuild, 4)
)
order by 2

EDIT: The KB numbers are not increasing all the time, so I’ve got a query to look for the latest build:

select * from CM_Supplemental.dbo.Windows10Versions b
 inner join
 (
     select
     left (versionbuild, 4) [Version]
     , max (CAST (REVERSE (SUBSTRING (REVERSE (VersionBuild), 1, CHARINDEX ('.', REVERSE (VersionBuild)) - 1)) as int)) [Build]
     from CM_Supplemental.dbo.Windows10Versions
     group by left (versionbuild, 4)
 ) m on b.VersionBuild like m.[Version] + '%' + CAST (m.Build as varchar)
 order by VersionBuild desc

Match them against the Microsoft website to find what’s missing.

Today (2/6/2018) I will add:

insert into CM_Supplemental.dbo.Windows10Versions (HotfixID0, VersionBuild) values ('KB4077735', '1507 - 10.0.10240.17741') insert into CM_Supplemental.dbo.Windows10Versions (HotfixID0, VersionBuild) values ('KB4058258', '1709 - 10.0.16299.214')

Convert ipv4 data in v_RA_System_IPAddresses.IP_Addresses0 from rows to columns

SELECT *
 FROM
 (
 SELECT
 'IP_Addresses0' + CONVERT(VARCHAR, ROW_NUMBER() OVER (PARTITION BY [ResourceID] ORDER BY [ResourceID])) [IP_Addresses]
 , ResourceID
 , IP_Addresses0
 FROM v_RA_System_IPAddresses where IP_Addresses0 not like '%::%'
 ) IPData
 PIVOT
 (
 MAX(IP_Addresses0) FOR [IP_Addresses] IN ([IP_Addresses01], [IP_Addresses02], [IP_Addresses03], [IP_Addresses04], [IP_Addresses05])
 ) Pvt

This will give 5 columns, more can be added…

Second SMS Provider causing driver package updates to fail with Code 0xffffffff (“HashFile failed as … is a Reparse Point that SMS does not support via downloads DriverCatalog”) in the DriverCatalog.log file

We decided to add a second SMS Provider for the large number of console users we have.  It seemed to go pretty well, but various admins were finding driver packages failing when trying to update them.  The DriverCatalog.log file on the new provider showed:

================== START CreateDriverFromINFs  =================================     DriverCatalog                11/24/2017 9:46:11 AM 6776 (0x1A78)
INF PATH ='\\fs1-95x.isilon1-95x.svc.ny.gov\its_shared\SCCMPkgSource\TEST\Durning\TestDrivers\NDIS65'                 DriverCatalog     11/24/2017 9:46:11 AM 6776 (0x1A78)
INF file 1 is 'i40ea65.inf'                 DriverCatalog     11/24/2017 9:46:11 AM 6776 (0x1A78)
INF file 2 is 'i40eb65.inf'                DriverCatalog     11/24/2017 9:46:11 AM 6776 (0x1A78)
INF file 3 is 'v40e65.inf' DriverCatalog     11/24/2017 9:46:11 AM 6776 (0x1A78)
HashFile failed as \\server.domain\its_shared\SCCMPkgSource\TEST\TestDrivers\NDIS65 is a Reparse Point that SMS does not support via downloads DriverCatalog     11/24/2017 9:46:11 AM 6776 (0x1A78)
Failed to build driver content information for '\\server.domain\its_shared\SCCMPkgSource\TEST\TestDrivers\NDIS65'.  Code 0xffffffff   DriverCatalog                11/24/2017 9:46:11 AM 6776 (0x1A78)
================== END CreateDriverFromINFs  =================================         DriverCatalog                11/24/2017 9:46:11 AM 6776 (0x1A78)

Simple solution, the new provider did not have permissions to the file share.

File ‘C:\WINDOWS\ccmsetup\NDP452-KB2901907-x86-x64-AllOS-ENU.exe’ returned failure exit code 9.

EDIT – also check the C:\Windows\ccmsetup\Logs\dotNetFx452_Setup.log.html file.  Checking this today I found:

Not enough disk space available to download files and/or install – unable to complete install

[1/12/2018, 12:22:17] Action complete

[1/12/2018, 12:22:17]Sending Manifest Report
[1/12/2018, 12:22:17]Final Result: Installation failed with error code: (0x000013EC), “A StopBlock was hit or a System Requirement was not met.” (Elapsed time: 0 00:00:01).

END EDIT

I’m doing this one from memory, I think in ccmsetup.log there was:

File 'C:\WINDOWS\ccmsetup\NDP452-KB2901907-x86-x64-AllOS-ENU.exe' returned failure exit code 9.  Fail the installation
InstallFromManifest failed 0x80070009

And in the application event log there was:

The description for Event ID 10997 from source MsiInstaller cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.
If the event originated on another computer, the display information had to be saved with the event.
The following information was included with the event:
Product: Microsoft .NET Framework 4.5.2 — Error 997. Error 997. Overlapped I/O operation is in progress.

I found a fix that suggested renaming C:\ProgramData\Microsoft\Crypto\RSA\S-1-5-18 to something like S-1-5-18.backup and then retrying the install.  This worked.

It doesn’t appear from the command line, but it does with Explorer:

Taken from Gerd Hübner’s comment on:

https://blogs.msdn.microsoft.com/vsnetsetup/2014/10/27/error-997-overlapped-io-operation-is-in-progress-kb2918614-breaks-windows-installer-service/

SQL for an SCCM report of SQL Standard or Enterprise editions

with IPData (ResourceID, IPAddresses) as
(
select
 n1.ResourceID
, stuff (
(
select ',' + IP_Addresses0 from v_RA_System_IPAddresses n2
where n2.ResourceID = n1.ResourceID and IP_Addresses0 not like '%:%'
order by IP_Addresses0
for xml path('')
),1,1,'') AS IPAddresses
from v_RA_System_IPAddresses n1
group by ResourceID
)
select s.Domain0, s.Name0, c.NormalizedName, ip.IPAddresses
from v_GS_SYSTEM s
inner join v_GS_INSTALLED_SOFTWARE_CATEGORIZED c on c.ResourceID = s.ResourceID
inner join IPData ip on ip.ResourceID = s.ResourceID
where
FamilyName = 'Information Management and Access'
and CategoryName = 'Information and Data Management'
and (c.NormalizedName like '[M,S]%QL Server % Database Engine Services%Enterprise%' or c.NormalizedName like '[M,S]%QL Server % Database Engine Services%Standard%')

SQL for an SCCM report of Windows Server Standard or Datacenter editions

with IPData (ResourceID, IPAddresses) as
(
select
 n1.ResourceID
, stuff (
(
select ',' + IP_Addresses0 from v_RA_System_IPAddresses n2
where n2.ResourceID = n1.ResourceID and IP_Addresses0 not like '%:%'
order by IP_Addresses0
for xml path('')
),1,1,'') AS IPAddresses
from v_RA_System_IPAddresses n1
group by ResourceID
)
select s.Domain0, s.Name0, c.NormalizedName, ip.IPAddresses
from v_GS_SYSTEM s
inner join v_GS_INSTALLED_SOFTWARE_CATEGORIZED c on c.ResourceID = s.ResourceID
inner join IPData ip on ip.ResourceID = s.ResourceID
where
FamilyName = 'Operating Environment'
and CategoryName = 'Operating System and Components'
and (c.NormalizedName like '[M,W]%indows Server%Standard%' or c.NormalizedName like '[M,W]%indows Server%Datacenter%')

“MSI: Could not access network location %APPDATA%\.” in ccmsetup.log

Thanks to http://www.haroldbato.com/2014/03/sccm-msi-could-not-access-network.html

The registry value “AppData” in the HKEY_USERS\S-1-5-18\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders key is hosed.  For some reason, it’s value is %APPDATA%:

C:\>reg query "\\WORKSTATION.DOMAIN.NAME\hku\S-1-5-18\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders" /v AppData

HKEY_USERS\S-1-5-18\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders

    AppData    REG_EXPAND_SZ    %APPDATA%

Change the value to “%USERPROFILE%\AppData\Roaming”.

“Setup was unable to upgrade Sql CE database file” in ccmsetup.log

MSI: Action 8:59:26: CcmUpgradeSqlCEDatabases. Upgrade Sql CE databases that were queued by CcmUpgradeSqlCEDatabasesInit. ccmsetup 1/12/2018 8:59:26 AM 1344 (0x0540)
MSI: Setup was unable to upgrade Sql CE database file UserAffinityStore.sdf.
The error code is 8007007E. ccmsetup 1/12/2018 8:59:26 AM 1344 (0x0540)

Similar fix as “unable to compile”… this time rename C:\Windows\ccm\*.sdf files.

C:\Windows\CCM>dir *.sdf
 Volume in drive C is System Reserved
 Volume Serial Number is A24B-DD40

 Directory of C:\Windows\CCM

01/12/2018  09:39 AM            65,536 CcmStore.sdf
04/11/2016  06:50 AM            32,768 CertEnrollmentStore.sdf
01/12/2018  09:39 AM            65,536 ClientEvents.sdf
05/30/2017  08:53 AM            65,536 ComplRelayStore.sdf
01/08/2018  01:13 PM        49,303,552 InventoryStore.sdf
01/10/2018  01:16 PM           229,376 StateMessageStore.sdf
01/10/2018  12:18 AM           106,496 UserAffinityStore.sdf
               7 File(s)     49,868,800 bytes
               0 Dir(s)  421,985,853,440 bytes free

C:\Windows\CCM>ren *.sdf *.backup

C:\Windows\CCM>dir *.backup
 Volume in drive C is System Reserved
 Volume Serial Number is A24B-DD40

 Directory of C:\Windows\CCM

01/12/2018  09:39 AM            65,536 CcmStore.backup
04/11/2016  06:50 AM            32,768 CertEnrollmentStore.backup
01/12/2018  09:39 AM            65,536 ClientEvents.backup
05/30/2017  08:53 AM            65,536 ComplRelayStore.backup
01/08/2018  01:13 PM        49,303,552 InventoryStore.backup
01/10/2018  01:16 PM           229,376 StateMessageStore.backup
01/10/2018  12:18 AM           106,496 UserAffinityStore.backup
               7 File(s)     49,868,800 bytes
               0 Dir(s)  421,985,853,440 bytes free

 

“Setup was unable to compile Sql CE script file” in ccmsetup.log during upgrade.

File C:\Windows\ccmsetup\{EF4FF44A-5960-4DD5-AC5A-4E10625AEA63}\client.msi installation failed. Error text: ExitCode: 1603
Action: CcmCompileSqlCEScripts.
ErrorMessages:
Setup was unable to compile Sql CE script file C:\Windows\CCM\StateMessageStore.sqlce.
The error code is 80004005.

in C:\Windows\ccm, rename the *.sqlce files.
Thanks to https://windowsbag.wordpress.com/2017/07/25/fix-error-unable-to-compile-sql-ce-script-file-during-sccm-client-install-or-upgrade-80004005/

“File ‘C:\Windows\ccmsetup\Silverlight.exe’ returned failure exit code 1612. Fail the installation” in ccmsetup.log

Seems to be a corrupt install of Silverlight.  Using “How to clean a corrupted Silverlight installation and then reinstall Silverlight” (https://support.microsoft.com/en-us/help/2608523/how-to-clean-a-corrupted-silverlight-installation-and-then-reinstall-s), create a batch file CleanSilverlight.bat with the following and run it locally on the affected client:

reg delete HKLM\Software\Microsoft\Silverlight /f
reg delete HKEY_CLASSES_ROOT\Installer\Products\D7314F9862C648A4DB8BE2A5B47BE100 /f
reg delete HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Installer\Products\D7314F9862C648A4DB8BE2A5B47BE100 /f
reg delete HKEY_CLASSES_ROOT\TypeLib\{283C8576-0726-4DBC-9609-3F855162009A} /f
reg delete HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\install.exe /f
reg delete HKEY_CLASSES_ROOT\AgControl.AgControl /f
reg delete HKEY_CLASSES_ROOT\AgControl.AgControl.5.1 /f
reg delete HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\{89F4137D-6C26-4A84-BDB8-2E5A4BB71E00} /f
rmdir /s /q "%ProgramFiles%\Microsoft Silverlight"
rmdir /s /q "%ProgramFiles(x86)%\Microsoft Silverlight"