GuidesThe SMS Database

The SMS Database




by Coates

(This page will be viewed best if you set your browser to the
width of the following line)

Retrieving specific data from the SMS database can sometimes be a
bit tricky. The standard views supplied by Microsoft are useful up
to a point, but the main thing that they lack is the ability to
access historical data.

——————————————————————————————————————– 

Retrieving specific data from the SMS database can sometimes be a
bit tricky. The standard views supplied by Microsoft are useful up
to a point, but the main thing that they lack is the ability to
access historical data. This can be useful for many reasons, such as
determining whether any components have been changed, checking IP
adresses that may have been assigned to the machine, whether a
machine has changed SMS Sites, or estimating future disk usage from
historical data. This last example shall form the basis of this
introduction to the SMS database.

The Main Tables

There are three main tables which form the ‘backbone’ of the SMS
database (db), GroupMap, MachineDataTable and
MachineDataHistoryTable. Descriptions of these tables follow.

GroupMap

Below is part of the GroupMap table from an SMS db. Some columns
and rows have been omitted for clarity. This data can be obtained by
typing the following command into ISQL/W.

SELECT ArchitectureKey,
CONVERT(CHAR(20), GroupName),
Groupkey, CommonTableName, SpecificTableName FROM
GroupMap

And the results are ………..

ArchitectureKey
GroupName           
GroupKey   
CommonTableName                 
SpecificTableName

————— ——————– ———–
——————————–
——————————–


1              
Identification      
1                                           
PackageLocations                
2              
Identification      
1                                           
UserGroups                      
3              
Identification      
1                                           
JobDetails                       
4              
Identification      
1                                           
SMSEvent                        
5              
Identification      
1          
Identification_COMM             
Identification_SPEC             
5              
Workstation Status  
2                                           
WorkstationStatus               
5              
Mouse               
4          
Mouse_COMM                      
Mouse_SPEC                       
5              
Video               
5          
Video_COMM                      
Video_SPEC                      
5              
Processor           
6          
Processor_COMM                  
Processor_SPEC                  
5              
Operating System    
7          
Operating_System_COMM           
Operating_System_SPEC            
5              
Disk                
8          
Disk_COMM                       
Disk_SPEC                        
5              
PC
Memory           
9          
PC_Memory_COMM                  
PC_Memory_SPEC                  
5              
Network             
10         
Network_COMM                    
Network_SPEC

ArchitectureKey is an internal value used by SMS
and will not be discussed here. In this document, we will assume
that the ArchitectureKey is equal to 5 which for all the systems I
have worked with has always been the value for the workstation
Architecture. Other systems may have a different value, but unless
the system is very complex, it should not cause a problem in this
simple example.

GroupName is the name of each different type of
information recorded in the db. These groups can be in any order,
but are usually similar to those shown above.

GroupKey is the numeric index used to locate
information about the group.

CommonTableName is the name of the table where
the common information about the group is held. Common info is that
which may be repeated many times across multiple machines, such as
Disk Type & Size, Network Card etc.

SpecificTableName is the name of the table where
information which is specific to each machine is held, such as Free
Disk Space, IP Address etc.

MachineDataTable

This is the table where the current information about all
machines is held. Information can be found about any machine by
referencing the dwMachineID found in the vIdentification view. For
example, the following command will show all the current rows that
correspond to machine ‘WKSTN1’

SELECT * FROM MachineDataTable WHERE
dwMachineID = (SELECT dwMachineID FROM vIdentification WHERE Name0 =
‘WKSTN1’)

This returns the following results (again, somewhat truncated)
……..

dwMachineID ArchitectureKey
GroupKey    InstanceKey SpecificKey
CommonKey   TimeKey

———– —————
———– ———– ———– ———–
—————————

41371      
5              
6          
12585      
0          
9           20 Jun
1997
15:19                 
41371      
5              
1          
3022       
29698      
0           15 Dec
1998
9:53                    
41371      
5              
6          
5616       
0          
4           28 Oct
1996
11:39                   
41371      
5              
14         
2761       
0          
46          31 Jul 1998
9:49                   
41371      
5              
15         
11045      
1          
1           21 Apr
1997
10:38                 
41371      
5              
15         
11046      
2177       
2           26 Feb
1997
9:28                   
41371      
5              
15         
11047      
1          
3           21 Apr
1997
10:38                   
41371      
5              
15         
11048      
1          
4           21 Apr
1997
10:38                      
41371      
5              
8          
6876       
162612     
6           15 Dec
1998
9:53                    
41371      
5              
16         
2770       
1          
1           21 Apr
1997
10:38                     
41371      
5              
9          
2864       
5227       
0           20 Jun
1997
15:19                  
41371      
5              
4          
2866       
0          
2           28 Oct
1996
11:39                      
41371      
5              
7          
5636       
0          
4           28 Oct
1996
11:39                 
41371      
5              
7          
5637       
0          
5           28 Oct
1996
11:39                  
41371      
5              
5          
2866       
4622       
28          15 Dec 1998
9:53                    
41371      
5              
17         
44177      
179        
0           21 Apr
1997 10:38

dwMachineID
is the machine id obtained from vIdentification used to
find entries for a specific machine.

ArchitectureKey is as discussed above. Note how
in this table it is always 5, the value for workstation
Architecture.

GroupKey corresponds to the GroupKey in the
GroupMap. Note how there can be more than one entry for each
GroupKey. In this db, group 15 is the Serial Port Group, and each
one of these entries will correspond to one of COM1 to COM4.

InstanceKey I haven’t got this one worked out
yet. Any ideas anyone?

SpecificKey is the entry in the specific table
for this entry in this group.

CommonKey is the entry in the specific table for
this entry in this group.

TimeKey is the time at which this entry was
made.

MachineDataHistoryTable

This table has exactly the same structure as the
MachineDataTable. Entries are made to this table when an entry is to
be updated in the MachineDataTable; the old entry is moved to the
MachineDataHistoryTable and the new entry is added to the
MachineDataTable. The MachineDataHistoryTable is referenced by the
SMS administrator console when the history buttons are pressed.

Latest Posts

Related Stories