The Ultimate SCCM SQL Script for Comprehensive Information Retrieval

Everything you need from a System Center SQL Script

System Center Configuration Manager (SCCM) is a powerful tool used by IT professionals to manage and deploy software, monitor system health, and gather information about users, computers, and more. One of the key components of SCCM is its SQL database, which stores a wealth of data that can be queried and analyzed to gain valuable insights. If you're an SCCM Admin, then you probably spend a lot of time in SQL Management Studio. I know I do.

If you're looking for a single SQL script that can capture all the information you need about users, computers, software, and more, look no further. This comprehensive SCCM SQL script will provide you with a holistic view of your environment and empower you to make informed decisions.

With this script, you can easily retrieve user information such as usernames, email addresses, department, and more. I am constantly asked for different information relating to users and computers. here are some examples:

• I have a list of email addresses, can you tell me what computers they are using?

• Can you let me know all the computers that have this software?

• Can I get a list of all the versions of this software and who is using it?

• We need a list of all computers at so-n-so site.

• can I get a list of users from this physical office?

• What version of Windows are we running and can they be separated by department?

• What models of hardware are we using?

• Can you create a list of computers for all the users in this group?

• I need a list of software installed in this folder

• can you list all the systems that haven't logged in within the last 30 days?

• and so on....

Anywho, here is the script.

Customizing the Script

This SCCM SQL script is highly customizable, allowing you to tailor it to your specific needs. You can modify the queries to include additional fields or filter the results based on specific criteria. By leveraging the power of SQL, you can extract the exact information you require and present it in a meaningful way.

With this ultimate SCCM SQL script, you can unlock the full potential of SCCM and harness the power of data to optimize your IT operations. By retrieving comprehensive information about users, computers, software, and system health, you can make informed decisions, streamline processes, and ensure the efficiency and security of your organization.

Customizing the script is either as simple as commenting out (--) or uncommenting () certain sections. Or changing the query values to match your need.

You will need to have the proper discovery methods enabled within your environment and the attribute you want to query.

Obtaining Computer Details

Need to know the hardware specifications of your computers or their last logged-on user? This SCCM SQL script has got you covered. It enables you to retrieve information about computer models, operating systems, disk space, and much more.

Tracking Software Inventory

Keeping track of software installations and licenses can be a daunting task, especially in large organizations. This SCCM SQL script simplifies the process by providing you with a comprehensive software inventory report. You can easily identify installed software and who is using it.

Alternatively you could uncomment one of the following to limit your searched to a user, computer, computer model, or specific operating system.

If you do not need a full inventory of all software, then you would also uncomment a line under the where clause. Typically it would be one of these lines to determine who has a certain software title installed, or who has the software installed in a specific folder, or even who has this file somewhere on their machine?

• One is for the installation data details. Think about Add/Remove Programs or Programs and Features.

  1. v_GS_INSTALLED_SOFTWARE = Via Hardware inventory = Lists information about the installed software applications on Configuration Manager clients found through Asset Intelligence.

  2. you must have defined the items that you want collected for this to pull data. here is a diagram below on where to add hardware classes that you want inventoried.

Modifying the script to get application data.

There are two lines for retrieving specific application data form the computers.

• The second is for the specific files. These are winword.exe, acrord32.exe, etc.

  1. v_GS_SoftwareFile = Via software inventory =Lists the files and associated product IDs on each Configuration Manager client.

  2. you must have defined the items that you want collected for this to pull data. here is a diagram below on where to add files that you want inventoried.

     1. Typically you just see *.exe, but I worked in a company that had me add audio and video file types, pdf's, pst's, etc. if you want to know who has what, then this is how you do it.

Download the Script