September 2011 - Posts

• Serial Number, User Information and installation dates of the software

  SELECT adv .AdvertisementName, stat .LastStateName, adv .Comment AS C072, pkg .Name AS C062, adv .ProgramName AS C071, adv .SourceSite, adv .AdvertisementID, se .SerialNumber0, cs .UserName0, Stat .LastAcceptanceStatusTime FROM v_Advertisement adv JOIN v_Package pkg ON adv.PackageID = pkg.PackageID JOIN v_ClientAdvertisementStatus stat ON stat.AdvertisementID = adv.AdvertisementID JOIN v_R_System sys ON stat.ResourceID = sys.ResourceID join dbo.v_GS_SYSTEM_ENCLOSURE se on se.ResourceID = sys.ResourceID join dbo.v_GS_COMPUTER_SYSTEM cs on cs.ResourceID = sys.ResourceID WHERE sys.Netbios_Name0 =@ComputerName Posted Sep 28 2011, 05:28 PM by Garth with no comments Filed under: SQL, SMS, Code, ConfigMGr, SCCM, vNext

• 10b–adding top console user

  if (@SoftwareID = '') set @SoftwareID = NULL; if (@Tag1Name = '') set @Tag1Name = NULL; if (@Tag2Name = '') set @Tag2Name = NULL; if (@Tag3Name = '') set @Tag3Name = NULL; select MEM .Netbios_Name0 [Computer Name], SCUM .TopConsoleUser0 as 'TCU', Soft .NormalizedName [Product Name], TG1 .TagName [Label 1], TG2 .TagName [Label 2], TG3 .TagName [Label 3] from v_GS_INSTALLED_SOFTWARE_CATEGORIZED Soft inner join (select v_R_System_Valid.ResourceID, v_R_System_Valid.Netbios_Name0 from v_R_System_Valid join v_FullCollectionMembership ON v_R_System_Valid.ResourceID = v_FullCollectionMembership.ResourceID where v_FullCollectionMembership.CollectionID = @CollectionID )MEM ON MEM.ResourceID = Soft.ResourceID inner join v_LU_SoftwareList_Local ls ON ls.SoftwareID = Soft.SoftwareID left join v_LU_Tags TG1 ON TG1.TagID = ls.Tag1ID left join v_LU_Tags TG2 ON TG2.TagID = ls.Tag2ID left join v_LU_Tags TG3 ON TG3.TagID = ls.Tag3ID join dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM ON SCUM.ResourceID = Soft.ResourceID where COALESCE (TG1.TagID, TG2.TagID, TG3.TagID) IS NOT NULL AND (@SoftwareID IS NULL OR Soft.SoftwareID = @SoftwareID) AND (@Tag1Name IS NULL OR TG1.TagName = @Tag1Name OR TG2.TagName = @Tag1Name OR TG3.TagName = @Tag1Name) AND (@Tag2Name IS NULL OR TG1.TagName = @Tag2Name OR TG2.TagName = @Tag2Name OR TG3.TagName = @Tag2Name) AND (@Tag3Name IS NULL OR TG1.TagName = @Tag3Name OR TG2.TagName = @Tag3Name OR TG3.TagName = @Tag3Name) order by MEM .Netbios_Name0, Soft .NormalizedName; Posted Sep 18 2011, 12:13 PM by Garth with no comments Filed under: SQL, Code, ConfigMGr

• Query to fetch the machines from a specific OU with the Specific Machine Model

  Select R .Name0 From dbo .v_R_System R join dbo.v_GS_COMPUTER_SYSTEM CS on R.ResourceID = CS.ResourceID join dbo.v_RA_System_SystemOUName OU on R.ResourceID = OU.ResourceID Where CS .Model0 = 'Virtual Machine' Group by R .Name0, CS .Model0 Having max(OU.System_OU_Name0) = 'GARTEK.TST/WORKSTATIONS' Posted Sep 13 2011, 06:41 AM by Garth with no comments Filed under: SQL, Code, System Center, ConfigMGr, SCCM

• MVA

  Have you seen Microsoft Virtual Academy (MVA): http://www.microsoftvirtualacademy.com/ ? Posted Sep 08 2011, 03:13 PM by Garth with no comments Filed under: Microsoft Canada, Microsoft