Query

Suppose you have a column in Table1 that you need to update with data from another column in Table2. Let’s do it!

Someone asked me the other day below thing: “On the same column I want a Range of dates not Duration between two dates So I have a Creation date and fulfillment date I need to come up with a column of weekly Range like for example 10/21 – 10/27 10/28 – 11/01 11/02 – 11/09…

WMI queries to find 64 and 32 bit computers: SELECT * FROM Win32_OperatingSystem WHERE OSArchitecture = “64-bit” SELECT * FROM Win32_OperatingSystem WHERE OSArchitecture = “32-bit”

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.Version < “6.0.300” and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “Java(TM) 6%”

SELECT DISTINCT TOP (100) PERCENT DisplayName0, Version0, COUNT(Version0) AS Total FROM dbo.v_GS_ADD_REMOVE_PROGRAMS GROUP BY DisplayName0, Version0 HAVING (DisplayName0 LIKE ‘%Adobe Reader%’) ORDER BY Total DESC, DisplayName0

A customer asked me today to help him with creating some collections with client computers that do not have FEP 2010 installed so he can advertise the antivirus client to those computers. You can find the queries bellow. All Windows 7 without Forefront Endpoint Protection 2010 select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on…

SELECT TOP (100) PERCENT Manufacturer0 AS Manufacturer, Model0 AS Model, COUNT(Model0) AS Total FROM dbo.v_GS_COMPUTER_SYSTEM GROUP BY Model0, Manufacturer0 ORDER BY Total DESC, Manufacturer, Model

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_R_System.Client = 0 and SMS_G_System_OPERATING_SYSTEM.Caption not like “%Server%” or SMS_R_System.Client is null and SMS_G_System_OPERATING_SYSTEM.Caption not like “%Server%”

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Model like “%HP ProBook 4710s%”

Recently I reinstalled ConfigMgr server in my company and was looking for an easy method to upgrade all Configuration Manager clients to 4.00.6487.2000 version. Put some notes on the table and finally made the following plan: Create a report that counts all client versions. (This is optional, just for informational purposes). Report query is:SELECT TOP…