SMSUG.ca

...your virtual communITy
Welcome to SMSUG.ca Sign in | Join | Help
in Search

Garth Jones

October 2012 - Posts

  • SCCM 2012 Table has multiple records for Server IP

    For full details see

    http://social.technet.microsoft.com/Forums/en-US/configmanagergeneral/thread/43686ca9-3064-4533-8f94-f28c889b4511/

     

    HS view are for History data, not current data.

    RA views are discovery data views.

    vSMS are “extra” views.. I don’t generally use them.

     

    It is generally better to limit the use of NOT LIKE statements

    Here is the clean up

    SELECT 
        R.ResourceID AS 'ResourceID', 
        R.Netbios_Name0 AS 'Name', 
        R.AD_Site_Name0 AS 'Site', 
        NAC.IPAddress0 AS 'IP Address', 
        OS.Caption0 AS 'Operating System', 
        OS.CSDVersion0 AS 'Service Pack', 
        OS.Version0 AS 'Version', 
        R.Is_Virtual_Machine0 AS 'Virtual', 
        R.Resource_Domain_OR_Workgr0 AS 'Domain', 
        S.SystemRole0 AS 'System Type'
    FROM 
        dbo.v_R_System R
        JOIN dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION NAC ON R.ResourceID = NAC.ResourceID
        JOIN dbo.v_GS_OPERATING_SYSTEM OS ON R.ResourceID = OS.ResourceID
        JOIN dbo.v_GS_SYSTEM S ON R.ResourceID = S.ResourceID 
    WHERE 
        NAC.IPAddress0 NOT LIKE 'fe80%' 
        AND S.SystemRole0 = 'Server'
    Posted Oct 23 2012, 05:03 PM by Garth with no comments
    Filed under: , , , ,
  • PC by Gateway

     

    select
        CS.Name0,
        CS.UserName0,
        NA.Description0,
        NAC.IPAddress0,
        NAC.MACAddress0,
        NAC.DefaultIPGateway0
    From
        dbo.v_GS_COMPUTER_SYSTEM CS
        join dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC on NAC.ResourceID = CS.ResourceID
        join dbo.v_GS_NETWORK_ADAPTER NA on NA.ResourceID = CS.ResourceID and NAC.ServiceName0 = NA.ServiceName0
    Where
        NAC.DefaultIPGateway0 = '192.168.1.1'
        or NAC.DefaultIPGateway0 = '192.168.5.1'
    Posted Oct 18 2012, 01:39 PM by Garth with no comments
    Filed under: , , ,
  • Software by OU

    SELECT 
        ARP.DisplayName0, 
        Count(*) AS 'Count', 
        ARP.Publisher0, 
        ARP.Version0
    FROM 
        dbo.v_Add_Remove_Programs ARP
        join dbo.v_RA_System_SystemOUName OU on OU.ResourceID = ARP.ResourceID
    WHERE 
        OU.System_OU_Name0 = @OU
    GROUP BY 
        ARP.DisplayName0, 
        ARP.Publisher0, 
        ARP.Version0 
    ORDER BY 
        ARP.Publisher0, 
        ARP.Version0
  • IE7 Collection

    For full detail please see forum post

     

    select 
        *  
    from  
        SMS_R_System 
        left join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId 
        left join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId 
    where 
        SMS_G_System_ADD_REMOVE_PROGRAMS.ProdID = "ie7" 
        or SMS_G_System_ADD_REMOVE_PROGRAMS_64.ProdID = "ie7"
  • List of PC by Excel versions

    For full detail see forum post.

    http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/66b71887-acc7-40ac-8634-536784fa5adf

    SELECT DISTINCT 
        R.Name0 AS 'Machine Name', 
        left(SF.FileVersion,2)
     FROM 
        dbo.v_R_System R
        INNER JOIN dbo.v_GS_SoftwareFile SF ON R.ResourceID = SF.ResourceID
        Join dbo.v_GS_SYSTEM S ON R.ResourceID = S.ResourceID
     WHERE
        S.SystemRole0 = 'Workstation'
        AND SF.FileName = 'excel.exe'
        AND SF.FilePath LIKE 'C:\Program Files%'
     ORDER BY 
        R.Name0
    .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

This Blog

Syndication

News

Training

Powered by Community Server (Non-Commercial Edition), by Telligent Systems