WMI / WQL Easy Editor

This is a good tool https://wqlqueryrunner.codeplex.com/     Project DescriptionWQL Query Runner is (or would be) a tool for WMI SQL query testing.There are different types of WQL queries. My plan is to support creating all of them, and the first type for which I decided to add support are WQL event queries. WMI is … Read more

Patching Related End to end – Collections and reports :-

Below is for a compliance report based on SQL declare @CollectID AS  varchar(8)SET @CollectID= ‘SMS0001’ declare @CollectionListID AS  varchar(90)SET @CollectionListID=’ScopeId_5432f432-F885-4A98-B666-5432134122/AuthList_F15C63EA-B655-4940-A250-654323fd432′ declare @CI_ID int; select @CI_ID=CI_ID from v_ConfigurationItems where CIType_ID=9 and CI_UniqueID=@CollectionListID declare @CollCount int, @NumClients int; select @CollCount = count(*), @NumClients=isnull(sum(cast(IsClient as int)), 0) from v_ClientCollectionMembers ccm where ccm.CollectionID=@CollectID select     CollectionName=vc.Name,    ‘Update List’=al.Title,    Status=sn.StateName,    … Read more

WQL to SQL Query

WQL to SQL Want to see What is the query????????   This SQL Query can be used to see how WQL is converted to SQL by SMS for collections   Select Collections.SiteID , Collections.CollectionName , Collection_Rules.QueryName , Collection_Rules_SQL.WQL , Collection_Rules_SQL.SQL From Collections,Collection_Rules, Collection_Rules_SQL Where Collections.CollectionID = Collection_Rules.CollectionID AND Collections.CollectionID = Collection_Rules_SQL.CollectionID——————- Thanks,https://sccm07.blogspot.com/

Removing Distribution Points if not needed

If a server is decommissioned before removing SMS, orphaned Distribution Points are left in the SMS 2003 database. This becomes apparent in the SMS Administrator Console when trying to add a package to a Distribution Point, showing blank DP entries. Method 1 – Use the CleanDP.vbs VBScript cscript CleanDP.vbs %server% Method 2 – Use WMIC … Read more

Status Check for Wake On Lan

On Error Resume Next Const wbemFlagReturnImmediately = &h10Const wbemFlagForwardOnly = &h20 arrComputers = Array("ComputerName")For Each strComputer In arrComputers   WScript.Echo   WScript.Echo "=========================================="   WScript.Echo "Computer: " & strComputer    WScript.Echo "=========================================="    Set objWMIService = GetObject("winmgmts:" & strComputer & "rootWMI")   Set colItems = objWMIService.ExecQuery("SELECT * FROM MSNdis_DeviceWakeOnMagicPacketOnly", "WQL", _                                           wbemFlagReturnImmediately + wbemFlagForwardOnly)    For Each objItem … Read more

WQL Query list reboot date timestamp X Days

WQL Query to list all of the machines that have a reboot date timestamp of more than x Day’s Select Sms_R_System.Name, Sms_G_System_Operating_System.LastBootUpTime From SMS_R_System Inner Join Sms_G_System_Operating_System On Sms_G_System_Operating_System.ResourceId = Sms_R_System.ResourceId Where DatePart(DD,Sms_G_System_Operating_System.LastBootUpTime) >= XEnjoy,Paddy

ALL SQL Queries

SQL Query To Retrieve Advanced Clients Assigned Site Code And Client Version   This SQL Query will allow you to list the assigned site code and client version numbers for your advanced client resources.   SQL Query:   Select  SD.Name0 'Machine Name', SC.SMS_Assigned_Sites0 'Assigned Site', SD.Client_Version0 Version From v_R_System SD   Join v_RA_System_SmsAssignedSites SC on … Read more