Here is a list of common items that people want to query for and some locations they're found in the SMS database
I'm basing this on SMS 2003, we have SP3+
Depending on your setup, you may not have some of these views in your environment or they may be empty. You may also find other places where these items exist.
This is not indended to be an exhaustive list, just a quick reference of items that most people want to see in a web report at some point and the places you can find these items.
Keep in mind, just because you can find a field in a certain place, doesn't mean it's the right place for you in your query. You need to understand the data, understand your query and understand why you're including something in your query.
| ITEM TO
FIND |
VIEWS WHERE ITEM IS FOUND |
COLUMNS (may
be in more than one) |
| Collection IDs |
dbo.v_FullCollectionMembership |
CollectionID |
| Computer Make |
dbo.v_GS_COMPUTER_SYSTEM |
Manufacturer0 |
| Computer Make |
dbo.v_GS_COMPUTER_SYSTEM_PRODUCT |
Vendor0 |
| Computer Make |
dbo.v_GS_BASEBOARD |
Manufacturer0 |
| Computer Make |
dbo.v_GS_PC_BIOS |
Manufacturer0 |
| Computer Model |
dbo.v_GS_COMPUTER_SYSTEM |
Model0 |
| Computer Model |
dbo.v_GS_COMPUTER_SYSTEM_PRODUCT |
Name0 |
| Computer Name |
dbo.v_R_System |
Netbios_Name0, Name0 |
| Computer Name |
dbo.v_GS_COMPUTER_SYSTEM |
Name0 |
| Computer Name |
dbo.v_RA_System_ResourceNames |
Resource_Name0 |
| Computer Name |
dbo.v_GS_SYSTEM |
Name0 |
| Computer Name |
dbo.v_GS_PROCESSOR |
SystemName0 |
| Computer Name |
dbo.v_GS_MOTHERBOARD_DEVICE |
SystemName0 |
| IP Addresses |
dbo.v_RA_System_IPAddresses |
IP_Addresses0 |
| Last Bootup Time |
dbo.v_GS_OPERATING_SYSTEM |
LastBootupTime0 |
| Last HW Inventory |
dbo.v_GS_WORKSTATION_STATUS |
LastHWScan |
| MAC Addresses |
dbo.v_RA_System_MACAddresses |
MAC_Addresses0 |
| OS Install Date |
dbo.v_GS_OPERATING_SYSTEM |
InstallDate0 |
| OS Name |
dbo.v_GS_OPERATING_SYSTEM |
Caption0 |
| OS Name And Version |
dbo.v_R_System |
Operating_System_Name_and0 |
| OS Version |
dbo.v_GS_OPERATING_SYSTEM |
Version0 |
| OU |
dbo.v_RA_System_SystemOUName |
System_OU_Name0 |
| Serial/Identifying Number |
dbo.v_GS_COMPUTER_SYSTEM_PRODUCT |
IdentifyingNumber0 |
| Serial/Identifying Number |
dbo.v_GS_PC_BIOS |
SerialNumber0 |
| Serial/Identifying Number |
dbo.v_GS_BASEBOARD |
SerialNumber0 |
| Site Code |
dbo.v_RA_System_SMSAssignedSites |
SMS_Assigned_Sites0 |
| User Name |
dbo.v_GS_COMPUTER_SYSTEM |
UserName0 |
| User Name |
dbo.v_R_System |
User_Name0 |
| User Name |
dbo.v_GS_SYSTEM_CONSOLE_USER |
SystemConsoleUser0 |
| User Name |
dbo.v_GS_SYSTEM_CONSOLE_USAGE |
TopConsoleUser0 |
| User Name |
dbo.v_LastLoggedOnUser |
USERID0 |
I'm open to suggestions/questions. If you'd like to know where to find something, let me know and I'll see about adding it to the list
Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog
