troubleshooting Question

SMS 2003 MMC Querie in SQL 2005

Avatar of alliedfusion
alliedfusionFlag for United Kingdom of Great Britain and Northern Ireland asked on
Web ServicesSSRS
2 Comments1 Solution569 ViewsLast Modified:
Using SMS 2003 querie dsigner, the following quiery has been created to return computer information.   The query runs fine using the SMS 2003 MMC console, however, it is slow.

I would like to run the query directlry against the SMS SQL 2005 database using SQL views.

I have created a view and copied and pasted the SQL code from SMS designer, however, I am unsure what the "SMS_G_System" table names etc should be changed to so that the query will work.

Is there a simple solution?


SELECT DISTINCT 
                      dbo.SMS_R_System.Name, dbo.SMS_R_System.IPAddresses, dbo.SMS_R_System.MACAddresses, 
                      dbo.SMS_R_System.OperatingSystemNameandVersion, dbo.SMS_R_System.ResourceDomainORWorkgroup, 
                      dbo.SMS_G_System_COMPUTER_SYSTEM.Model, dbo.SMS_G_System_X86_PC_MEMORY.TotalPhysicalMemory, 
                      dbo.SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, dbo.SMS_G_System_ADD_REMOVE_PROGRAMS.Version, 
                      dbo.SMS_G_System_PROCESSOR.ProcessorType, dbo.SMS_G_System_WORKSTATION_STATUS.LastHardwareScan AS Expr2, 
                      dbo.SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.DefaultIPGateway AS Expr1, dbo.SMS_G_System_PROCESSOR.Manufacturer, 
                      dbo.SMS_G_System_PROCESSOR.NormSpeed
FROM         dbo.SMS_R_System INNER JOIN
                      dbo.SMS_G_System_COMPUTER_SYSTEM ON dbo.SMS_G_System_COMPUTER_SYSTEM.ResourceID = dbo.SMS_R_System.ResourceId INNER JOIN
                      dbo.SMS_G_System_X86_PC_MEMORY ON dbo.SMS_G_System_X86_PC_MEMORY.ResourceID = dbo.SMS_R_System.ResourceId INNER JOIN
                      dbo.SMS_G_System_ADD_REMOVE_PROGRAMS ON 
                      dbo.SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = dbo.SMS_R_System.ResourceId INNER JOIN
                      dbo.SMS_G_System_PROCESSOR ON dbo.SMS_G_System_PROCESSOR.ResourceID = dbo.SMS_R_System.ResourceId INNER JOIN
                      dbo.SMS_G_System_WORKSTATION_STATUS ON 
                      dbo.SMS_G_System_WORKSTATION_STATUS.ResourceID = dbo.SMS_R_System.ResourceId INNER JOIN
                      dbo.SMS_G_System_NETWORK_ADAPTER_CONFIGURATION ON 
                      dbo.SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = dbo.SMS_R_System.ResourceId
ASKER CERTIFIED SOLUTION
alliedfusion

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros