Browse by Tags

All Tags » SQL Queries (RSS)
Monday, December 22, 2008 9:38 AM

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...
Posted by dhite | with no comments
Filed under:
Monday, December 22, 2008 9:38 AM

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...
Posted by dhite | with no comments
Filed under:
Sunday, December 14, 2008 3:39 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, December 14, 2008 3:39 PM

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
Posted by dhite | with no comments
Filed under:
Sunday, December 14, 2008 3:39 PM

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...
Posted by dhite | with no comments
Filed under:
Saturday, December 06, 2008 8:20 AM

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...
Posted by dhite | with no comments
Filed under:
Saturday, December 06, 2008 8:20 AM

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...
Posted by dhite | with no comments
Filed under:
Saturday, December 06, 2008 8:19 AM

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...
Posted by dhite | with no comments
Filed under:
Sunday, November 23, 2008 2:58 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, November 23, 2008 2:58 PM

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'...
Posted by dhite | with no comments
Filed under:
Sunday, November 16, 2008 7:45 PM

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'...
Posted by dhite | with no comments
Filed under:
Monday, November 10, 2008 5:50 PM

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' ...
Posted by dhite | with no comments
Filed under:
Sunday, November 02, 2008 3:21 PM

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)...
Posted by dhite | with no comments
Filed under:
Sunday, November 02, 2008 3:21 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, November 02, 2008 3:20 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, October 26, 2008 2:33 PM

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 ...
Posted by dhite | with no comments
Filed under:
Sunday, October 26, 2008 2:33 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, October 19, 2008 1:13 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, October 19, 2008 1:13 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, October 19, 2008 1:12 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, October 12, 2008 3:22 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, October 12, 2008 3:22 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, October 12, 2008 3:21 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, October 12, 2008 3:21 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, October 05, 2008 2:44 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, October 05, 2008 2:44 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, October 05, 2008 2:44 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, September 28, 2008 1:50 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, September 28, 2008 1:49 PM

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...
Posted by dhite | with no comments
Filed under:
Sunday, September 21, 2008 3:03 PM

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...
Posted by dhite | 1 comment(s)
Filed under:
More Posts Next page »