Jump to content


Established Members
  • Posts

  • Joined

  • Last visited

Everything posted by cfreeman21

  1. We have been challenged to get 98%+ on Servers and Workstations and we usually get 9x%. Challenges I am facing are Machines not checking, online, or Not active or not rebooting as they should. What are you all doing to get Patch Compliance as High as possible. Process, Collections, Deployments, etc?
  2. Okay so I have read this somewhere and the way I interpret this i want to make sure is correct. If I stand up a 2012R2 KMS Server and use the Windows Srv 2012R2 DataCtr/Std KMS for Windows 10 Key all Servers Standard/DataCtr/Enteprise and Windows Workstation Windows 10 Below will be activated by this KMS Server? All the KMS Keys at Microsoft VLSC with KMS are only for if you are setting up a machine with that OS and want it to be a KMS right? Next Question if I use 2012R2 will 2016 activate or would it be best to stand up a 2016 KMS? The way i understand this i would need to do the following: Stand-up 2012R2 & Use this Key Srv 2012R2 DataCtr/Std KMS for Windows 10 Add Office Keys 2010/2013/2016 And this will cover my servers and desktops... is a correct assumption?
  3. Windows 7 & Windows 10. Just capture image then install updates via SCCM Console after importing into images? I also want to patch Office and such.
  4. I know there have been changes in software updates by reading starting in 1606... How are you all patching your B&C Image using SCCM... I use SCCM to B&C not MDT... I do have MDT integrated into SCCM.
  5. I am shocked nobody else is looking for this feature... Where I used to work we had Dell and I was able to get this working, but Lenovo is a different animal. Nobody has a working "free" solution for this?
  6. I wanting to start building a home lab using Windows 10 & Hyper-V, but I don't want to have to rebuild my lab every 180/90 days. Does Microsoft not Offer free/cheap licenses for Home Labs? I am not a student, but am a Tech looking to further my skills. What do you all do about licensing and such? I do have a MSDN Subscription through my employer but did not know about usage of those downloads/keys?
  7. Hard Coded main Query: DECLARE @SUG varchar(50); DECLARE @COLID varchar(8); DECLARE @CI_ID int; SET @SUG = 'Software Updates | Servers | 2016' SET @COLID = 'A1200602' SELECT @CI_ID = CI_ID FROM CI_ConfigurationItems WHERE CIType_ID = 9 AND CI_UniqueID = (SELECT CI_UniqueID AS AuthListID FROM v_AuthListInfo WHERE Title = @SUG) SELECT Status = sn.StateName, StatusNumber = (CASE WHEN StateName = 'Compliant' THEN 1 WHEN StateName = 'Non-Compliant' THEN 3 ELSE 2 END), cs.NumberOfComputers, PercentOfComputers = CONVERT(float, ISNULL(cs.NumberOfComputers, 0) * 100.00) / ISNULL(NULLIF(cs.NumTotal, 0), 1) FROM (SELECT CI_ID, NumTotal, [0] = NumUnknown, [1] = NumPresent + NumNotApplicable, [2] = NumMissing FROM v_UpdateSummaryPerCollection WHERE CI_ID = @CI_ID AND CollectionID = @COLID) cnt UNPIVOT (NumberOfComputers FOR [Status] IN ([0], [1], [2])) cs LEFT JOIN v_StateNames sn ON sn.TopicType = 300 AND sn.StateID = cs.Status WHERE cs.NumberOfComputers > 0 ORDER BY StatusNumber Both Parameters are just Pulling Available Values Get Values from a query
  8. If I run report with parameters I get blank data, but I hard code them I get data I do not understand why this is happening.
  9. This is the data you want on the report? Are you currently pulling in Email address from Active Directory into SCCM? The display name that outlook uses should be the AD Display name right?
  10. If you put in the Name for Software Update Group and the Collection ID the Main query works I need help getting the parameter to work. Thanks! Main Query: declare @SUG VARCHAR(50); declare @COLID VARCHAR(8); declare @CI_ID int; select @CI_ID = CI_ID from CI_ConfigurationItems where CIType_ID=9 and CI_UniqueID=(select CI_UniqueID as AuthListID from v_AuthListInfo Where Title=@SUG) select Status=sn.StateName, StatusNumber=(CASE WHEN StateName='Compliant' THEN 1 WHEN StateName='Non-Compliant' THEN 3 ELSE 2 END), cs.NumberOfComputers, PercentOfComputers=convert(float, isnull(cs.NumberOfComputers, 0)*100.00) / isnull(nullif(cs.NumTotal, 0), 1) from (select CI_ID, NumTotal, [0]=NumUnknown, [1]=NumPresent+NumNotApplicable, [2]=NumMissing from v_UpdateSummaryPerCollection where CI_ID=@CI_ID and CollectionID=@COLID ) cnt unpivot (NumberOfComputers for [Status] in ([0], [1], [2])) cs left join v_StateNames sn on sn.TopicType=300 and sn.StateID=cs.Status where cs.NumberOfComputers>0 order by StatusNumber Query for SUG Paramter: SELECT CI_UniqueID, Title FROM v_AuthListInfo WHERE Title LIKE '%Compliance%' OR Title LIKE '%Software Updates%' ORDER BY Title Query for COLID Parameter: SELECT DISTINCT c.CollectionID, c.CollectionName FROM v_UpdateSummaryPerCollection c WHERE (CollectionName LIKE '%Patching Group%' OR c.CollectionName LIKE '%Compliance%' OR c.CollectionName LIKE '%Server Patching%' OR c.CollectionName LIKE '%Approved Patching%') ORDER BY c.CollectionName
  11. Yes Peter33, That is exactly what I wanted. Thanks!
  12. I am trying to get a collection and then member count for that collection and if referenced collections list those and give me those counts... I don't seem to be able to get the dependent collection count any sql gurus able to assist? SELECT c.Name AS [Collection Name], c.Comment AS [Collection Comment], c.CollectionID AS [Collection ID], c. [Membercount], cd.SourceCollectionID AS 'Collection Dependency', cc.name as CollectionDependencyName, CASE WHEN cd.relationshiptype = 1 THEN 'Limited To ' + c.name + ' (' + cd.SourceCollectionID + ')' WHEN cd.relationshiptype = 2 THEN 'Include ' + c.name + ' (' + cd.SourceCollectionID + ')' WHEN cd.relationshiptype = 3 THEN 'Exclude ' + c.name + ' (' + cd.SourceCollectionID + ')' END AS 'Type of Relationship', COUNT (cd.DependentcollectionID) AS [Dependent Count] FROM v_Collection c JOIN v_FullCollectionMembership fc ON c.CollectionID = fc.CollectionID JOIN vSMS_CollectionDependencies cd ON c.CollectionID = cd.DependentCollectionID JOIN v_Collection cc ON cc.CollectionID = cd.SourceCollectionID WHERE c.CollectionID = 'A1200601' GROUP BY c.Name, c.Comment, c.CollectionID, c.Membercount, cd.SourceCollectionID, cd.RelationshipType, cc.name ORDER BY c.Name
  13. Anyone have a working solution for Lenovo Warranty in SCCM weather it rights to DB or to Registry?
  14. Did you get this all ironed out? I am wanting to do something very similar. What ports did you open on each server and what ports did you open on the firewall?
  15. I changed the parameter @CollID from @ColID =join(Parameters!ColID.Value,",") to @ColID =Parameters!ColID.Value appears to be working now.
  16. I made this change... No Luck Sorry I am not following you here on exactly what you are saying to do. Can you show an example? On my main Dataset the 3 parameters I have setup (@ColID, @SQLInstanceTypes, @SQLVersions) each parameter is using an expression. @ColID =join(Parameters!ColID.Value,",") @SQLVersions =IIF(Parameters!SQLVersions.Value ="","%",Parameters!SQLVersions.Value) @SQLInstanceTypes =IIF(Parameters!SQLInstanceTypes.Value ="","%",Parameters!SQLInstanceTypes.Value) Are these the settings you want me to change?
  17. I have my main SQL Query: SET DATEFORMAT dmy DECLARE @TODAY smalldatetime = GETDATE() SELECT DISTINCT SYS.Name0 AS [Computer Name], CPU.Manufacturer0 AS [Manufacturer], CPU.Name0 AS [Name], CPU.NumberofLogicalProcessors0 AS [Logical CPU Count], CPU.NumberofCores0 AS [Number of Cores per CPU], CS.NumberofProcessors0 AS [Number of CPUs], CASE WHEN SYS.Is_Virtual_Machine0 = 1 THEN 'Virtual' ELSE 'Physcial' END AS [Server Type], RAM.TotalPhysicalMemory0 / 1024 / 1024 AS [Memory (GB)], SQL.InstanceName0 AS [Instance Name], CASE WHEN SQL.Version0 LIKE '8.%' THEN 'SQL 2000' WHEN SQL.Version0 LIKE '9.%' THEN 'SQL 2005' WHEN SQL.Version0 LIKE '10.%' THEN 'SQL 2008' WHEN SQL.Version0 LIKE '10.%' THEN 'SQL 2008 R2' WHEN SQL.Version0 LIKE '11.%' THEN 'SQL 2012' WHEN SQL.Version0 LIKE '12.%' THEN 'SQL 2014' WHEN SQL.Version0 LIKE '13.%' THEN 'SQL 2016' ELSE 'UnKnown' END AS [Version], SQL.Edition0 AS [Edition], SQL.InstanceType0 AS [Instance Type], SQL.TCPPort0 AS [SQL TCPPort] FROM v_R_System SYS INNER JOIN v_GS_Processor CPU ON SYS.ResourceID = CPU.ResourceID INNER JOIN v_GS_COMPUTER_SYSTEM CS ON SYS.ResourceID = CS.ResourceID INNER JOIN v_gs_x86_pc_memory RAM ON SYS.ResourceID = RAM.ResourceID INNER JOIN v_GS_SQLINSTANCE SQL ON SYS.ResourceID = SQL.ResourceID INNER JOIN v_FullCollectionMembership FC ON SYS.ResourceID = FC.ResourceID INNER JOIN v_Collection VC ON FC.CollectionID = VC.CollectionID WHERE FC.CollectionID LIKE @ColID AND SQL.InstanceType0 LIKE @SQLInstanceTypes AND CASE WHEN SQL.Version0 LIKE '8.%' THEN 'SQL 2000' WHEN SQL.Version0 LIKE '9.%' THEN 'SQL 2005' WHEN SQL.Version0 LIKE '10.%' THEN 'SQL 2008' WHEN SQL.Version0 LIKE '10.%' THEN 'SQL 2008 R2' WHEN SQL.Version0 LIKE '11.%' THEN 'SQL 2012' WHEN SQL.Version0 LIKE '12.%' THEN 'SQL 2014' WHEN SQL.Version0 LIKE '13.%' THEN 'SQL 2016' ELSE 'UnKnown' END LIKE @SQLVersions Then I have a few secondary querries: All_Collections: SELECT DISTINCT c.CollectionID, c.Name FROM v_collection c WHERE c.CollectionType = 2 ORDER BY c.Name SQL_Instance_Type: SELECT DISTINCT SQL.InstanceType0 AS [Instance Type] FROM v_GS_SQLINSTANCE SQL UNION SELECT '' ORDER BY [Instance Type] SQL_Version: SELECT DISTINCT CASE WHEN SQL.Version0 LIKE '8.%' THEN 'SQL 2000' WHEN SQL.Version0 LIKE '9.%' THEN 'SQL 2005' WHEN SQL.Version0 LIKE '10.%' THEN 'SQL 2008' WHEN SQL.Version0 LIKE '10.%' THEN 'SQL 2008 R2' WHEN SQL.Version0 LIKE '11.%' THEN 'SQL 2012' WHEN SQL.Version0 LIKE '12.%' THEN 'SQL 2014' WHEN SQL.Version0 LIKE '13.%' THEN 'SQL 2016' ELSE 'UnKnown' END AS [Version] FROM v_GS_SQLINSTANCE SQL UNION SELECT '' ORDER BY [Version] Then of Course I have 3 parameters @ColID, @SQLInstanceTypes, @SQLVersions I do not know how to allow for multiple selections on the parameters for example I may want to choose multiple collections or SQL Version and so... I now within the parameter you can set to allow multiple but then I just get an error when choosing multiple. What is the proper way to allow multiple choices how do I need to update the parameter and the queries?
  18. I found this https://scottkeiffer.wordpress.com/2013/07/12/computer-warranty-info-formerly-dell-warranty-info/but I see he is creating a seperate database i thought that was frowned upon in SCCM World? thoughts?
  19. I am also looking for this... Does anyone have a working solution today that will pull warranty information for Lenovo into SCCM?
  • Create New...