WQL/SQL

[DreamBroker]

Sorry i'm new (2 weeks with sms-server:-()
where can i find a description which wql-table-alias means which table in sql-server

 

[DreamBroker]

hmm found a workaround
u can take a look in the smsprov.log and c what sql command was made out of ur last wql command
but i don't like this, still need a kind of documentation

 

[snyderj]

I think this is what you are seeking. It's a SMS 2 cross-reference table that I made for myself. I think it's all correct, but I haven't validated it recently.

------- Copy the following text -------

WMI Current (History | ExtHistory),CIM studio parent (Current | History | ExtHistory),SQL Svr SMS Data (History),SQL Svr SMS Web Reports Current
SMS_R_System,SMS_BaseClass\SMS_Resource,System_DISC (HIST),v_R_System
SMS_G_System_LastSoftwareScan,SMS_BaseClass\SMS_Group\SMS_G_System,SoftwareInventoryStatus,v_GS_LastSoftwareScan
SMS_G_System_OPERATING_SYSTEM (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Operating_System_DATA (HIST),v_GS_OPERATING_SYSTEM
SMS_G_System_WORKSTATION_STATUS,SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],WorkstationStatus_DATA,v_GS_WORKSTATION_STATUS
SMS_G_System_COMPUTER_SYSTEM (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Computer_System_DATA (HIST),v_GS_COMPUTER_SYSTEM
SMS_G_System_DISK (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Disk_DATA (HIST),v_GS_DISK
SMS_G_System_LOGICAL_DISK (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Logical_Disk_DATA (HIST),v_GS_LOGICAL_DISK
SMS_G_System_PARTITION (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Partition_DATA (HIST),v_GS_PARTITION
SMS_G_System_DISPLAY_CONFIGURATION (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Display_Configuration_DATA (HIST),v_GS_DISPLAY_CONFIGURATION
SMS_G_System_DISPLAY_CONTROLLER_CONFIGURATION (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Display_Controller_Config_DATA (HIST),v_GS_DISPLAY_CONTROLLER_CONFI
SMS_G_System_DRIVER_VXD (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Driver_VxD_DATA (HIST),v_GS_DRIVER_VXD
SMS_G_System_VIDEO (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Video_DATA (HIST),v_GS_VIDEO
SMS_G_System_VIDEO_CONTROLLER (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Video_Controller_DATA,v_GS_VIDEO_CONTROLLER
SMS_G_System_KEYBOARD_DEVICE (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Keyboard_DATA,v_GS_KEYBOARD_DEVICE
SMS_G_System_MODEM_DEVICE (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Modem_DATA (HIST),v_GS_MODEM_DEVICE
SMS_G_System_MOTHERBOARD_DEVICE (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Motherboard_DATA (HIST),v_GS_MOTHERBOARD_DEVICE
SMS_G_System_POINTING_DEVICE (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Mouse_DATA (HIST),v_GS_POINTING_DEVICE
SMS_G_System_SCSI_CONTROLLER (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],SCSI_Controller_DATA (HIST),v_GS_SCSI_CONTROLLER
SMS_G_System_PARALLEL_PORT (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Parallel_Port_DATA (HIST),v_GS_PARALLEL_PORT
SMS_G_System_CDROM (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],CD_ROM_DATA (HIST),v_GS_CDROM
SMS_G_System_SOUND_DEVICE (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Sound_Devices_DATA,v_GS_SOUND_DEVICE
SMS_G_System_PRINTER_DEVICE (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],,—
SMS_G_System_TAPE_DRIVE (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Tape_Drive_DATA (HIST),v_GS_TAPE_DRIVE
SMS_G_System_NETWORK_ADAPTER (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Netcard_DATA (HIST),v_GS_NETWORK_ADAPTER
SMS_G_System_NETWORK_ADAPTER_CONFIGURATION (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Network_DATA (HIST),v_GS_NETWORK_ADAPTER_CONFIGUR
SMS_G_System_NETWORK_CLIENT (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Network_Client_DATA (HIST),v_GS_NETWORK_CLIENT
SMS_G_System_NETWORK_CONNECTION (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Network_Connection_DATA (HIST),v_GS_NETWORK_CONNECTION
SMS_G_System_PROCESSOR (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Processor_DATA (HIST),v_GS_PROCESSOR
SMS_G_System_SERVICE (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Services_DATA (HIST),v_GS_SERVICE
SMS_G_System_SoftwareFile,SMS_BaseClass\SMS_Group\SMS_G_System,sf | si,v_GS_SoftwareFile
SMS_G_System_UnknownFile,SMS_BaseClass\SMS_Group\SMS_G_System,sf | si,v_GS_UnknownFile
SMS_G_System_SoftwareProduct,SMS_BaseClass\SMS_Group\SMS_G_System,sp | si,v_GS_SoftwareProduct
SMS_G_System_SYSTEM (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],System_DATA (HIST),v_GS_SYSTEM
SMS_G_System_X86_PC_MEMORY (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],PC_Memory_DATA (HIST),v_GS_X86_PC_MEMORY
SMS_G_System_ENVIRONMENT (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],Environment_DATA (HIST),v_GS_ENVIRONMENT
SMS_G_System_PC_CASE_TYPE (GH | GEH),SMS_BaseClass\..._Group\..._G_System\...[Current|History|ExtHistory],PC_Case_Type_DATA (HIST),v_GS_PC_CASE_TYPE
SMS_StatusMessage,SMS_BaseClass\..._StatusMessage,StatusMessages,v_StatusMessage
SMS_G_System_Current,SMS_BaseClass\..._Group\..._G_System\...G_System_Current,,—
SMS_StatMsgAttributes,SMS_BaseClass\...StatMsgAttributes,,v_StatMsgAttributes
SMS_StatMsgInsStrings,SMS_BaseClass\...StatMsgInsStrings,,v_StatmsgInsStrings
SMS_StatMsgModuleNames,SMS_BaseClass\...StatMsgModuleNames,,v_StatMsgModuleNames
SMS_Advertisement,SMS_BaseClass\...Advertisement,,v_Advertisement
SMS_AdvertisementStatusRootSummarizer,SMS_BaseClass\...AdvertisementStatusRootSummarizer,,v_AdvertisementStatusRootSumm
SMS_AdvertisementStatusSummarizer,SMS_BaseClass\...AdvertisementStatusSummarizer,,v_AdvertisementStatusSummarize
SMS_AdvertToSourceSite_a,SMS_BaseAssociation\...AdvertToSourceSite_a,,—
SMS_Collection,SMS_BaseClass\...Collection,,v_Collection
SMS_CollectionMember,SMS_BaseClass\...CollectionMember,,—
SMS_CollectionMember_a,SMS_BaseAssociation\...CollectionMember_a,,—
SMS_CollectionToPkgAdvert_a,SMS_BaseAssociation\...CollectionToPkgAdvert_a,,—
SMS_CollectToSubCollect,SMS_BaseClass\...CollectionToSubCollect,,v_CollectToSubCollect
SMS_CollectToSubCollect_a,SMS_BaseAssociation\...CollectionToSubCollect_a,,vCollectToSubCollect
SMS_DistributionPoint,SMS_BaseClass\...DistributionPoint,,v_DistributionPoint
SMS_G_System_ExtHistory,SMS_BaseClass\...Group\...G_System\...G_System_ExtHistory,,—
SMS_G_System_History,SMS_BaseClass\...Group\...G_System\...G_System_History,,—
SMS_ObjectToClassPermissions_a,SMS_BaseAssociation\...ObjectToClassPermissions_a,,—
SMS_ObjectToInstancePermissions_a,SMS_BaseAssociation\...ObjectToInstancePermissions_a,,—
SMS_Package,SMS_BaseClass\...Package,,v_Package
SMS_PackageAccessByUsers,SMS_BaseClass\...PackageAccessByUsers,,—
SMS_PackageStatus,SMS_BaseClass\...PackageStatus,,—
SMS_PackageStatusDetailSummarizer,SMS_BaseClass\...PackageStatusDetailSummarizer,,v_PackageStatusDetailSumm
SMS_PackageStatusDistPointsSummarizer,SMS_BaseClass\...PackageStatusDistPoinstSummarizer,,v_PackageStatusDistPointsSumm
SMS_PackageStatusRootSummarizer,SMS_BaseClass\...PackageStatusRootSummarizer,,v_PackageStatusRootSummarizer
SMS_PackageToAdvert_a,SMS_BaseAssociation\...PackageToAdvert_a,,—
SMS_PackageToSourceSite_a,SMS_BaseAssociation\...PackageToSourceSite_a,,—
SMS_PkgToPkgAccess_a,SMS_BaseAssociation\...PkgToPkgAccess_a,,—
SMS_PkgToPkgProgram_a,SMS_BaseAssociation\...PkgToProgram_a,,—
SMS_PkgToPkgServer_a,SMS_BaseAssociation\...PkgToServer_a,,—
SMS_PropertyDisplayNode,SMS_BaseClass\...PropertyDisplayNode,,—
SMS_Query,SMS_BaseClass\SMS_Query,,v_Query
SMS_ResIDValueLookup,SMS_ResIDValueLookup,,—
SMS_ResourceMap,SMS_SiteInstallItem\...ResourceMap,,v_ResourceMap
SMS_SCFToSCI_a,SMS_BaseAssociation\...SCFToSCI_a,,—
SMS_SCFToSite_a,SMS_BaseAssociation\...SCFToSite_a,,—
SMS_SecuredObject,SMS_BaseClass\...SecuredObject,,v_SecuredObject
SMS_SetupInfo,SMS_BaseClass\...SetupInfo,,—
SMS_SIIB_AddressType,SMS_BaseClass\...SiteInstallItem\...SIIB_AddressType,,—
SMS_SIIB_Component_FileList,SMS_BaseClass\...SiteInstallItem\...SIIB_Component_FileList,,—
SMS_SIIB_Configuration,SMS_BaseClass\...SiteInstallItem\...SIIB_Configuration,,—
SMS_SIIB_Generic_Configuration,SMS_BaseClass\...SiteInstallItem\...SIIB_Generic_Configuration,,—
SMS_SIIB_NALProvider,SMS_BaseClass\...SiteInstallItem\...SIIB_NALProvider,,—
SMS_SIIB_SenderType,SMS_BaseClass\...SiteInstallItem\...SIIB_SenderType,,—
SMS_SIIB_SysResRole,SMS_BaseClass\...SiteInstallItem\...SIIB_SysResRole,,—
SMS_SIIB_UINALProvider,SMS_BaseClass\...SiteInstallItem\...SIIB_UINALProvider,,—
SMS_Site,SMS_BaseClass\...Site,,v_Site
SMS_SiteInstallItem,SMS_BaseClass\...SiteInstallitem,,—
SMS_SiteInstallItemBase,SMS_BaseClass\...SiteInstallitem\...SiteInstallItemBase,,—
SMS_SiteSystemSummarizer,SMS_BaseClass\...SiteSystemSummarizer,,—
SMS_SiteToROOTColl_a,SMS_BaseAssociation\...SiteToROOTColl_a,,—
SMS_SiteToSiteID_a,SMS_BaseAssociation\...SiteToSiteID_a,,—
SMS_SummarizerStatus,SMS_BaseClass\SummarizerStatus,,—
SMS_SupportedPlatforms,SMS_BaseClass\...SupportedPlatforms,,—
SMS_UserClassPermissionNames,SMS_BaseClass\...UserClassPermissionNames,,—
SMS_UserClassPermissions,SMS_BaseClass\...UserClassPermissions,,v_UserClassPermissions
SMS_UserInstancePermissionNames,SMS_BaseClass\...UserInstancePermissionNames,,v_UserClassPermissionsNames
SMS_UserInstancePermissions,SMS_BaseClass\...UserInstancePermissions,,v_UserInstancePermissions
SMS_Program,SMS_BaseClass\...Program,,v_Program
SMS_PropertyDisplayNode,SMS_BaseClass\...PropertyDisplayNode,,

------- end of copy -------

Paste the text into a text file and name it with a .csv extension, then open with Excel. The top row includes column headers.

If you're going to be developing direct SQL table queries, be aware that Microsoft does not publish the database schema and reserves the right to change it. (BTW, my first glance at SMS 2003 seemed to indicate that most of the data structure will remain quite similar.) The recommended method of data access is through WMI, though it's often painfully slow, and not conducive to data extracts in very large environments.

You may also want to consider looking at SMS Extract for Access and/or Excel. I believe it's available on the SMS 2 CD.


snyderj

 

[snyderj]

One more item that may be of interest:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sms/refsms22_3lir.asp

 

[DreamBroker]

kewl, thx a lot