SCCM

Nice to Know – Defender Sandbox

So another step in the right direction for Windows Defender, it can now run in sandboxed mode. For now you have to turn it on but in the future that will be default.

If you want to read more about the release of this check out the cloudblog from MS here https://cloudblogs.microsoft.com/microsoftsecure/2018/10/26/windows-defender-antivirus-can-now-run-in-a-sandbox/?fbclid=IwAR1BX92wvaqmse7bgucQtPbi_Si6XY1cMfIec9JW1XttX-4wqttIU39mokM

So lets assume you also run ConfigMgr, now this is where its gets intreseting. We can then use a CI to track if it has been turned on!

This is done using a very simple detection script.

image

Here is the small code snippet used to track compliance.

if ($env:MP_FORCE_USE_SANDBOX -eq 1) {
  return $true
}
else {
  return $false
}

Now two things remain, set the data type to boolean and as compliance set to “True”.

All set to measure this. Of course a simple script or package can now be used to force the setting of this, just remeber that its only supported on Windows 10 1703 and later and will require a reboot before taking effect.

Happy deploymnet!

/Peter

Advertisements

SQL Server Inventory using ConfigMgr

The hardware inventor feature of ConfigMgr helps us discover and track several items. One recurring item is SQL Server and by default there is no nice way of tracking this. Basically the only option out of the box is using the application inventory to find where SQL server is installed. However this usualy results in a large list with mixed servers and clients and you still have no idea if its running SQLExpress, Standard or Enterprise edtion.

So what can we do to fix this besides buy a third party solution. Well the good thing is that SQL stores all the information into WMI, this means we can track editions, installations and versions with a bit of trickery with the hardware inventory engine.

There are number of steps included here at it is important to follow all of them for a successful result.

Step 1 – Update Configuration.mof

The first part of this is updateing configuration.mof. This file will control classes that inventoried and then index into the database.

The file can be a bit tricky to find if you have never done this before. You need to go to this location <Your ConfigMgr Install folder>\Inboxes\clifiles.src\hinv

In here is the configuration.mof file, now first make a backup copy of the file in case you want to revert the changes later and don’t want edit the file again.

At the very end of the file there is a section for adding custom extensions.

//========================

// Added extensions start

//========================

//========================

// Added extensions end

//========================

In between these block we need to add the following

//———————————————

// SQL 2017 Properties

//———————————————

[Union, ViewSources{“select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,

ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement14”}, dynamic,

Provider(“MS_VIEW_INSTANCE_PROVIDER”)]

class cm_sql17

{

[PropertySources{“IsReadOnly”}        ] boolean IsReadOnly;

[PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex;

[PropertySources{“PropertyName”},key  ] string PropertyName;

[PropertySources{“PropertyNumValue”}  ] uint32 PropertyNumValue;

[PropertySources{“PropertyStrValue”}  ] string PropertyStrValue;

[PropertySources{“PropertyValueType”} ] uint32 PropertyValueType;

[PropertySources{“ServiceName”},key   ] string ServiceName;

[PropertySources{“SqlServiceType”},key] uint32 SqlServiceType;

};

//———————————————

// SQL 2016 Properties

//———————————————

[Union, ViewSources{“select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,

ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement13”}, dynamic,

Provider(“MS_VIEW_INSTANCE_PROVIDER”)]

class cm_sql16

{

[PropertySources{“IsReadOnly”}        ] boolean IsReadOnly;

[PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex;

[PropertySources{“PropertyName”},key  ] string PropertyName;

[PropertySources{“PropertyNumValue”}  ] uint32 PropertyNumValue;

[PropertySources{“PropertyStrValue”}  ] string PropertyStrValue;

[PropertySources{“PropertyValueType”} ] uint32 PropertyValueType;

[PropertySources{“ServiceName”},key   ] string ServiceName;

[PropertySources{“SqlServiceType”},key] uint32 SqlServiceType;

};

//———————————————

// SQL 2014 Properties

//———————————————

[Union, ViewSources{“select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,

ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement12”}, dynamic,

Provider(“MS_VIEW_INSTANCE_PROVIDER”)]

class cm_sql14

{

[PropertySources{“IsReadOnly”}        ] boolean IsReadOnly;

[PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex;

[PropertySources{“PropertyName”},key  ] string PropertyName;

[PropertySources{“PropertyNumValue”}  ] uint32 PropertyNumValue;

[PropertySources{“PropertyStrValue”}  ] string PropertyStrValue;

[PropertySources{“PropertyValueType”} ] uint32 PropertyValueType;

[PropertySources{“ServiceName”},key   ] string ServiceName;

[PropertySources{“SqlServiceType”},key] uint32 SqlServiceType;

};

//———————————————

// SQL 2012 Properties

//———————————————

[Union, ViewSources{“select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,

ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement11”}, dynamic,

Provider(“MS_VIEW_INSTANCE_PROVIDER”)]

class cm_sql12

{

[PropertySources{“IsReadOnly”}        ] boolean IsReadOnly;

[PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex;

[PropertySources{“PropertyName”},key  ] string PropertyName;

[PropertySources{“PropertyNumValue”}  ] uint32 PropertyNumValue;

[PropertySources{“PropertyStrValue”}  ] string PropertyStrValue;

[PropertySources{“PropertyValueType”} ] uint32 PropertyValueType;

[PropertySources{“ServiceName”},key   ] string ServiceName;

[PropertySources{“SqlServiceType”},key] uint32 SqlServiceType;

};

//———————————————

// SQL 2008 Properties

//———————————————

[Union, ViewSources{“select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,

ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement10”}, dynamic,

Provider(“MS_VIEW_INSTANCE_PROVIDER”)]

class cm_sql08

{

[PropertySources{“IsReadOnly”}        ] boolean IsReadOnly;

[PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex;

[PropertySources{“PropertyName”},key  ] string PropertyName;

[PropertySources{“PropertyNumValue”}  ] uint32 PropertyNumValue;

[PropertySources{“PropertyStrValue”}  ] string PropertyStrValue;

[PropertySources{“PropertyValueType”} ] uint32 PropertyValueType;

[PropertySources{“ServiceName”},key   ] string ServiceName;

[PropertySources{“SqlServiceType”},key] uint32 SqlServiceType;

};

//———————————————

// SQL 2000/2005 Properties

//———————————————

[Union, ViewSources{“select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,

ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement”}, dynamic,Provider(“MS_VIEW_INSTANCE_PROVIDER”)]

class cm_sql2kand05

{

[PropertySources{“IsReadOnly”}        ] boolean IsReadOnly;

[PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex;

[PropertySources{“PropertyName”},key  ] string PropertyName;

[PropertySources{“PropertyNumValue”}  ] uint32 PropertyNumValue;

[PropertySources{“PropertyStrValue”}  ] string PropertyStrValue;

[PropertySources{“PropertyValueType”} ] uint32 PropertyValueType;

[PropertySources{“ServiceName”},key   ] string ServiceName;

[PropertySources{“SqlServiceType”},key] uint32 SqlServiceType;

};

Save the configuration.mof file

Done! On to step two.

Step 2 – Import hardware inventory information

The next step is done in the console and you need some nice permissions to do this. You will need to edit the default client settings object.

First thing is first. So we need to create a custom mof file to import. To do this, copy the following and save it as SQLProperties.mof (pick a nice location, like the desktop).

//=================SQL 2017 Information

[dynamic, provider(“MS_VIEW_INSTANCE_PROVIDER”),

SMS_Report(TRUE),

SMS_Group_Name(“SQL17 Property”),

SMS_Class_ID(“CUSTOM|SQL17_Property|1.0”)]

class cm_sql17 : SMS_Class_Template

{

[SMS_Report(TRUE)    ]  boolean IsReadOnly;

[SMS_Report(TRUE),key]  uint32 PropertyIndex;

[SMS_Report(TRUE),key]  string PropertyName;

[SMS_Report(TRUE)    ]  uint32 PropertyNumValue;

[SMS_Report(TRUE)    ]  string PropertyStrValue;

[SMS_Report(TRUE)    ]  uint32 PropertyValueType;

[SMS_Report(TRUE),key]  string ServiceName;

[SMS_Report(TRUE),key]  uint32 SqlServiceType;

};

//=================SQL 2016 Information

[dynamic, provider(“MS_VIEW_INSTANCE_PROVIDER”),

SMS_Report(TRUE),

SMS_Group_Name(“SQL16 Property”),

SMS_Class_ID(“CUSTOM|SQL16_Property|1.0”)]

class cm_sql16 : SMS_Class_Template

{

[SMS_Report(TRUE)    ]  boolean IsReadOnly;

[SMS_Report(TRUE),key]  uint32 PropertyIndex;

[SMS_Report(TRUE),key]  string PropertyName;

[SMS_Report(TRUE)    ]  uint32 PropertyNumValue;

[SMS_Report(TRUE)    ]  string PropertyStrValue;

[SMS_Report(TRUE)    ]  uint32 PropertyValueType;

[SMS_Report(TRUE),key]  string ServiceName;

[SMS_Report(TRUE),key]  uint32 SqlServiceType;

};

//=================SQL 2014 Information

[dynamic, provider(“MS_VIEW_INSTANCE_PROVIDER”),

SMS_Report(TRUE),

SMS_Group_Name(“SQL14 Property”),

SMS_Class_ID(“CUSTOM|SQL14_Property|1.0”)]

class cm_sql14 : SMS_Class_Template

{

[SMS_Report(TRUE)    ]  boolean IsReadOnly;

[SMS_Report(TRUE),key]  uint32 PropertyIndex;

[SMS_Report(TRUE),key]  string PropertyName;

[SMS_Report(TRUE)    ]  uint32 PropertyNumValue;

[SMS_Report(TRUE)    ]  string PropertyStrValue;

[SMS_Report(TRUE)    ]  uint32 PropertyValueType;

[SMS_Report(TRUE),key]  string ServiceName;

[SMS_Report(TRUE),key]  uint32 SqlServiceType;

};

//=================SQL 2012 Information

[dynamic, provider(“MS_VIEW_INSTANCE_PROVIDER”),

SMS_Report(TRUE),

SMS_Group_Name(“SQL12 Property”),

SMS_Class_ID(“CUSTOM|SQL12_Property|1.0”)]

class cm_sql12 : SMS_Class_Template

{

[SMS_Report(TRUE)    ]  boolean IsReadOnly;

[SMS_Report(TRUE),key]  uint32 PropertyIndex;

[SMS_Report(TRUE),key]  string PropertyName;

[SMS_Report(TRUE)    ]  uint32 PropertyNumValue;

[SMS_Report(TRUE)    ]  string PropertyStrValue;

[SMS_Report(TRUE)    ]  uint32 PropertyValueType;

[SMS_Report(TRUE),key]  string ServiceName;

[SMS_Report(TRUE),key]  uint32 SqlServiceType;

};

//=================SQL 2008 Information

[dynamic, provider(“MS_VIEW_INSTANCE_PROVIDER”),

SMS_Report(TRUE),

SMS_Group_Name(“SQL Property”),

SMS_Class_ID(“CUSTOM|SQL_Property|2.0”)]

class cm_sql08 : SMS_Class_Template

{

[SMS_Report(TRUE)    ]  boolean IsReadOnly;

[SMS_Report(TRUE),key]  uint32 PropertyIndex;

[SMS_Report(TRUE),key]  string PropertyName;

[SMS_Report(TRUE)    ]  uint32 PropertyNumValue;

[SMS_Report(TRUE)    ]  string PropertyStrValue;

[SMS_Report(TRUE)    ]  uint32 PropertyValueType;

[SMS_Report(TRUE),key]  string ServiceName;

[SMS_Report(TRUE),key]  uint32 SqlServiceType;

};

//==================SQL Information 2000 and 2005

[dynamic, provider(“MS_VIEW_INSTANCE_PROVIDER”),

SMS_Report(TRUE),

SMS_Group_Name(“SQL Property Legacy”),

SMS_Class_ID(“CUSTOM|SQL_Property_Legacy|2.0”)]

class cm_sql2kand05 : SMS_Class_Template

{

[SMS_Report(TRUE)    ]  boolean IsReadOnly;

[SMS_Report(TRUE),key]  uint32 PropertyIndex;

[SMS_Report(TRUE),key]  string PropertyName;

[SMS_Report(TRUE)    ]  uint32 PropertyNumValue;

[SMS_Report(TRUE)    ]  string PropertyStrValue;

[SMS_Report(TRUE)    ]  uint32 PropertyValueType;

[SMS_Report(TRUE),key]  string ServiceName;

[SMS_Report(TRUE),key]  uint32 SqlServiceType;

};

Next, head on over to the Administration workspace in the ConfigMgr console and find the Client Settings in the menu to the left. Open up the Default Client Settings and check the Hardware Inventory tab.

There is a nice little button named Set Classes, hit that and then hit import. Browse to the SQLProperties.mof file and hit Open.

You will be greated by this screen where you need to make sure the Import both hardware inventory classes and hardware inventory class settings is selected

sqlinv02

If you are like me and don’t want this inventory to hit each and every device you have deselect the following classes in your default settings. If you are only using the default client settings object, please skip to the next step.

sqlinv01

If you are using custom client settings, please add the above classes to be included in hardware inventory under the same tabe as we did the import.

This means SQL information will now be imorted into the database! We can now create collections based on this, please do leave enough time for hardware inventory to run on each client before you find data in the database.

Step 3 – The Report

For me the last piece of the pussel is to create a custom report to easily show me the information needed. I will not cover the details of how to create a custom reports as the options are plenty. However I will share the query used to get a nice detailed view with the server and edition info.

Note that this is a simple report and can be extended in a lot of ways and if you are already using PowerBI you can extract the same data that way.

Query used:

— SQL 2017
select
sys1.Netbios_name0 as [Computer Name]
,max(Case sql6.PropertyName0 when ‘VERSION’ then
(case
when sql6.PropertySTRValue0 like ‘9.0%’ then ‘SQL 2005’
when sql6.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2′
when sql6.PropertySTRValue0 like ’10.%’ then ‘SQL 2008′
when sql6.PropertySTRValue0 like ’11.%’ then ‘SQL 2012′
when sql6.PropertySTRValue0 like ’12.%’ then ‘SQL 2014′
when sql6.PropertySTRValue0 like ’13.%’ then ‘SQL 2016′
when sql6.PropertySTRValue0 like ’14.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]
,max(Case sql6.PropertyName0 when ‘SKUName’ then
sql6.PropertySTRValue0 end) as [SQL Type]
,max(Case sql6.PropertyName0 when ‘SPLEVEL’ then
sql6.PropertyNUMValue0 end) as [SQL Service Pack]
,max(Case sql6.PropertyName0 when ‘VERSION’ then
sql6.PropertySTRValue0 end) as [SQL Version]
,max(Case sql6.PropertyName0 when ‘FILEVERSION’ then
sql6.PropertySTRValue0 end) as [SQL CU Version]
from v_r_system sys1
left join v_gs_custom_SQL17_Property0 sql6 on sys1.ResourceID=sql6.ResourceID
where
sql6.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)
group by sys1.Netbios_name0
union

— SQL 2016
select
sys1.Netbios_name0 as [Computer Name]
,max(Case sql5.PropertyName0 when ‘VERSION’ then
(case
when sql5.PropertySTRValue0 like ‘9.0%’ then ‘SQL 2005’
when sql5.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2′
when sql5.PropertySTRValue0 like ’10.%’ then ‘SQL 2008′
when sql5.PropertySTRValue0 like ’11.%’ then ‘SQL 2012′
when sql5.PropertySTRValue0 like ’12.%’ then ‘SQL 2014′
when sql5.PropertySTRValue0 like ’13.%’ then ‘SQL 2016′
when sql5.PropertySTRValue0 like ’14.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]
,max(Case sql5.PropertyName0 when ‘SKUName’ then
sql5.PropertySTRValue0 end) as [SQL Type]
,max(Case sql5.PropertyName0 when ‘SPLEVEL’ then
sql5.PropertyNUMValue0 end) as [SQL Service Pack]
,max(Case sql5.PropertyName0 when ‘VERSION’ then
sql5.PropertySTRValue0 end) as [SQL Version]
,max(Case sql5.PropertyName0 when ‘FILEVERSION’ then
sql5.PropertySTRValue0 end) as [SQL CU Version]
from v_r_system sys1
left join v_gs_custom_SQL16_Property0 sql5 on sys1.ResourceID=sql5.ResourceID
where
sql5.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)
group by sys1.Netbios_name0
union

— SQL 2014
select
sys1.Netbios_name0 as [Computer Name]
,max(Case sql4.PropertyName0 when ‘VERSION’ then
(case
when sql4.PropertySTRValue0 like ‘9.0%’ then ‘SQL 2005’
when sql4.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2′
when sql4.PropertySTRValue0 like ’10.%’ then ‘SQL 2008′
when sql4.PropertySTRValue0 like ’11.%’ then ‘SQL 2012′
when sql4.PropertySTRValue0 like ’12.%’ then ‘SQL 2014′
when sql4.PropertySTRValue0 like ’13.%’ then ‘SQL 2016′
when sql4.PropertySTRValue0 like ’14.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]
,max(Case sql4.PropertyName0 when ‘SKUName’ then
sql4.PropertySTRValue0 end) as [SQL Type]
,max(Case sql4.PropertyName0 when ‘SPLEVEL’ then
sql4.PropertyNUMValue0 end) as [SQL Service Pack]
,max(Case sql4.PropertyName0 when ‘VERSION’ then
sql4.PropertySTRValue0 end) as [SQL Version]
,max(Case sql4.PropertyName0 when ‘FILEVERSION’ then
sql4.PropertySTRValue0 end) as [SQL CU Version]
from v_r_system sys1
left join v_gs_custom_SQL14_Property0 sql4 on sys1.ResourceID=sql4.ResourceID
where
sql4.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)
group by sys1.Netbios_name0
union

— SQL 2012
select
sys1.Netbios_name0 as [Computer Name]
,max(Case sql3.PropertyName0 when ‘VERSION’ then
(case
when sql3.PropertySTRValue0 like ‘9.0%’ then ‘SQL 2005’
when sql3.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2′
when sql3.PropertySTRValue0 like ’10.%’ then ‘SQL 2008′
when sql3.PropertySTRValue0 like ’11.%’ then ‘SQL 2012′
when sql3.PropertySTRValue0 like ’12.%’ then ‘SQL 2014′
when sql3.PropertySTRValue0 like ’13.%’ then ‘SQL 2016′
when sql3.PropertySTRValue0 like ’14.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]
,max(Case sql3.PropertyName0 when ‘SKUName’ then
sql3.PropertySTRValue0 end) as [SQL Type]
,max(Case sql3.PropertyName0 when ‘SPLEVEL’ then
sql3.PropertyNUMValue0 end) as [SQL Service Pack]
,max(Case sql3.PropertyName0 when ‘VERSION’ then
sql3.PropertySTRValue0 end) as [SQL Version]
,max(Case sql3.PropertyName0 when ‘FILEVERSION’ then
sql3.PropertySTRValue0 end) as [SQL CU Version]
from v_r_system sys1
left join v_gs_custom_SQL14_Property0 sql3 on sys1.ResourceID=sql3.ResourceID
where
sql3.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)
group by sys1.Netbios_name0
union

— SQL 2008
Select
sys1.Netbios_name0 as [Computer Name]
,max(Case sql2.PropertyName0 when ‘VERSION’ then
(case
when sql2.PropertySTRValue0 like ‘9.0%’ then ‘SQL 2005’
when sql2.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2′
when sql2.PropertySTRValue0 like ’10.%’ then ‘SQL 2008′
when sql2.PropertySTRValue0 like ’11.%’ then ‘SQL 2012′
when sql2.PropertySTRValue0 like ’12.%’ then ‘SQL 2014′
when sql2.PropertySTRValue0 like ’13.%’ then ‘SQL 2016′
when sql2.PropertySTRValue0 like ’14.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]
,max(Case sql2.PropertyName0 when ‘SKUName’ then
sql2.PropertySTRValue0 end) as [SQL Type]
,max(Case sql2.PropertyName0 when ‘SPLEVEL’ then
sql2.PropertyNUMValue0 end) as [SQL Service Pack]
,max(Case sql2.PropertyName0 when ‘VERSION’ then
sql2.PropertySTRValue0 end) as [SQL Version]
,max(Case sql2.PropertyName0 when ‘FILEVERSION’ then
sql2.PropertySTRValue0 end) as [SQL CU Version]
from v_r_system sys1
left join v_gs_custom_sql_property_2_00 sql2 on sys1.resourceid=sql2.ResourceID
where
sql2.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)
group by sys1.Netbios_name0
union

— SQL 2005
Select
sys1.Netbios_name0 as [Computer Name]
,max(Case sql1.PropertyName0 when ‘VERSION’ then
(case
when sql1.PropertySTRValue0 like ‘9.%’ then ‘SQL 2005’
when sql1.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2′
when sql1.PropertySTRValue0 like ’10.%’ then ‘SQL 2008′
when sql1.PropertySTRValue0 like ’11.%’ then ‘SQL 2012′
when sql1.PropertySTRValue0 like ’12.%’ then ‘SQL 2014′
when sql1.PropertySTRValue0 like ’13.%’ then ‘SQL 2016′
when sql1.PropertySTRValue0 like ’14.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]
,max(Case sql1.PropertyName0 when ‘SKUName’ then
sql1.PropertySTRValue0 end) as [SQL Type]
,max(Case sql1.PropertyName0 when ‘SPLEVEL’ then
sql1.PropertyNUMValue0 end) as [SQL Service Pack]
,max(Case sql1.PropertyName0 when ‘VERSION’ then
sql1.PropertySTRValue0 end) as [SQL Version]
,max(Case sql1.PropertyName0 when ‘FILEVERSION’ then
sql1.PropertySTRValue0 end) as [SQL CU Version]
from v_r_system sys1
left join v_gs_custom_sql_property_legacy_2_00 sql1 on sys1.ResourceID=sql1.ResourceID
where sql1.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)
group by sys1.Netbios_name0

This might not be the prettiest query every created or the smartest way (there are alot smarter people out there) so don’t go flaming it’s not optimized. It does the job : )

/Peter

Techdays 2017 Pre-Conf Slides

Me and Jörgen Nilsson (@ccmexec) did a pre-conf together at Techdays in sweden. And after much slacking here are now the slide deck from that pre-conf.

Here is the link to download the slidedeck!
https://1drv.ms/b/s!ArAh2CEqOjRkk-8ZDAVJ1vRqi8Glxg

Techday2017

/Peter

Techdays 2017

I have the great honor of presenting at TechDays sweden this year. I will be doing a pre conf together with Jörgen Nilsson about Client management. We will show of tips and trix, give some insight on how to surive the Windows As A Service change and talk about the future of client management.

Make sure to reserve your seat now and we will see you there. For more information check out the preconf site for TechDays here http://tdswe.se/events/windows-10-client-management-now-and-in-the-future-level-300/

Hope to see you there!

/Peter

ConfigMgr 1703 Technical Preview

ConfigMgr 1703 TP has just been released and I am really exited by all the new features! Three of the ones you should check out are the Collapsible task sequence groups, the ability to control Windows Analytics and the direct links to applications in software center

Collapsible Task Sequence Groups

MDT has had this for a long while but now ConfigMgr gets it as well. This will help out while editing a task sequence. And it looks a bit like this

Collapse

Windows Analytics

There is now a new settings group in the client settings that’s called Windows Analytics and there you can enter your Commercial ID (found in the settings of OMS) and select the type of telemetry data sent to Windows Analytics.

It looks a bit like this

CommericialID

When you get data back you can connect Windows Analytics with ConfigMgr and then get collections based on the data in Windows Analytics. For instance a collection with all machines that are ready for the next version of Windows without known upgrade issues.

Shiny!

Create direct links to applications in Software Center

So one request that has been around for a while is the ability for admins to give end users a direct link to the install section of an app in software center. This has previously been done with a workaround and calling the WMI methods involved. Starting with 1703 you can now easily deploy your own link as URL links.

Its pretty straight forward, create a URL link specify the link to be Softwarecenter:SoftwareID=<ApplicationID>

To find the ID either use powershell and the Get-CMApplication command or use the gui end show the column named “CI Unique ID”

You will then end up with a URL looking something like this

Softwarecenter:SoftwareID=ScopeId_96EF26AC-1571-4633-9CE4-1A51DCC38B84/Application_3e312927-bcd9-4fea-8066-0e407d051037

Note that this new feature requires the new agent version available with 1703 and does not work with previous agent versions.

Below is a short script that can help you easily create these shortcuts. Just run it on your CM Server or a machine with the console installed. Select the applications you want and it will create the shortcuts on your desktop ready for distribution to other machines.

<#
Created:     2017-03-31
Version:     1.0
Author :     Peter Lofgren
Twitter:     @LofgrenPeter
Blog   :     https://syscenramblings.wordpress.com

Disclaimer:
This script is provided "AS IS" with no warranties, confers no rights and
is not supported by the author

Updates
1.0 - Initial release
#>

# Import the ConfigurationManager.psd1 module
Import-Module "$($ENV:SMS_ADMIN_UI_PATH)\..\ConfigurationManager.psd1"
# Set the current location to be the site code.
Set-Location "$((Get-PSDrive -PSProvider CMSite).Name):"

#Get Application Name
$AppName = Get-CMApplication | Select LocalizedDisplayName | Out-GridView -PassThru
foreach ($App in $AppName) {
  $Application = Get-CMApplication -Name $App.LocalizedDisplayName

  #Get Application ID
  $ID = ($Application.CI_UniqueID -split "/")[0] + "/" + ($Application.CI_UniqueID -split "/")[1]

  #Create URL Link on desktop
  $Shell = New-Object -ComObject ("WScript.Shell")
  $Favorite = $Shell.CreateShortcut($env:USERPROFILE + "\Desktop\$($App.LocalizedDisplayName).lnk")
  $Favorite.TargetPath = "SoftwareCenter:SoftwareID=$ID";
  $Favorite.IconLocation = "C:\Windows\ccm\SCClient.exe, 0";
  $Favorite.Save()
}

$Favorite.Save()

If you want to read more about the other new features check out the post by the team here https://blogs.technet.microsoft.com/enterprisemobility/2017/03/30/update-1703-for-configuration-manager-technical-preview-branch-available-now/

/Peter

System Center ConfigMgr 1702

Here we go again. New version of ConfigMgr! This time around it brings a bunch of new cool features and improvements. It also brings along the end of the 2008 era for site servers and SQL servers.

This is all good news but it requires some planning and managinig before an upgrade can be done if you are still running Windows Server 2008 or SQL server 2008.

For a complete list of what’s new and removed check out the official documentation here.

https://docs.microsoft.com/en-us/sccm/core/plan-design/changes/whats-new-in-version-1702

 

Happy deploying!

/Peter

Keeping Track of PowerShell versions

In today enterprises many are faced with the challenge of managing both Windows 7, 8, 8.1 and 10. This means that most have a multitude of PowerShell versions out there which in turn does not ease the management tasks faced.

If you are running ConfigMgr 2012 or later you have access to one of my favorite features called Compliance Settings. Use this feature you can easily keep track of your environments different settings and measure compliance. One of the things I like to measure is the current running PowerShell version. I do this for two reasons. Number one, I want to now that my systems are running the version set out as a baseline. Number two is that if they are not running the correct version I get an easy way of finding them all and hence an easy way of correcting it.

So the tasks including creating a Configuration Item, linking it to a Configuration Baseline, deploying said baseline to a collection of workstations and creating a collection of devices that are not running the correct version.

Step 1 – Creating the Configuration Item

In your ConfigMgr console find the Assets and Compliance workspace and then under Compliance Settings you will find Configuration Items.

Create a new one and give it a name, I will be using “PowerShell Version”. Make sure that Settings for device managed with ConfigMgr Client is set to “Windows Desktops and Servers (custom)”.

In the next pane select the appropriate Operating Systems that this can be run on. Hint, Windows XP does not support PowerShell.

On the settings pane, hit New and in the configuration set a Name, again “PowerShell version” works just fine. Set the Setting type to “Script” and the datatype to Integer. Hit the “Add Script” button for Discovery script and paste in the following script and then hit OK.

[int]$Version = $PSVersionTable.PSVersion.Major
return $Version

On the Compliance Rules pane hit New and give the Rule a name. I’m calling it BaselineVersion. Hit the browse button and select your Current CI and the Version setting we just created. The rule type should be set to Value and in the comply part set the value returned must “Equal” and then set your desired baseline version. 4 will give you an OK on Windows 8.1 and Windows 10 and 5 will only give you an OK on Windows 10 (this assumes you have not previously upgraded your WMF versions). Hit OK and then Next.

Review your setting on the summary pane and hit next when ready to create the Configuration Item

Step 2 – Creating a Configuration Baseline

Head over to the Configuration Baselines workspace and create a new baseline. Please note this can both be included in previously created baselines but I prefer a separate for this so I can later use the non compliance feature. Give the Baseline a name, “PowerShell”. Hit Add, Select Configuration Item and select your previously created CI.

Step 3 – Deploying the Baseline

This should feel very normal to most of you since it’s the same procedure as deploying any application or client setting. Right click your baseline and select deploy. The wizard will not look like the usual deployment wizards but all you have to do is select a collection to deploy to. I recommend avoiding deploying it to the built-in collections and instead do two deployments if you want to monitor both servers and clients. Before you hit OK change the Schedule to suite your response times. Default is 7 days which in a small environment can be forever but in a large environment it just around the corner.

Step 4 – Creating the non compliant collection

The last step is to create that all needed collection which you can deploy the new Windows Management Framework too. select your newly created baseline, look for a tab named Deployments a the bottom of the console. In this view you can see the collection the baseline has been deployed to.

Now right click the collection, select “Create New Collection” and then select “Non-Compliant”. Follow the new Collection wizard and not that the rule for membership is premade.

noncompliance

Last notes

Now all that remains is waiting for the devices to report back status and then end up in the Non-Compliant collection so you can remedy them.

For your Windows 7 machines please note that if you have not previously upgraded Windows Management Framework you will need to install both WMF4 and WMF5. WMF4 is a prerequisite for WMF5 and both require a reboot to complete. This might be a good time for a small custom task sequence.

 

/Peter