SCCM – Create user collections based on Active Directory department attribute with Powershell

sccm logo

We recently built a new Configuration Manager system for a client who wanted user collections for all departments and companies within their corporate group.

We were going to set these up manually which is a fairly simple, but time consuming, job to do. But this was a time restricted installation so we had a quick Google and found an awesome Powershell script written by Timmy Andersson over at CTGlobal which would create all the department collections we required.

Full instructions and all the Powershell scripts you would need to carry this out is available from here.

We used this script and it worked perfectly, especially as their AD was all up to date with department and company data and we had already setup the discovery for these attributes for other parts of reporting that they wanted.

We also then amended this script slightly to carry out company collection creations as well. This saved us a few days work and were able to hand over way before the deadline.

SCCM – Create a device collection based on Exchange Server role

sccm logo

Following on from our recent posts for Configuration Manager collection queries, we have another one here for all devices that are Microsoft Exchange servers.

Go ahead and create a new device collection and then use this query:

select
*
from
SMS_R_System
inner join SMS_G_System_SERVICE
on SMS_G_System_SERVICE.ResourceId = SMS_R_System.ResourceId
where SMS_G_System_SERVICE.Name like “Microsoft Exchange%”

Save the query and you should now see a collection of all your Exchange Servers.

Just be careful with copying and pasting in case the formatting of the symbols comes out incorrectly. Check these first if your collection fails to import and devices.

More Queries

Our full range of SQL and WQL Collection queries are available here.

Feedback

If you have any questions or feedback about this post, or if you would like us to create any queries for you, please go ahead and leave us a message below in the comments section and we will get back to you as quick as we can.

Rescue Lost Data with Free Data Recovery Software

Logo EaseUS

Missing files is a huge hassle regardless if they are connected to your personal life or your professional one. As losing an important piece of a document could cost you a great deal in both cases. To help you out we are offering you a free data recovery software by that will help you recover deleted files. So you do not have to worry about any file you might have accidentally deleted or lost. The software is designed to help you gain access to the files you thought were long gone. So you can rest assured, that you have not had to suffer a great loss due to an accidental delete, or an accident.

How can you Recover Deleted Files?

Recovering your files is easy, and all you need is a free data recovery software. Once you have downloaded and installed EaseUS Data Recovery Wizard, then next thing you need to do is to launch the software. Once opened, you can guide the software to the place where the file resided before you deleted it. Once you have decided on the place, you can start to scan the space for the files that were recently lost or deleted. The entire process is fairly simple, and you do not have to worry about anything. As after you have launched it, the software will walk you through the process.

The process can be followed on your computer, or any device you have attached to your computer that has the EaseUS Data Recovery Wizard downloaded on it. As the initial scan will help you check out all the files you have recently deleted, the ones that were cleaned from the recycle bin, or the ones that were lost due to some other reason. If the device is running, there is a chance you can get all your files from it safely. Furthermore, the deep scan will kick in as soon as the quick scan is over, and the software will start looking for all the lost files. This will take longer depending on the files you had on your computer or device, but in the end, you will be able to access your lost files.

Once the scan is completed you can review these files and, and after selecting the files you wanted; all you need to do is click on “Recover”. As soon as you do, the free data recovery software will start to recover your files, and save them on your device. However, you should consider saving these files in some other device, so you do not have to go through the process again.

What Type of Files Can Be Recovered?

The EaseUS Data Recovery software will allow you to recover documents, graphics, videos, audio, emails, and many other files. For photographers, it can be used exclusively as a photo recovery tool There is a complete range offered under each aspect, so you do not have to worry the format your files were in. Allowing you to use the recovery software, and gain access to your once lost of deleted files.

Enable Windows Remote Desktop Protocol and Set Firewall Rules using PowerShell

powershell

If you are using Windows Server 2012 or newer, or if you are just using Windows Server Core, then this Powershell script will help you out by enabling Remote Desktop and setting the correct firewall inbound rules.

Simply open up an elevated Powershell command box, or use Powershell ISE and use the two scripts below, one to enable Remote Desktop and then the second to set the firewall ports correctly.

Enable Remote Desktop

(Get-WmiObject Win32_TerminalServiceSetting -Namespace root\cimv2\TerminalServices).SetAllowTsConnections(1,1)
(Get-WmiObject -Class “Win32_TSGeneralSetting” -Namespace root\cimv2\TerminalServices -Filter “TerminalName=’RDP-tcp'”).SetUserAuthenticationRequired(0)

Enable Inbound Firewall Rule

Enable-NetFirewallRule -DisplayGroup “Remote Desktop”

Now if you try and remote desktop on your server, it should prompt for a login and go straight in.

Feedback

If you have any questions or feedback on this article, please feel free to leave us a message below in our comments section.

SCCM – Create a device collection based on Domain Controller role

microsoft black logo

We have another WQL query here for you today to enable you to create a Configuration Manager collection based on whether a device is used as a domain controller.

Go ahead and create a new device collection and then use this query:

select
*
from
SMS_R_System
inner join SMS_G_System_COMPUTER_SYSTEM
on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId
where SMS_G_System_COMPUTER_SYSTEM.Roles like "%Domain_Controller%"

Save the query and you should now see a collection of all your domain controllers.

Just be careful with copying and pasting in case the formatting of the symbols comes out incorrectly. Check these first if your collection fails to import and devices.

More Queries

Our full range of SQL AND WQL Collection queries are available here.

Feedback

If you have any questions or feedback about this post, or if you would like us to create any queries for you, please go ahead and leave us a message below in the comments section and we will get back to you as quick as we can.

Java 8 Update 161 MSI Installers and Offline Installers for x86 and x64 Browsers Released

Java MSI

We have now uploaded to our Filestore microsite the MSI installers and Offline Installers for the latest Java version release.

The versions that have been added are:

  • Java 8 Update 161 x86 MSI Installer
  • Java 8 Update 161 x64 MSI Installer
  • Java 8 Update 161 x86 Offline Installer
  • Java 8 Update 161 x64 Offline Installer

You can go and download these new installers by clicking the Download button below and going to our new filestore download website:

Download

Java 8 Update 161

Instructions

Full instructions on how to use the offline and MSI installers for mass distribution are also available on the microsite.

Comments

If you have any questions, feedback or comments about this post, please feel free to leave us a message below in our comments section.

SCCM – Hardware and Client Version SQL Query

sql2016

If you are looking for a SQL query to pull back hardware inventory information and the SCCM client version, then you can use the below to pull all this information together.

SELECT
S.Name0 as Name,
S.Client_Version0 as [Cient Version],
OS.Caption0 as [Operating System],
CS.Manufacturer0 as Manufacturer,
CS.Model0 as Model,
PB.SMBIOSBIOSVersion0 as BIOS,
PR.Name0 as CPU,
SUM(PM.Capacity0) as Memory,
SUM(LD.Size0)as [Disc Size],
SUM(LD.FreeSpace0) as [Disc Free]
FROM
v_R_System S
join v_GS_OPERATING_SYSTEM OS on S.ResourceID = OS.ResourceID
join v_GS_PHYSICAL_MEMORY PM on S.ResourceID = PM.ResourceID
join v_GS_COMPUTER_SYSTEM CS on S.ResourceID = CS.ResourceID
join v_GS_LOGICAL_DISK LD on S.ResourceID = LD.ResourceID
join v_GS_PC_BIOS PB on S.ResourceID = PB.ResourceID
join v_GS_PROCESSOR PR on S.ResourceID = PR.ResourceID
WHERE LD.DeviceID0 = ‘C:’
GROUP
BY S.ResourceID, S.Name0, S.Client_Version0, OS.Caption0, CS.Manufacturer0, CS.Model0, PB.SMBIOSBIOSVersion0, PR.Name0

More Queries

Our full range of SQL and WQL Collection queries are available here.

Feedback

If you have any questions or feedback about this post, or if you would like us to create any queries for you, please go ahead and leave us a message below in the comments section and we will get back to you as quick as we can.

5 Home Gadgets That Will Actually Make Your Life Better

Technology

Technology continues to creep into every aspect of life as it advances, and your home is no exception. New gadgets are introduced continuously that make life just a little bit easier. Consider adding these five devices to your home to take advantage of advancements in technology and simplify your life. Smart thermostat A smart thermostat … Read more

Adobe Flash Player MSI Installers v28.0.0.137

Adobe Flash Player MSI Package

Adobe have released another update to their Adobe Flash Player software, this time taking it up to version 28.0.0.137.

As always, you can download the executable file but if you want the MSI files so that you can load it into your Configuration Manager Software Catalog, you can get that here.

The update is also now available to import through System Center Update Publisher (SCUP) so you can inject it into your WSUS or Configuration Manager systems.

You can the download the MSI installers for Adobe Flash (ActiveX, Plugin, PPAPI) by clicking the button link below:

Download

Download Flash Player MSI Package

Comments

If you have any feedback or questions, please feel free to leave a message below in our comments section.

SCCM SQL Query for Software not in Add Remove Programs

sccm logo 600x400

If you are looking for an SCCM SQL query to create a report to search for machines which do not have a particular software installed, you can use the following:

Select Distinct
 sys.Netbios_Name0,
 sys.User_Domain0,
 sys.User_Name0
FROM
 v_R_System sys
JOIN v_Add_Remove_Programs arp
 ON sys.ResourceID = arp.ResourceID
WHERE
 sys.ResourceID not in
 (select sys.ResourceID
 from
 v_R_System sys
 JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
 WHERE
 DisplayName0 = @displayname)

Then you can use the variable @displayname to choose from a drop down of software that is in the SCCM database pulled from client machines. The query you can use for that is:

select
 DisplayName0
 from
 v_Add_Remove_Programs

You should be able to use these to create a report that will prompt for a selection from all the available data.

More Queries

Our full range of SQL and WQL Collection queries are available here.

Feedback

If you have any questions or feedback about this post, or if you would like us to create any queries for you, please go ahead and leave us a message below in the comments section and we will get back to you as quick as we can.

Video Demo – Windows Updates Stuck on Shut Down or Reboot Fix

Windows Update Stuck on Shutdown

We also have written guides for the different versions of Windows available here:

Feedback

We would love to hear your feedback on whether any of these methods helped you at all. If they have, please feel free to go ahead and leave us a message below using our comments section.