How do I get device type information from SMS 2003

Posted on 2007-08-08
Last Modified: 2013-11-21

I would like to determine, by querying the SMS database, what type of device each item in the SYSTEM_DISC table is, i.e. workstation, server, router, etc.

Is this info available within the SMS database and if so, where do I look for it?

Question by:eoinmccarthy
    LVL 29

    Accepted Solution

    Assuming your referring to System Role?

    Here's a simple query based on All Systems

    select SMS_R_System.Name, SMS_G_System_SYSTEM.SystemRole, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.NetbiosName from  SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId


    Author Comment

    Neither SMS_R_System or SMS_G_System_SYSTEM exist on the sample SMS database that I'm working with.

    Author Comment

    Tried a cut-down version of query on views that looked like the ones described above:

    from  v_R_System
    inner join v_GS_System on v_GS_System.ResourceID = v_R_System.ResourceId

    This produced useful results - machine name and its role (just server or workstation in the sample data). It appears that v_R_System looks onto the System_DISC table and
    v_GS_System looks onto System_DATA table.   Not sure what either means but seem to be headed in the right direction.



    Featured Post

    Do email signature updates give you a headache?

    Do you feel like you are constantly making changes to email signatures? Are the images not formatting how you want them to? Want high-quality HTML signatures on all devices, including on mobiles and Macs? Then, let Exclaimer solve all your email signature problems today.

    Join & Write a Comment

    Consider a situation when you deploy a seemingly harmless software package to your network without testing and therefore without fully knowing the implications of your actions. I was recently involved in just this situation when a corporate IT netwo…
    Problem Description: Actually I found the below issue with some customers after migration from SMS 2003 to SCCM 2007 and epically if they change site code, some clients may appear in the console with old site code, plus old sites still appearing …
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now