ServerWatch content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
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.
