I attempted to go to Microsoft.com/smserver but I was instead redirected to Thesource.ofallevil.com/smserver Posted by dhite | with no comments Filed under: Odds & Ends
In response to my post “Searching Your SQL Database for A Specified Column Name” many people have asked me if it is possible to go further into the SQL database to find not just a specified column header but actual SMS captured data such as an individual user or computer name.
To answer that question yes it is possible but it can become very complicated. We have to make use of Cursors to loop through tables and create temporary SQL tables to place all of the results into an array of sorts and then in turn enumerate that array to get the desired result set back from the query parser.
Note: In SQL server a Cursor, whether it is cursed or blessed by SQL DBA’s, is simply a result set that allows you to recursively loop through elements in a row by row operation on a returned result set. Cursors must adhere to specific rules and must include the following flow: First you must declare or set the Cursor, then open the cursor to gather information, then fetch or grab information from it and then finally close or deallocate the cursor to terminate it.
To use the SQL query below change the line Set @Locator = '%Don Hite%' from %Don Hite% to the string that you want to search for. As was mentioned in my original post the percent symbols are set to function as wildcard placeholders. You can either retain the percent symbols or remove one or both of them as you wish.
To run or execute the SQL query below simply copy and paste the SQL query below into the SQL query analyzer (Isqlw.Exe from the start > run line) use the database dropdown arrow to select your SMS database change the Set @Locator = to the keyword that you wish to locate and press the F5 keyboard shortcut to start the query parser.
SQL Query:
Declare @Locator VarChar(50)
Declare @Object Int
Declare @Column VarChar(50)
Declare @Qry NVarChar(500)
Set @Locator = '%Don Hite%'
Create Table #TableColumns
("Table Name" VarChar(50), "Column Name" VarChar(50))
Declare Table_Cursor Cursor For
Select Id From SysObjects
Where XType = 'U'
Open Table_Cursor
Fetch Next From Table_Cursor
Into @Object
While @@Fetch_Status = 0
Begin
Declare Column_Cursor Cursor For
Select SysColumns.Name
From SysColumns,SysTypes
Where SysColumns.Id = @Object
And SysColumns.XType = Systypes.XType
And Systypes.Name In ('char', 'nchar', 'nvarchar', 'varchar')
Open Column_Cursor
Fetch Next From Column_Cursor Into @Column
Set @Qry = 'Insert Into #TableColumns '
+ 'Select Distinct ''' + Object_Name(@Object) + ''', '
+ '''' + @Column + ''' '
+ 'From [' + Object_Name(@Object) + '] '
+ 'Where Exists (Select 1 From [' + Object_Name(@Object) + '] '
+ 'Where [' + @column + '] Like ''%' + @Locator + '%'') '
Exec Sp_ExecuteSql @Qry
End
Close Column_Cursor
DeAllocate Column_Cursor
Fetch Next From Table_Cursor Into @Object
Close Table_Cursor
DeAllocate Table_Cursor
Select "Table Name", "Column Name" From #TableColumns
Drop Table #TableColumns
This post will briefly explain the “SMS online library” available to you from the SMS 2003 Systems Management Server program group. It will also provide you with some tips and tricks to use to be more proficient navigating through it.
The SMS Online library that originally shipped with SMS 2003 has been updated with new and improved information and it is always at your disposal because it lives snugly inside the Systems Management Server program group. It was designed and intended for you to use if you are experiencing problems or if you are just trying to understand how things work.
The Systems Management Server’s SMS Online Library as mentioned can be started from within the program group or from the run command or a shortcut using the following command:
%WinDir%\Help\sms\00000409\sms.srv\htm\smswrapper.chm
Once you have opened the online library you are presented with the home screen as show above. From here simply select the hyperlinks to view any of the following subject lines:
Installed Documents and Documents on the CD
SMS 2003 Release Notes
%Windir%\Help\sms\00000409\sms.srv\htm\Readme.chm
What's New in this version of SMS 2003
%Windir%\Help\sms\00000409\sms.srv\htm\Whatsnew.chm
Documents on the Web
SMS 2003 Product Documentation
Http://www.microsoft.com/smserver/techinfo/productdoc/default.mspx
SMS Feature Packs
SMS 2003 Device Management Feature Pack
Http://www.microsoft.com/smserver/downloads/2003/dmfp.asp
SMS 2003 Operating System Deployment Feature Pack
Http://www.microsoft.com/smserver/downloads/2003/osdfp.mspx
The SMS online library help file can be accessed from anywhere within the SMS administrators console from the right-click context menu, by pressing the F1 function keys or from the run command or a shortcut using the following command:
%Windir%\Help\sms\00000409\sms.srv\htm\Sms20hlp.chm
When launched you are then presented a new screen and from here you are provided with the Contents, Index, Search and Favorites tabs.
The help files (Sms20hlp.chm) opens to the Contents leaf and contains the following main headings:
SMS Online Library
SMS Administrator Console
Security
Configuring SMS Site Settings
Managing and Locating Resources
Managing Inventory
Distributing Software to Clients
Distributing Software Updates
Using Repots to Organize Information
Monitoring Software Usage
Using Remote Tools to Manage Clients
Maintaining a Sire Database
Backup and Recovery
Monitoring Your System
Understanding Product Compliance Issues
Tools
Glossary
From here you can use the ‘Index’ where you can find a standard file index page that is listed alphabetically. You can either scroll through the Index page leaf or type in a keyword to search for. For example if you wanted to find information about the Recovery Expert simply type in the keywords ‘Recovery expert’ and it brings you to the “Recovery Expert” index heading. Then double-click ‘Overview’ and it will bring up the topics found context menu for you to select the topic that you are interested in.
To use the ‘Search’ feature all you need to do is enter any keywords or a brief description of the problems that you are experiencing. As in the example above type in the keywords ‘Recovery expert’ and select ‘List Topics’ or hit the enter key to bring up the results.
NOTE: Unlike the original help file you do not need to quote the search keywords to filter your results because this functionality is now built into the applet.
TIP: You can click the “Title” dialog bar to sort your results alphabetically to make it easier to navigate.
The ‘Favorites’ tab is where you are allowed to store the help pages that you frequently use for future use. This cuts down on the time that you would spend looking up information that you would use frequently.
When you are in either the Contents, Index or Search tabs looking for information simply click on the ‘Favorites’ tab and you will see the current page that you are looking at displayed to the left of the blank Topics page. Right mouse click into the blank area under ‘Topics:’ and then from the context menu select ‘Add’ to place the current article into your favorites. To remove or display your favorites highlight the article in question and select either ‘Remove’ or ‘Display’ to perform your action.
To navigate away from any tab that you are working in click the home icon (The house or home picture) that is located neatly in the top taskbar and you will be placed back to the original start page.
Once you are familiar with the Contents, Index, Search and Favorites tabs you should become familiar with the icons at the top of the screen.
Hide – This hides the left hand section so that you can only see the page that you are looking in.
Show – This is only displayed if you have selected the ‘Hide’ tab and when it is selected the ‘Hide’ tab once again becomes visible.
Back – By selecting this icon you are always moved back to your previous screen. You can select this icon as many times as needed until you are back to the point that you started at which is usually the home page if you launched the applicati0on form the program group.
Forward – This allows you to move forward when used in conjunction with the Back icon.
Home – The home icon as stated previously will bring you back to the home or original page that was displayed when you opened the application.
Options – By selecting this icon you are presented with a context menu that will allow you to do the following. You may notice that many items here have their own icons presented to you in the icon bar as well.
Hide Tabs – Same as the Hide icon.
Back – Same as the Back icon.
Forward – Same as the forward icon
Home – Same as the Home icon.
Stop – This allows you to stop your searches if they are in progress.
Refresh – This allows you to refresh the current screen if needed.
Internet Options – This is the same as the internet options found in the Tools menu of Microsoft Internet explorer.
The last section of the options menu contains the two following options:
Print – This is where you would print your topics.
Search highlight off – This allows you to turn the search highlighting function off. If it is currently turned off it is then displayed as Search highlight on. This allows you to toggle it as needed.
TIP: The Search highlight function can be useful if you want to copy the contents of the search results leaf to an application such as Microsoft Word and not have the highlighted text copied as such to the clipboard buffer by simply turning it on.
Additional Information
Within the \%WinDir%\Help\sms\00000409\sms.srv\htm directory you can find all of the information found in the SMS Online Library as well as additional information as detailed below:
DistribSwUpdateContext.chm – Information about the Distribute Software Updates Wizard.
ReadMe.chm – Contains the Systems Management Server 2003 Service OPack 2 Installation Release Notes
ReadMe.htm – Contains the Systems Management Server 2003 Service Pack 2 Installation Release Notes
Readme_Operations.htm – Contains the Systems Management Server 2003 Service Pack 2 Operations Release Notes
Sms20hlp.chm – Contains the Administrator Help as used when the F1 key or the Help icon is selected in the SMS console.
Sms_Srw.chm – Provides you with information for the Microsoft SMS Recovery and Repair Tools
SmsWrapper.chm – This is the SMS online library as it appears in the SMS 2003 Systems Management Server program group.
WhatsNew.chm – Contains the what’s New in SMS 2003 Service Pack 2 notes
NOTE: When Microsoft releases service packs to SMS 2003 as well as SMS 2.0 the online help is always updated to provide you with the most current and up to date information available.
For those people that have only one SMS primary site server to manage opening the SMS administrations console and looking at the System Status leaf is usually not much of a time consuming chore. However if you have a parent site with several sites below it that task can start to become a taxing operation very rapidly.
The following SQL query will allow you to quickly look at your SMS sites component status messages and will allow you to see when a site has reached the Warning or Critical stage. This in turn will allow you to save some time performing your daily management tasks.
The Summarizer_Components Status SQL table has the Status column name set as a 4 byte integer where 0 is OK, 1 is Warning and 2 represents Critical. The tally interval is currently set to 0001128000100008 which is Since 12:00 AM. Below you will find the currently available Tally Intervals for the Summarizer_ComponentTallys.TallyInterval.
Since 12:00 AM
0001128000100008
Since 04:00 AM
0081128000100008
Since 08:00 AM
0101128000100008
Since 12:00 PM
0181128000100008
Since 04:00 PM
0201128000100008
Since 08:00 PM
0281128000100008
Since Sunday
0001128000192000
Since Monday
00011280001A2000
Since Tuesday
00011280001B2000
Since Wednesday
00011280001C2000
Since Thursday
00011280001D2000
Since Friday
00011280001E2000
Since Saturday
00011280001F2000
Since 1st of month
000A470000284400
Since 15th of month
000A4700002BC400
Since site installation
0001128000080008
You can also modify the SQL query below to flag only those sites where the Summarizer_Components Status is either OK, in a Warning or in a Critical state by changing the line And SC.Status >= 1 to And SC.Status = 0, 1 or 2. The SQL query can also be modified further to see only those sites that have a specified state by changing the line And SC.Status to And SC.State and adding the State numeric value that you want to query for.
Select
SC.SiteCode,
SC.MachineName,
SC.ComponentName,
'SC.Status' = Case
When SC.Status = 0 Then 'OK'
When SC.Status = 1 Then 'Warning'
When SC.Status = 2 Then 'Critical'
Else ' '
End,
'SC.State' = Case
When SC.State = 0 Then 'Stopped'
When SC.State = 1 Then 'Started'
When SC.State = 2 Then 'Paused'
When SC.State = 3 Then 'Installing'
When SC.State = 4 Then 'Re-Installing'
When SC.State = 5 Then 'De-Installing'
ST.Errors,
ST.Infos,
ST.Warnings
From Summarizer_Components SC
Join Summarizer_ComponentTallys ST
On SC.ComponentDataID = ST.ComponentDataID
Where ST.TallyInterval = '0001128000100008'
And SC.Status >= 1
Order by ST.Errors
Searching Your SQL Database For A Specified Column Name
Sometimes you may find it necessary to search for a specific SQL table, view or procedure column name to develop your SQL or even your WQL queries. Using the following SQL query you can locate a predefined column or header name by specifying and setting a Column Variable (ColVar).
In the SQL query below we are setting the Column Variable to %Time%. By placing the % symbol both before and after the word “Time” we are asking the SQL query analyzer to parse or search all of the internal system columns (SysColumns ) for the database from which the query is initiated from where the columns name contains the “Time” string.
The word “Time” will be enumerated with additional characters both before and after the word “Time” where the percent symbols (%) are set to function as wildcard placeholders as in the query output example below:
Collections - LastChangeTime
pOperating_System_DATA - @LastBootUpTime0
v_AgentDiscoveries - AgentTime
If you were to remove the percent symbols from around the word “Time” by setting the ColVar to simply “Time” it will return to you only those columns that contained the word “Time” as in the examples below:
StatusMessages - Time
v_StatusMessage - Time
vStatusMessages - Time
Additionally we can set only one percent symbol, either before or after the “Time” string to look for columns that have the header xxxTime or Timexxx contained within them. Changing the line to “Set @ColVar = 'Time%'” would return for example the ‘Sites’ table name with the ‘TimeZoneInfo’ column name. By changing the line to Set @ColVar = '%Time' it would return for example the ‘InventoryAction’ table with the ‘LastUpdateTime’ column name.
To run or execute the SQL query simply copy and paste the SQL query below into the SQL query analyzer (Isqlw.Exe from the start > run line) use the database dropdown arrow to select your SMS database change the Set @ColVar = to the keyword that you wish to locate and press the F5 keyboard shortcut to start the query parser.
Set NoCount On
Declare @ColVar VarChar(30)
Set @ColVar = '%Time%' -- String To Find
Convert(Char(75), SysObjects.Name) 'Table Names:',
Convert(Char(75), SysColumns.Name) 'Column Names:'
From SysObjects, SysColumns, SysTypes
Where SysObjects.ID = SysColumns.ID
And SysColumns.xType = SysTypes.xType
And SysColumns.Name like @ColVar
Order by SysObjects.Name Asc
Set NoCount Off