The SMS Database

By ServerWatch Staff (Send Email)
Posted Jan 25, 1999


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.

Page 1 of 1


Comment and Contribute

Your name/nickname

Your email

(Maximum characters: 1200). You have characters left.