|
The information in this article applies to:
- Microsoft Systems Management Server, versions 1.0, 1.1 and 1.2
SUMMARY
There are problems associated with Querying within SMS - SMS Query Results
Display Only First Data Record. Another application is necessary to
retrieve the correct data. For instance, Access can be used to retrieve
drive information from the SMS Database Views.
MORE INFORMATION
To retrieve drive information from SMS Databease Views using Access,
perform the following steps.
- Run SMSVIEW to create the views.
- Confirm SQL connectivity using ODBC Manager.
- Start Access and create a new database.
- Select File, and then select Get External Data.
- Select Import and in Files of Type select ODBC Databases.
- Select your SMS data source and log in to SQL.
- Select all the tables preceded by 'dbo.v' - the views.
- Open the Tools / Relationships and Added dbo.vDisk and
dbo_vIdentification.
- In the first table, select dwMachineID. Drag and drop dwMachineID into
the second table.
- Open the Relationships window and select Create.
- In the Database window, select Queries and select New.
- Use the Simple Query Wizard.
- At the first screen, select dbo.vIdentification and choose SMSID0 as
the selected field. Select dbo.vDisk and choose Disk_Index0,
Storage_Size_MByte_0, Storage_Used_MByte_0, Free_Storage_MByte_0, and
__Disk_Full0 as selected fields.
- At the next screen select Detail. At the Final screen give it a
title and select Finish. At this point you should see all your SMSIDs
and associated drives.
- Select View and selct SQL. You should see a query similar to:
SELECT DISTINCTROW [dbo.vIdentification].[SMSID0],
[dbo.vDisk].[Disk_Index0], [dbo.vDisk].[Storage_Size__MByte_0],
[dbo.vDisk].[Storage_Used__MByte_0], [dbo.vDisk].[Free_Storage__MByte_0],
[dbo.vDisk].[__Disk_Full0]
FROM dbo.vDisk INNER JOIN dbo.vIdentification ON
dbo.vDisk].[dwMachineID]=[dbo.vIdentification].[dwMachineID];
- Modify this query to have the following as the final statement (note
the query ends with the semicolon(;), so remove it from the original
query before adding the final line):
WHERE (((dbo.vDisk.Disk_Index0)>"B"));
Here is an example output:
SMSID0 Disk_Index0 Storage_Size__MByte_0 Storage_Used__MByte_0
Free_Storage__MByte_0 __Disk_Full0
UUU01000 C 324 284 40 88
UUU01000 D 324 267 57 83
UUU02000 C 502 398 104 80
UUU02000 D 1545 1426 119 93
UUU02001 C 514 347 167 68
UUU01001 C 514 347 167 68
UUU01002 C 202 171 31 85
|