Query - TechyGeeksHome

SCCM – WQL Query for devices with specific software installed

17 February 202616 January 2014 by Andrew Armstrong

If you want to identify machines with a specific piece of software installed, you can utilise the Add/Remove Programs software inventory to do this by using this query. select distinct SMS_R_System.Name, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ClientVersion from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Microsoft Office Professional Plus 2010” order by SMS_R_System.Name In this … Read more

Report on Dell Warranty Statuses for all machines using SCCM & Dell Client Integration Pack

1 May 202614 January 2014 by Andrew Armstrong

Dell have an excellent tool freely available called Dell Client Integration Pack. Included in this tool is something called Warranty Status. What this part of the tool does is looks at your SCCM SQL database for Dell machines and their asset tags, then checks against its own warranty database for the warranty information. It then inserts … Read more

SCCM – WQL Query for machines with a valid Hardware Scan in the last x days

16 February 20259 January 2014 by Andrew Armstrong

If you would like to identify the machines that have had a valid hardware inventory scan within the last x amount of days, you can use this WQL query as per below. select SMS_R_System.Name, SMS_G_System_WORKSTATION_STATUS.LastHardwareScan from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId where DateDiff(dd,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate() ) <= 15 This query is defaulted to 15 … Read more

SCCM – WQL Query for MAC Address Prompt

17 February 20268 January 2014 by Andrew Armstrong

If you would like to search for a machine based upon its MAC address, you can use a WQL query to do this. The WQL query that you should use for this is below: select distinct SMS_R_System.Name, SMS_R_System.MACAddresses from SMS_R_System where SMS_R_System.MACAddresses = ##PRM:SMS_R_System.MACAddresses## order by SMS_R_System.MACAddresses More Queries Our full range of SQL and … Read more

SCCM – Query for devices in a specific Active Directory Organisational Unit (OU)

17 February 20267 January 2014 by Andrew Armstrong

If you are looking for a SCCM query query that will return all machines that are in a specified organisational unit (OU), then you can use this one below. All you need to do is change the demo OU below to your own required OU. Organisational Unit TECHYGEEKSHOME/LAB/COMPUTERS/WINDOWS 10 Query Script SELECT distinct * FROM SMS_R_System … Read more

SCCM – SQL Query for newly discovered devices in last 24 hours

1 May 20262 January 2013 by Andrew Armstrong

This query for SCCM will tell you the devices that have been discovered by Configuration Manager in the last 24 hours. This query can be amended to change the amount of days you wish to look back at for newly discovered devices by changing the last digit in the code. SQL Query More Queries Our … Read more

Find last logged on user by NetBIOS name – SCCM Query

17 February 20262 January 2013 by Andrew Armstrong

I’ve been asked a lot of questions about the various ways of using queries and there are almost no limits to what you can do with a query and of course, you can then go on and use these queries for creating collections or just to supply you with the information you require. I have … Read more

SCCM – Last logged on user query – All Systems

17 February 20262 January 2013 by Andrew Armstrong

If you are looking for a query that will display all machines and their last logged on user, the below will do this for you without any prompts. This allows you to search on the data to find what you would like across the information provided by the query. The query below is the full … Read more

SCCM Query to create collection for x86 & x64 machines

17 February 20261 January 2013 by Andrew Armstrong

We have been asked on a number of occasions how to create Configuration Manager Collections for machines on x86 or x64 Windows Operating Systems. To do this, you should use the two collection queries as below: Instructions Query to create collection for x86 machines Query to create collection for x64 machines More Queries Our full … Read more

SCCM – SQL Query for showing number of different Operating Systems

17 February 202611 December 2012 by Andrew Armstrong

If you would like to create a report to show you the number of different Operating System’s you have, you can use this script below which should provide you with the information for Servers and the various Windows OS’s. Query SELECT CASE WHEN Caption0 LIKE ‘%XP%’ THEN ‘XP’ WHEN Caption0 LIKE ‘%Windows 7%’ THEN ‘Windows … Read more

SCCM Collection Queries

17 February 202627 November 2012 by Andrew Armstrong

We have been building a number of collections within SCCM lately so we thought we would start to build up a reference here of various collections, some simple, some more complex. We will be adding to this when we can so make sure you come back and check for updates. Computers with certain prefixes – … Read more

SCCM – Find system by Last Logged on User Query

16 February 202531 October 2012 by Andrew Armstrong

If you need to find a machine for a particular user in your business using SCCM, one way you can do it is to use a custom query to find the machine that relates to the last logged on user. This means that you can run this query, it’ll prompt for a username, enter the … Read more