October 2011 - Posts

Simple History query
Saturday, October 22, 2011 9:49 AM

Select distinct

    SD.Name0 'Machine Name', 

    HARP.DisplayName0 as '32bit',

    HARP.Version0,  

    HARP64.DisplayName0 as '64bit',

    HARP64.Version0,

    SD.User_Name0 'User Name'  

From

    v_R_System SD 

    Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID 

    Join v_Collection COL on FCM.CollectionID = COL.CollectionID 

    left outer join dbo.v_HS_ADD_REMOVE_PROGRAMS HARP on SD.ResourceID = HARP.ResourceID

    left outer join dbo.v_HS_ADD_REMOVE_PROGRAMS_64 HARP64 on SD.ResourceID = HARP64.ResourceID

Where

     COL.Name = 'All Systems' 

    and (HARP.DisplayName0 = 'Configuration Manager Client' or HARP64.DisplayName0 = 'Configuration Manager Client')

Order By 

    SD.User_Name0

by Garth Jones

Filed under: SMS, Code, SQL, SCCM, vNext

Two Software Titles
Wednesday, October 19, 2011 10:47 PM

SELECT  DISTINCT
    a.Netbios_Name0 AS 'Computer Name',
    CASE WHEN  ARP.OMSA IS NULL THEN 'N/A' ELSE ARP.OMSA END AS 'Dell OMSA Version',
    CASE WHEN  ARP.Equallogic IS NULL THEN 'N/A' ELSE ARP.Equallogic END AS 'Broadcom Version'
 FROM 
    v_R_System_Valid a
    INNER JOIN v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = a.ResourceID
    INNER JOIN v_FullCollectionMembership FCM on FCM.ResourceID = a.ResourceID
  
--INSTALLED SOFTWARE--
    LEFT OUTER JOIN (SELECT ResourceID, 
                MAX(CASE WHEN DisplayName0 ='Dell OpenManage Server Administrator' THEN Version0 ELSE 'Not Installed' END) AS 'OMSA',
                MAX(CASE WHEN DisplayName0 ='Dell Equallogic Host Integration Tools' THEN Version0 ELSE 'Not Installed' END) AS 'Equallogic'
            FROM 
                v_ADD_REMOVE_PROGRAMS 
            WHERE 
                    ProdID0 LIKE 'Microsoft SQL Server%' 
                    OR DisplayName0 = 'Dell OpenManage Server Administrator'
                    OR DisplayName0 = 'Dell Equallogic Host Integration Tools'
            GROUP BY 
                ResourceID) ARP ON ARP.ResourceID = a.ResourceID
 
 Where 
    FCM.CollectionID = 'SMS00001' 
 Order by 
    a.Netbios_Name0

by Garth Jones

Filed under: SMS, System Center, Code, SQL, SCCM

This is a test of the a code plug-in for live writer.
Sunday, October 02, 2011 10:45 PM

SELECT
    A.AdvertisementName, 
    A.ProgramName,
    R.Netbios_Name0 as Computername,
    SCUM.TopConsoleUser0 as MainUsername 
FROM 
    dbo.v_R_System_Valid R
    join dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM on R.ResourceID = SCUM.ResourceID
    join dbo.v_ClientAdvertisementStatus CAS on R.ResourceID = CAS.ResourceID
    join dbo.v_Advertisement A on A.AdvertisementID = CAS.AdvertisementID

.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; }

by Garth Jones

Advertisements, Programs and Main User of all PC
Saturday, October 01, 2011 10:16 AM

SELECT

A

.AdvertisementName,

A

.ProgramName,

R

.Netbios_Name0 as Computername,

SCUM

.TopConsoleUser0 as MainUsername FROM

dbo

.v_R_System_Valid R join dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM on R.ResourceID = SCUM.ResourceID join dbo.v_ClientAdvertisementStatus CAS on R.ResourceID = CAS.ResourceID join dbo.v_Advertisement A on A.AdvertisementID = CAS.AdvertisementID

by Garth Jones

Filed under: ConfigMGr, SMS, Code, SQL, SCCM, vNext