Recent Posts
Don Hite
Return To Blog Listing
Tags: Break Time, By Request, Microsoft SQL Server, Odds & Ends, PowerShell, SMS 2003 SQL Queries, Vbs Scripts
Recent Posts Tagged With 'sql queries'
SQL Query To Count Installed Resources By Site
This SQL query will display the count for resources installed on a site. SQL Query: Select Count(SS.ResourceID) Resources, Sc.SiteCode 'Site Code' From v_RA_System_SMSInstalledSites SS Join v_Site SC On SS.SMS_In...
SQL Query To Find Machine Resources With No SMS Unique Identifiers Or GUIDS
This SQL Query will list all of the machine resources that do not have an SMS Unique Identifier or GUID. SQL Query: Select SD.Name0 'Machine Name' From System_Disc SD Join MachineIdGroupXRef ID On SD.ItemK...
SQL Query To Retrieve The Column Names For All Tables In A Specified Database
This SQL Query will allow you to view the column names for all of the tables in a specified database sorted by their Ordinal position. SQL Query: Select Table_Name, Column_Name From Information_Schema.Columns Where Colum...
Asset Intelligence Customized Catalog Custom Labels SQL Query
Provided here is a SQL version of the Asset Intelligence customized catalog custom labels report. SQL Query: Select CategoryName Name, Description From v_LU_Category_Editable Where Type = 2 Order by CategoryName ...
Asset Intelligence Asset Intelligence Reports SQL Query
Provided here is a SQL query version of the Asset Intelligence asset intelligence reports report. SQL Query: Select Name, Category, ReportID 'Report ID' From Report Where Category = 'Asset Intelligence' O...
Inventoried Software Titles Unidentified And Not Pending Online Identification SQL
Provided here is a SQL version of the Asset Intelligence inventoried software titles unidentified and not pending online identification report. SQL Query: Select CommonName 'Product Name', IsNull(CommonPublisher, '&#...
Asset Intelligence Hardware Requirements SQL Query
Provided here is a SQL version of the Asset Intelligence hardware requirements report. SQL Query: Select Product 'Software Title', MinCPU 'Minimum CPU (MHz)', MinRAM 'Minimum RAM (KB)', MinDiskFree 'Mi...
Asset Intelligence Customized Catalog Software Categories SQL Query
Provided here is a SQL version of the Asset Intelligence customized catalog software categories report. SQL Query: Select CategoryName 'Category Name', Description, 'Type' = Case When Type = 0 Then 'Validated...
Inventoried Software Titles All Inventoried Software Titles SQL Query
Provided here is a SQL version of the Asset Intelligence inventoried software titles all inventoried software titles report. SQL Query: Select CommonName 'Product Name', IsNull(CommonPublisher, '') Vendor, IsNull...
Asset Intelligence Customized Catalog Software Families SQL Query
Provided here is a SQL version of the Asset Intelligence customized catalog software families report. SQL Query: Select CategoryName Name, Description, 'Type' = Case When Type = 1 Then 'Validated' End Fro...
SQL Query To Return Your SQL Servers Property Information
This SQL Query will allow you to retrieve Property information about your SQL server. SQL Query: Select ServerProperty('ServerName') 'Server Name', ServerProperty('ComputerNamePhysicalNetBIOS') 'Physi...
SQL Query To Retrieve The Assigned Site And Installed Site Code For Discovered Resources
This SQL Query will return the machine name, assigned site code and installed site code for all discovered resources as well as their client state. SQL Query: Select SD.Name0 'Machine Name', A.SMS_Assigned_Sites0 'As...
SQL Query To Retrieve ConfigMgr 2007 Site Server Primary Information
This SQL query will return primary information for a ConfigMgr 2007 Site Server. The following information will be displayed: Site Server name, Site Name, Site Code, the Report To Parent Site (If any), the Site Type either Primary or Se...
SQL Query To List Startup Applications On A Specified Machine
This SQL Query will enumerate or list the applications on a specified machine that start when the Operation system starts. SQL Query: Select SA.Product0 'Product Name', SA.Publisher0 Publisher, SA.Description0 Descriptio...
SQL Query To List All Uncategorized Asset Intelligence Inventoried Software Titles
This SQL Query will list all of the Asset Intelligence inventoried software titles that are uncategorized. SQL Query: Select IsNull(CommonName, '') 'Product Name', IsNull(CommonPublisher, '') Vendor, IsNul...
SQL Query To Find All IBM And Lenovo Machines
This SQL Query will provide you with a list of all of the IBM and Lenovo machines. SQL Query: Select SD.Name0, CS.Manufacturer0 From v_R_System SD Join v_GS_Computer_System CS On SD.ResourceId = CS.ResourceId ...
SQL Queries To Retrieve The Views For A Specified SQL Database
Provided here you will find two SQL queries to allow you to list all of the Views from a specified SQL Database. The first query uses the Tables Schema and the second one uses the Views Schema. SQL Query: Tables Schema Se...
SQL Query To Retrieve the Machine Name For A Specified GUID Number
This SQL Query will allow you to locate the machine name for a specified SMS Unique Identifier or GUID. SQL Query: Select Name0 'Machine Name', SMS_Unique_Identifier0 GUID From v_R_System Where SMS_Unique_Identifier0 =...
SQL Query To Locate Machines That Generated New GUIDs
This SQL Query will list machines existing and aged or previous GUIDs as well as the time stamp for when the new GUID was generated. SQL Query: Select Name0 'Machine name', SMS_Unique_Identifier0 GUID,...
SQL Query To Find Machines With Multiple GUIDs
Sometimes in the ConfigMgr 2007 console you may see a machine resource listed twice where one Item key may show the machine as obsolete and not active while the other item key shows the same machine name as not obsolete and active. Yet the mac...
SQL Query To Retrieve SQL Views Creation Syntax
This SQL Query will allow you to see the “Create View” script syntax for all of the views in the database from which the script is executed on. SQL Query: Select Table_Name, View_Definition From Information_Schema.V...
SQL Query To Retrieve SQL Procedures Creation Syntax
This SQL Query will allow you to see the “Create Procedure” script syntax for all of the procedures in the database from which the script is executed on. SQL Query: Select Routine_Name, Routine_Definition From Informa...
SQL Query To Retrieve SQL Function Creation Syntax
This SQL Query will allow you to see the “Create Function” script syntax for all of the functions in the database from which the script is executed on. SQL Query: Select Routine_Name, Routine_Definition From Informati...
SQL Query To Enumerate Applications Used In The Last 24 Hours For A Specified Machine
This SQL Query will enumerate or list the applications used in the last 24 hours on a specified machine. The script is designed to run on ConfigMgr 2007 that has Asset Intelligence information collected. Note: The results set list all o...
SQL Query To Gather Portable Laptops And Notebook Machines Information
This SQL Query will gather the following information for Portables, Laptops and Notebook resources: Machine Name, Asset Tag, Manufacturer, Model, Serial Number and Operating System version information. SQL Query: Select SD....
SQL Query To Find IP Subnets Not Specified In Your ConfigMgr 2007 Subnet Boundaries
This SQL Query will list all of the subnets discovered that are not included in your ConfigMgr 2007 subnet site Boundaries SQL Query: Select Distinct IP_Subnets0 From v_Ra_System_IpSubnets Where v_Ra_System_IpSubnets.IP_Subnets...
SQL Query To Count ConfigMgr 2007 Subnet Boundary Resources
This SQL query will count the resources for your ConfigMgr 2007 site boundaries. The usefulness of this script is that it will allow you to see any site boundaries that currently do not have any resources. This will allow you to adjust your si...
SQL Queries To Find Applications Installed On Machines By Group, Container, OU Or Subnet
Provided here you will find various SQL queries that will allow you to retrieve distinct application information such as the display name, publisher and version as listed in the Add and Remove programs applet on machines by querying a Group, C...
Client Asset Information SQL Query
Provided here is a Client Asset Information SQL Query that will gather the following information: Machine Name, Installed Site Code, Resource Domain, Account Domain, Login ID, Users Full Name, Asset Tag, Manufacturer, Model, Serial Numb...
SQL Query To List Machines Rebooted In The Last Week
This SQL Query will list all of the machines that have been rebooted in the last week or seven days. SQL Query: Select SD.Name0, Convert(VarChar(30), OS.LastBootUpTime0, 109) 'Last Boot Date' From v_R_Syste...
