Tuesday, October 17, 2017

The moment I hit Refresh



There comes a time in everyone’s life in when they need to “Hit Refresh”.  My father never graduated college but rose to a VP of Engineering by taking courses and surrounding himself with people willing to teach him and allow him to learn.  He ended up holding 4 patents in electronics and the medical field.  I enjoyed watching him learn circuit design and homebrew computers.  I was born in 1975 and by 1980 we had a TRS-80 Model III.  By 1984 I was programming in BASIC and by 1987 I was helping my father fix and sell computers.  Of a family of 6 we had one for each person and several more coming and going.  I was even beginning to write programs for people.  In 1997, I started my full-time career in IT working for a university, Windows 3.1, 95 and Netware over Token Ring and 10Base2.  By 2005, I had been fixing and programming computers for a long time but I needed a change, a challenge that would take me to the next stage.  I started on my Master’s degree in Computer Sciences and learning .NET/C#.  Then, I was contacted by an old co-worker that had changed jobs.  He was working for another education environment that was searching for someone to manage machines using SMS 2003.  I had never heard of SMS 2003 but I interviewed.  My friend knew I enjoyed a challenge and could master anything, having already taught me JavaScript, ASP and PERL.  My first day on the job I hit refresh and started a new path, managing machines from a central console.  I started on a path of installing SMS 2003 from the ground up! I found the world of Enterprise Management to be one of challenges, frustrations and how to properly manage machines.  As the only person managing SMS, it became necessary to learn every aspect and to perfect each area.  I began to create a blog and answer questions in the Microsoft Forums. I had mastered the software and wanted to help others.  I also wanted to learn more, enter the Microsoft Management Summit (MMS), but we didn’t have any funds for me to attend the conference.  I learned that speakers have their hotel and conference pass paid in full so I did the next best thing, I submitted a topic and was selected.  From there my life moved forward and I became a Microsoft MVP in 2009 and enjoy the award still.  People saw potential in my father and helped him grow, he and other IT professionals helped me grow, so I continue helping the next generation.  All it takes is someone to take an interest in your future. 

This post is part of the collection of #HitRefresh moments. Read more at www.hitrefreshbook.com

Tuesday, August 8, 2017

Storing Family data in the cloud


We are creating more and more data with the arrival of the digital camera and cell phone camera.  But what about all the pictures you have already. How do you save those photos and at the same time share them with your family?  You could use Facebook as place to share old family photos and gather information but Facebook isn't designed for long term storage, data retrieval and archiving of high quality photos.

 

Enter Azure and Blob Storage (https://azure.microsoft.com/en-us/services/storage/blobs/)

 

Put simply a Blob is unstructured data you store for retrieval via HTTP or HTTPS.  Data such as text files, pictures, or streaming files like Audio or Video. This data can be easily retrievable or stored for the long term.

 

Cool Storage - Files that you don't expect to access very often like a long term backup or possible the high resolution version of a Family Picture.

Hot Storage -File that you expect to use often and need readily available like a thumbnail or small 100k version of your high-resolution picture.

 

Understand that to make a hot file cool is free but if you want to thaw out your data for retrieval, there is a cost.

 

Now that we understand what a Blob is, how do we organize our data. 

 

Files are stored in Containers (https://docs.microsoft.com/en-us/azure/storage/storage-dotnet-how-to-use-blobs) like Folders in your My Documents.  Pictures, Videos or you could break it down such as Smith Family, Jones Family if you trying to save your videos from a family tree.

 

Now that we have talked about the basics.  Let's look at what we can do with Azure and why data storage retrieval can be easy and benificial to many people distributed around the world outside of the business area.

 

Problem: Person has tons of family photos but doesn't know the identities of the people.  Several family members have helped out but members are all over the globe and it would be nice to catalog the data so you can search as well as download a high-resolution version of a family picture. 

 

Solution: Azure Blob Storage, Azure SQL and possibly a Web Front end all in the Central Region

 

I will take a simplified view of the cost of what it might take to get it started:

In the future, we will walk through the process of setting it up, right now we talk about the cost of getting started.

 

Blob Storage Cost

https://azure.microsoft.com/en-us/pricing/details/storage/blobs/

Cool Storage - Geographically Redundant Storage (GRS) because we want our data separated and backed up

Hot Storage - Locally Redundant Storage (LRS), these are the thumbnails and preview files that are small and won't matter if lost.

(https://docs.microsoft.com/en-us/azure/storage/storage-blob-storage-tiers)

 

Recall - Your cold storage is the high-resolution files that you shouldn't be viewing often.

 

10 family containers

5 hot - .05

5 cool - .20

 

Retrieving the data

5 hot - free

5 cool - $0.01/GB

 

There is a cost to store and replicate your data for later use

Saving and Replicating the data

Hot - Free

Cool - $0.02

 

 Azure SQL is required for us to save the metadata for the files like location, who is in the pictures, etc

 

https://azure.microsoft.com/en-us/pricing/details/sql-database/

 

Databases under 2GB is almost free at $0.0068/hr of access.  Given this data is only access when you search or write and this should be relatively small.  Should it grow beyond 2GB then you start to see your cost increase.

 

Ideally the database will show a picture no more than 100kb. Videos and Audio will need to be compress to smaller file so you don't pay for previewing the data.

 

But how do we access the data?  You create your Azure database here (https://docs.microsoft.com/en-us/azure/sql-database/) add a specific IP address of your local machine and then you can create an MS Access front end to access your data.  Realize that searching and moving through will incur a cost. But you can also have a button to download the Cool Storage of the file.  When you perform this action, Cool Storage will be converted to Hot storage.

 

"Changing the storage tier: Changing the storage tier from cool to hot will incur a charge equal to reading all the data existing in the storage account for every transition. On the other hand, changing the storage tier from hot to cool will be free of cost."

  

 

 

 

Monday, July 24, 2017

O365 Client Management Dashboard

Have you every wondered why you only have 5 Collections in your dropdown? 


This is currently by design, 1702.  What you have is the top 5 largest collection, sorted alphabetically.  If you wish to look at a collection other than what is given you must use the Browse button. 




Friday, May 5, 2017

User Device Affinity (UDA) in C#

With the demise of Silver Light, you might want to keep it just so you can do UDA for the users.  Well here is some code you can use to create a webpage, much like I did to go around that issue.


Here is some straight code:

First you need to search for the user you want to add:
Create a Text box named UserSelected
Now search for the user based on the name, this will also contain their userID so it helps with the searches.


string strQuery = "Select * from SMS_R_User where Name like '%" + UserSelected.Trim() + "%'";
IResultObject UsersFound = SCCMconnection.QueryProcessor.ExecuteQuery(strQuery);


Now you can move through the list and add them to the radio button if you like.


foreach (IResultObject UserFound in UsersFound)
{
dt.Rows.Add(CreateRow(UserFound["UniqueUserName"].StringValue.Trim(), UserFound["Name"].StringValue.Trim(), dt));
dv = new DataView(dt);
}
UniqueUserName-  This is what SCCM needs in order to add the user domain\userid
UserFound["Name"] - this is used for our searches because it looks like this (userID\domain  Username ResourceID)
Next you can create a search for the machine,

SMS_CM_RES_COLL_XY000017 - This is your collection of machines you wish to search, this could be your AllWorkstations collection


Select ResourceID,Name from SMS_CM_RES_COLL_XY000017 where name like '%" + FindComputerTxtBox.Text.Trim() + "%'";


Once you find it add it to a drop down box. 
Now with both the user and the Machine we can set the UDA.  Be sure your Security on the Service Account performing the action has access to set User Device Affinity.

Dictionary<string, object> addUDAParameters = new Dictionary<string, object>();
addUDAParameters.Add("MachineResourceId", ResourceIDVariable);
addUDAParameters.Add("UserAccountName", "domain\userid";
addUDAParameters.Add("SourceId", "2");  //Administrative change
addUDAParameters.Add("TypeId", "1");
IResultObject UDA = SCCMconnection.ExecuteMethod("SMS_UserMachineRelationship", "CreateRelationship", addMembershipRuleParameters);




Depending on what you use the Application Catalog for, you can use this code on a website and remove SilverLight.

Tuesday, May 2, 2017

What is Managed Mobile Convergence and why is it important to my business?



Managed Mobile Convergence is the ability to manage a mobile device, desktops or even servers as the same machine.  Think of as treating everyone the same not matter what!!  Let’s outline some definitions because as the lines blur so do the meaning.
Mobile Device – traditionally we would refer to these as smart devices like smart phones but now see laptops and tablets slowly move into this area.
IoT Devices – Internet of Things- We use to call these embedded devices.  They were sometimes dumb devices that might have linked to a central control, had a small storage and ran PIC but now the name has changed and so have the usability.  These devices are as smart as Mobile devices and sometimes run a modern OS like Windows 10 or Linux. 
Workstations / Desktops – Machines we use for our daily work and play.  These devices use a full OS like Apple, Linux, Windows and are work hard.  These machines are now blurring between our work pc and the home pc with the Bring Your Own Device initiative.
Now what is left out of all this conversation are servers.  Servers have moved from closets to rooms to “server room” and now to the Data Center.  In all our management views, we traditionally leave server management up to the Server admins.  Even today Servers are treated differently that other devices. 
Intune currently doesn’t support Server but it has slowly provided more modern management of the mobile workstations like Win10 devices and can also manage the desktop for smaller organizations. This is a great idea but be aware that part of this solution is no reimage, yet.  Now we have Win10 provisioning, MDT and other solutions.  Using this perspective MS is looking to move you to the Cloud, “Modern Management” and with no more on-prem management infrastructure like SCCM.  This can save money but you still need to manage your server environments. 


https://twitter.com/msintune
I see your “Modern Management” and raise you “Managed Mobile Convergence”.  The ability to manage all devices globally, without prejudice.  Smart phone, Workstation, Laptop, BYOD, Tablet, Server as well as Operating System Agnostic all utilizing a single pane of glass.  To begin to see this world we must place a blinder on the use of the machine and look at it from the perspective as business device, nothing more.  If the device needs antivirus/antimalware it should be managed, even if that means Microsoft devices pull from MS and the Apple Devices pull from Apple, all the while talking to the same management infrastructure.  The machine policies should be the same even if the signatures are from different vendors.  I should have a single pane of glass to create an exemption for a virus alert, file type, etc.
We see this partly in SCCM with the Adroid for Work, Mac support, Servers and many different types of machines.  To truly reach our goal of Managed Mobile Convergence we must treat every device as equal.  That would mean if we want to deploy an update to an Apple, Workstation, Server, IoT device it should be a single solution with a single interface. 
I am only discussing the management of the machine. I am leaving out the trade compliance issue of data entering or leaving counties, client data in the cloud.  These problems will always exist and there for complicate the infrastructure to manage these devices.  For this reason we will always need a kind of hybrid approach for management.  That is a discussion for later….

Wednesday, February 22, 2017

BIOS Report for Dell and HP

When moving to Windows 10, you need to ensure that you have the BIOS up-to-date as to not have issues with UEFI or any other component.  Use these SQL statements to create your BIOS reports.


I am only trying to look at Workstation models and not servers.  HP was my second set of queries and it was easier to simply exclude the server models.  Use and modify as necessary.


HP
SELECT CompSys.Manufacturer0 as 'OEM', CompSys.model0 as 'Model',BIOS.SMBIOSBIOSVERSION0 as 'BIOSVersion', COUNT(Sys.Resourceid) AS 'TotalSystems'
 FROM V_R_System as Sys
 LEFT JOIN V_GS_PC_BIOS as BIOS on Sys.resourceid = BIOS.resourceid
 LEFT JOIN V_GS_COMPUTER_SYSTEM as CompSys on Sys.resourceid = CompSys.resourceid
 WHERE
 (CompSys.Manufacturer0 LIKE '%HP%'OR
  CompSys.Manufacturer0 LIKE '%Hewlett-Packard%') AND
 CompSys.model0 NOT like '%ProLiant%'
 GROUP BY CompSys.Manufacturer0,CompSys.model0, BIOS.SMBIOSBIOSVersion0
 ORDER BY CompSys.Model0, BIOS.SMBIOSBIOSVersion0




Dell
SELECT CompSys.Manufacturer0 as 'OEM', CompSys.model0 as 'Model',BIOS.SMBIOSBIOSVERSION0 as 'BIOSVersion', COUNT(Sys.Resourceid) AS 'TotalSystems'
 FROM V_R_System as Sys
 LEFT JOIN V_GS_PC_BIOS as BIOS on Sys.resourceid = BIOS.resourceid
 LEFT JOIN V_GS_COMPUTER_SYSTEM as CompSys on Sys.resourceid = CompSys.resourceid
 WHERE
 CompSys.Manufacturer0 LIKE '%dell%'
 AND (CompSys.Model0 LIKE '%optiplex%'
 OR CompSys.Model0 LIKE '%latitude%'
 OR CompSys.Model0 LIKE '%precision%')
 GROUP BY CompSys.Manufacturer0,CompSys.model0, BIOS.SMBIOSBIOSVersion0
 ORDER BY CompSys.Model0, BIOS.SMBIOSBIOSVersion0

Tuesday, February 21, 2017

Windows 10 Model Check Dell and HP

Dell (http://www.dell.com/support/article/us/en/4/SLN297954/computers-tested-for-upgrade-to-windows-10-anniversary-update?lang=EN) and HP (http://support.hp.com/us-en/document/c05195282) both have blogs on the Models that have been tested and support Windows 10.  Win10 can install to many devices.  These devices can take full advantage of Win10 (Secure boot, UEFI, Certified Drivers).


These SQL queries can be used to make pivot tables and other nice looking reports for SCCM depending on your need.  I did my best to add in all the Models.  Please check your models against these reports and the blogs and include or exclude them for your query as needed.




Note: HP Manufacturer is either HP or Hewlett-Packard


HP Models supporting Win10
SELECT CompSys.Manufacturer0 as 'OEM', CompSys.model0 as 'Model', COUNT(Sys.Resourceid) AS 'TotalSystems'
 FROM V_R_System as Sys
 LEFT JOIN V_GS_PC_BIOS as BIOS on Sys.resourceid = BIOS.resourceid
 LEFT JOIN V_GS_COMPUTER_SYSTEM as CompSys on Sys.resourceid = CompSys.resourceid
 WHERE

 (CompSys.model0  like '%EliteBook 850 G2%' or
 CompSys.model0  like '%EliteBook 750 G2%' or
 CompSys.model0  like '%EE 10 G1%' or
 CompSys.model0  like '%Elite x2 1011 G1%' Or
 CompSys.model0  like '%Elite x2 1012 G1 %' Or
 CompSys.model0  like '%EliteBook 1040 G2%' Or
 CompSys.model0  like '%EliteBook 1040 G4%' Or
 CompSys.model0  like '%EliteBook 725 G2%' Or
 CompSys.model0  like '%EliteBook 725 G3%' Or
 CompSys.model0  like '%EliteBook 725 G4%' Or
 CompSys.model0  like '%EliteBook 740 G1%' Or
 CompSys.model0  like '%EliteBook 740 G2%' Or
 CompSys.model0  like '%EliteBook 745 G2%' Or
 CompSys.model0  like '%EliteBook 745 G3%' Or
 CompSys.model0  like '%EliteBook 745 G4%' Or
 CompSys.model0  like '%EliteBook 750 G1 %' Or
 CompSys.model0  like '%EliteBook 755 G2%' Or
 CompSys.model0  like '%EliteBook 755 G3%' Or
 CompSys.model0  like '%EliteBook 755 G4%' Or
 CompSys.model0  like '%EliteBook 820 G3%' Or
 CompSys.model0  like '%EliteBook 820 G1%' Or
 CompSys.model0  like '%EliteBook 820 G4%' Or
 CompSys.model0  like '%EliteBook 720 G2%' Or
 CompSys.model0  like '%EliteBook 720 G1%' Or
 CompSys.model0  like '%EliteBook 820 G2%' Or
 CompSys.model0  like '%EliteBook 830 G1%' Or
 CompSys.model0  like '%EliteBook 840 G1%' Or
 CompSys.model0  like '%EliteBook 840 G2%' Or
 CompSys.model0  like '%EliteBook 840 G3%' Or
 CompSys.model0  like '%EliteBook 840 G4%' Or
 CompSys.model0  like '%EliteBook 850 G1%' Or
 CompSys.model0  like '%EliteBook 850 G3%' Or
 CompSys.model0  like '%EliteBook 850 G4%' Or
 CompSys.model0  like '%EliteBook Folio G1%' Or
 CompSys.model0  like '%EliteBook Folio 1030 G1%' Or
 CompSys.model0  like '%EliteBook Folio 1040 G3%' Or
 CompSys.model0  like '%EliteDesk 700 G1%' Or
 CompSys.model0  like '%EliteDesk 705 G1%' Or
 CompSys.model0  like '%EliteDesk 705 G2%' Or
 CompSys.model0  like '%EliteDesk 705 G3%' Or
 CompSys.model0  like '%EliteDesk 800 G1%' Or
 CompSys.model0  like '%EliteDesk 800 G2%' Or
 CompSys.model0  like '%EliteDesk 800 G3%' Or
 CompSys.model0  like '%EliteOne 705 G1%' Or
 CompSys.model0  like '%EliteOne 705 G2%' Or
 CompSys.model0  like '%EliteOne 800 G1%' Or
 CompSys.model0  like '%EliteOne 800 G2%' Or
 CompSys.model0  like '%ElitePad 1000 G2 %' Or
 CompSys.model0  like '%Folio 1020 G1%' Or
 CompSys.model0  like '%Folio 1040 G1%' Or
 CompSys.model0  like '%Folio 9480m%' Or
 CompSys.model0  like '%HP 200 G1%' Or
 CompSys.model0  like '%HP 202 G2%' Or
 CompSys.model0  like '%HP 205 G3%' Or
 CompSys.model0  like '%HP 240 G4%' Or
 CompSys.model0  like '%HP 240 G5%' Or
 CompSys.model0  like '%HP 245 G4%' Or
 CompSys.model0  like '%HP 245 G5%' Or
CompSys.model0  like '%HP 248 G1%' Or
CompSys.model0  like '%HP 250 G4%' Or
CompSys.model0  like '%HP 250 G5%' Or
CompSys.model0  like '%HP 255 G4%' Or
CompSys.model0  like '%HP 255 G5%' Or
CompSys.model0  like '%HP 260 G1%' Or
CompSys.model0  like '%HP 260 G2%' Or
CompSys.model0  like '%HP 280 G1%' Or
CompSys.model0  like '%HP 280 G2%' Or
CompSys.model0  like '%HP 285 G1%' Or
CompSys.model0  like '%HP 285 G2%' Or
CompSys.model0  like '%HP 340 G1%' Or
CompSys.model0  like '%HP 340 G2%' Or
CompSys.model0  like '%HP 340 G3%' Or
CompSys.model0  like '%HP 340 G4%' Or
CompSys.model0  like '%HP 345 G2%' Or
CompSys.model0  like '%HP 346 G3%' Or
CompSys.model0  like '%HP 348 G3%' Or
CompSys.model0  like '%HP 350 G1%' Or
CompSys.model0  like '%HP 350 G2%' Or
CompSys.model0  like '%HP 355 G2%' Or
CompSys.model0  like '%HP 402 G1%' Or
CompSys.model0  like '%HP 406%' Or
CompSys.model0  like '%Elite x2 1012 G2 %' Or
CompSys.model0  like '%HP ENVY Pro Curve%' Or
CompSys.model0  like '%Omen Pro Mobile%' Or
CompSys.model0  like '%x2 210 G1%' Or
CompSys.model0  like '%x2 210 G2%' Or
CompSys.model0  like '%Z1 G2 Workstation%' Or
CompSys.model0  like '%Z230 Workstation%' Or
CompSys.model0  like '%Z240 Workstation%' Or
CompSys.model0  like '%Z440 Workstation%' Or
CompSys.model0  like '%Z640 Workstation%' Or
CompSys.model0  like '%Z840 Workstation%' Or
CompSys.model0  like '%MP9 G2 Retail%' Or
CompSys.model0  like '%Pro Tablet 608 G1%' Or
CompSys.model0  like '%Pro Tablet 612 G1%' Or
CompSys.model0  like '%Pro Tablet Retail%' Or
CompSys.model0  like '%Pro x2 612 G2%' Or
CompSys.model0  like '%ProBook 11 G1%' Or
CompSys.model0  like '%ProBook 11 G2%' Or
CompSys.model0  like '%ProBook 430 G1%' Or
CompSys.model0  like '%ProBook 430 G2%' Or
CompSys.model0  like '%ProBook 430 G3%' Or
CompSys.model0  like '%ProBook 430 G4%' Or
CompSys.model0  like '%ProBook 440%' Or
CompSys.model0  like '%ProBook 445%' Or
CompSys.model0  like '%ProBook 450%' Or
CompSys.model0  like '%ProBook 455%' Or
CompSys.model0  like '%ProBook 470%' Or
CompSys.model0  like '%ProBook 640 G1%' Or
CompSys.model0  like '%ProBook 640 G2%' Or
CompSys.model0  like '%ProBook 640 G3%' Or
CompSys.model0  like '%ProBook 645 G1 %' Or
CompSys.model0  like '%ProBook 645 G2%' Or
CompSys.model0  like '%ProBook 645 G3%' Or
CompSys.model0  like '%ProBook 650 G1%' Or
CompSys.model0  like '%ProBook 650 G2%' Or
CompSys.model0  like '%ProBook 650 G3%' Or
CompSys.model0  like '%ProBook 655 G1%' Or
CompSys.model0  like '%ProBook 655 G2%' Or
CompSys.model0  like '%ProBook 655 G3%' Or
CompSys.model0  like '%ProBook x360 11%' Or
CompSys.model0  like '%ProDesk 400 G1%' Or
CompSys.model0  like '%ProDesk 400 G2%' Or
CompSys.model0  like '%ProDesk 400 G3%' Or
CompSys.model0  like '%ProDesk 400 G4%' Or
CompSys.model0  like '%ProDesk 405 G1%' Or
CompSys.model0  like '%ProDesk 405 G2%' Or
CompSys.model0  like '%ProDesk 480 G1%' Or
CompSys.model0  like '%ProDesk 480 G2%' Or
CompSys.model0  like '%ProDesk 480 G3%' Or
CompSys.model0  like '%ProDesk 490 G1%' Or
CompSys.model0  like '%ProDesk 490 G2%' Or
CompSys.model0  like '%ProDesk 490 G3%' Or
CompSys.model0  like '%ProDesk 600 G1 %' Or
CompSys.model0  like '%ProDesk 600 G2%' Or
CompSys.model0  like '%ProDesk 600 G3%' Or
CompSys.model0  like '%ProOne 400 G1%' Or
CompSys.model0  like '%ProOne 400 G2%' Or
CompSys.model0  like '%ProOne 600 G1%' Or
CompSys.model0  like '%ProOne 600 G2%' Or
CompSys.model0  like '%ProOne 600 G3%' Or
CompSys.model0  like '%Revolve 810 G2%' Or
CompSys.model0  like '%Revolve 810 G3%' Or
CompSys.model0  like '%RP2, Model 2000%' Or
CompSys.model0  like '%RP2, Model 2030%' Or
CompSys.model0  like '%RP5, Model 5810%' Or
CompSys.model0  like '%RP9, Model 9015  G1%' Or
CompSys.model0  like '%RP9, Model 9018  G1%' Or
CompSys.model0  like '%Spectre 13%' Or
CompSys.model0  like '%Spectre Pro 13 G1%' Or
CompSys.model0  like '%Spectre x2 13%' Or
CompSys.model0  like '%Spectre x360 G1%' Or
CompSys.model0  like '%Stream 11 G3%' Or
CompSys.model0  like '%Tablet 408 G1%' Or
CompSys.model0  like '%TX1 POS 200%' Or
CompSys.model0  like '%Ultrabook 210 G1%' Or
CompSys.model0  like '%Ultrabook 215 G1%' Or
CompSys.model0  like '%x360 310 G2%' Or
CompSys.model0  like '%ZBook 14 G1%' Or
CompSys.model0  like '%ZBook 14 G2%' Or
CompSys.model0  like '%ZBook 14 G3%' Or
CompSys.model0  like '%ZBook 15 G1%' Or
CompSys.model0  like '%ZBook 15 G2%' Or
CompSys.model0  like '%ZBook 15 G3%' Or
CompSys.model0  like '%ZBook 15u G2%' Or
CompSys.model0  like '%ZBook 15u G3%' Or
CompSys.model0  like '%ZBook 17 G1%' Or
CompSys.model0  like '%ZBook 17 G2%' Or
CompSys.model0  like '%ZBook 17 G3%' )and
( CompSys.Manufacturer0 LIKE '%HP%'OR
  CompSys.Manufacturer0 LIKE '%Hewlett-Packard%')
 GROUP BY CompSys.Manufacturer0,CompSys.model0
 ORDER BY CompSys.Model0




HP Models NOT supporting Win10
SELECT CompSys.Manufacturer0 as 'OEM', CompSys.model0 as 'Model', COUNT(Sys.Resourceid) AS 'TotalSystems'
 FROM V_R_System as Sys
 LEFT JOIN V_GS_PC_BIOS as BIOS on Sys.resourceid = BIOS.resourceid
 LEFT JOIN V_GS_COMPUTER_SYSTEM as CompSys on Sys.resourceid = CompSys.resourceid
 WHERE  (CompSys.Manufacturer0 LIKE '%HP%'OR
  CompSys.Manufacturer0 LIKE '%Hewlett-Packard%') AND CompSys.model0 not like '%ProLiant%' and Sys.ResourceID Not in
 (SELECT Sys.ResourceID
 FROM V_R_System as Sys
 LEFT JOIN V_GS_PC_BIOS as BIOS on Sys.resourceid = BIOS.resourceid
 LEFT JOIN V_GS_COMPUTER_SYSTEM as CompSys on Sys.resourceid = CompSys.resourceid
 WHERE

 (CompSys.model0  like '%EliteBook 850 G2%' or
 CompSys.model0  like '%EliteBook 750 G2%' or
 CompSys.model0  like '%EE 10 G1%' or
 CompSys.model0  like '%Elite x2 1011 G1%' Or
 CompSys.model0  like '%Elite x2 1012 G1 %' Or
 CompSys.model0  like '%EliteBook 1040 G2%' Or
 CompSys.model0  like '%EliteBook 1040 G4%' Or
 CompSys.model0  like '%EliteBook 725 G2%' Or
 CompSys.model0  like '%EliteBook 725 G3%' Or
 CompSys.model0  like '%EliteBook 725 G4%' Or
 CompSys.model0  like '%EliteBook 740 G1%' Or
 CompSys.model0  like '%EliteBook 740 G2%' Or
 CompSys.model0  like '%EliteBook 745 G2%' Or
 CompSys.model0  like '%EliteBook 745 G3%' Or
 CompSys.model0  like '%EliteBook 745 G4%' Or
 CompSys.model0  like '%EliteBook 750 G1 %' Or
 CompSys.model0  like '%EliteBook 755 G2%' Or
 CompSys.model0  like '%EliteBook 755 G3%' Or
 CompSys.model0  like '%EliteBook 755 G4%' Or
 CompSys.model0  like '%EliteBook 820 G3%' Or
 CompSys.model0  like '%EliteBook 820 G1%' Or
 CompSys.model0  like '%EliteBook 820 G4%' Or
 CompSys.model0  like '%EliteBook 720 G2%' Or
 CompSys.model0  like '%EliteBook 720 G1%' Or
 CompSys.model0  like '%EliteBook 820 G2%' Or
 CompSys.model0  like '%EliteBook 830 G1%' Or
 CompSys.model0  like '%EliteBook 840 G1%' Or
 CompSys.model0  like '%EliteBook 840 G2%' Or
 CompSys.model0  like '%EliteBook 840 G3%' Or
 CompSys.model0  like '%EliteBook 840 G4%' Or
 CompSys.model0  like '%EliteBook 850 G1%' Or
 CompSys.model0  like '%EliteBook 850 G3%' Or
 CompSys.model0  like '%EliteBook 850 G4%' Or
 CompSys.model0  like '%EliteBook Folio G1%' Or
 CompSys.model0  like '%EliteBook Folio 1030 G1%' Or
 CompSys.model0  like '%EliteBook Folio 1040 G3%' Or
 CompSys.model0  like '%EliteDesk 700 G1%' Or
 CompSys.model0  like '%EliteDesk 705 G1%' Or
 CompSys.model0  like '%EliteDesk 705 G2%' Or
 CompSys.model0  like '%EliteDesk 705 G3%' Or
 CompSys.model0  like '%EliteDesk 800 G1%' Or
 CompSys.model0  like '%EliteDesk 800 G2%' Or
 CompSys.model0  like '%EliteDesk 800 G3%' Or
 CompSys.model0  like '%EliteOne 705 G1%' Or
 CompSys.model0  like '%EliteOne 705 G2%' Or
 CompSys.model0  like '%EliteOne 800 G1%' Or
 CompSys.model0  like '%EliteOne 800 G2%' Or
 CompSys.model0  like '%ElitePad 1000 G2 %' Or
 CompSys.model0  like '%Folio 1020 G1%' Or
 CompSys.model0  like '%Folio 1040 G1%' Or
 CompSys.model0  like '%Folio 9480m%' Or
 CompSys.model0  like '%HP 200 G1%' Or
 CompSys.model0  like '%HP 202 G2%' Or
 CompSys.model0  like '%HP 205 G3%' Or
 CompSys.model0  like '%HP 240 G4%' Or
 CompSys.model0  like '%HP 240 G5%' Or
 CompSys.model0  like '%HP 245 G4%' Or
 CompSys.model0  like '%HP 245 G5%' Or
CompSys.model0  like '%HP 248 G1%' Or
CompSys.model0  like '%HP 250 G4%' Or
CompSys.model0  like '%HP 250 G5%' Or
CompSys.model0  like '%HP 255 G4%' Or
CompSys.model0  like '%HP 255 G5%' Or
CompSys.model0  like '%HP 260 G1%' Or
CompSys.model0  like '%HP 260 G2%' Or
CompSys.model0  like '%HP 280 G1%' Or
CompSys.model0  like '%HP 280 G2%' Or
CompSys.model0  like '%HP 285 G1%' Or
CompSys.model0  like '%HP 285 G2%' Or
CompSys.model0  like '%HP 340 G1%' Or
CompSys.model0  like '%HP 340 G2%' Or
CompSys.model0  like '%HP 340 G3%' Or
CompSys.model0  like '%HP 340 G4%' Or
CompSys.model0  like '%HP 345 G2%' Or
CompSys.model0  like '%HP 346 G3%' Or
CompSys.model0  like '%HP 348 G3%' Or
CompSys.model0  like '%HP 350 G1%' Or
CompSys.model0  like '%HP 350 G2%' Or
CompSys.model0  like '%HP 355 G2%' Or
CompSys.model0  like '%HP 402 G1%' Or
CompSys.model0  like '%HP 406%' Or
CompSys.model0  like '%Elite x2 1012 G2 %' Or
CompSys.model0  like '%HP ENVY Pro Curve%' Or
CompSys.model0  like '%Omen Pro Mobile%' Or
CompSys.model0  like '%x2 210 G1%' Or
CompSys.model0  like '%x2 210 G2%' Or
CompSys.model0  like '%Z1 G2 Workstation %' Or
CompSys.model0  like '%Z230 Workstation%' Or
CompSys.model0  like '%Z240 Workstation%' Or
CompSys.model0  like '%Z440 Workstation%' Or
CompSys.model0  like '%Z640 Workstation%' Or
CompSys.model0  like '%Z840 Workstation%' Or
CompSys.model0  like '%MP9 G2 Retail%' Or
CompSys.model0  like '%Pro Tablet 608 G1%' Or
CompSys.model0  like '%Pro Tablet 612 G1%' Or
CompSys.model0  like '%Pro Tablet Retail%' Or
CompSys.model0  like '%Pro x2 612 G2%' Or
CompSys.model0  like '%ProBook 11 G1%' Or
CompSys.model0  like '%ProBook 11 G2%' Or
CompSys.model0  like '%ProBook 430 G1%' Or
CompSys.model0  like '%ProBook 430 G2%' Or
CompSys.model0  like '%ProBook 430 G3%' Or
CompSys.model0  like '%ProBook 430 G4%' Or
CompSys.model0  like '%ProBook 440%' Or
CompSys.model0  like '%ProBook 445%' Or
CompSys.model0  like '%ProBook 450%' Or
CompSys.model0  like '%ProBook 455%' Or
CompSys.model0  like '%ProBook 470%' Or
CompSys.model0  like '%ProBook 640 G1%' Or
CompSys.model0  like '%ProBook 640 G2%' Or
CompSys.model0  like '%ProBook 640 G3%' Or
CompSys.model0  like '%ProBook 645 G1 %' Or
CompSys.model0  like '%ProBook 645 G2%' Or
CompSys.model0  like '%ProBook 645 G3%' Or
CompSys.model0  like '%ProBook 650 G1%' Or
CompSys.model0  like '%ProBook 650 G2%' Or
CompSys.model0  like '%ProBook 650 G3%' Or
CompSys.model0  like '%ProBook 655 G1%' Or
CompSys.model0  like '%ProBook 655 G2%' Or
CompSys.model0  like '%ProBook 655 G3%' Or
CompSys.model0  like '%ProBook x360 11%' Or
CompSys.model0  like '%ProDesk 400 G1%' Or
CompSys.model0  like '%ProDesk 400 G2%' Or
CompSys.model0  like '%ProDesk 400 G3%' Or
CompSys.model0  like '%ProDesk 400 G4%' Or
CompSys.model0  like '%ProDesk 405 G1%' Or
CompSys.model0  like '%ProDesk 405 G2%' Or
CompSys.model0  like '%ProDesk 480 G1%' Or
CompSys.model0  like '%ProDesk 480 G2%' Or
CompSys.model0  like '%ProDesk 480 G3%' Or
CompSys.model0  like '%ProDesk 490 G1%' Or
CompSys.model0  like '%ProDesk 490 G2%' Or
CompSys.model0  like '%ProDesk 490 G3%' Or
CompSys.model0  like '%ProDesk 600 G1 %' Or
CompSys.model0  like '%ProDesk 600 G2%' Or
CompSys.model0  like '%ProDesk 600 G3%' Or
CompSys.model0  like '%ProOne 400 G1%' Or
CompSys.model0  like '%ProOne 400 G2%' Or
CompSys.model0  like '%ProOne 600 G1%' Or
CompSys.model0  like '%ProOne 600 G2%' Or
CompSys.model0  like '%ProOne 600 G3%' Or
CompSys.model0  like '%Revolve 810 G2%' Or
CompSys.model0  like '%Revolve 810 G3%' Or
CompSys.model0  like '%RP2, Model 2000%' Or
CompSys.model0  like '%RP2, Model 2030%' Or
CompSys.model0  like '%RP5, Model 5810%' Or
CompSys.model0  like '%RP9, Model 9015  G1%' Or
CompSys.model0  like '%RP9, Model 9018  G1%' Or
CompSys.model0  like '%Spectre 13%' Or
CompSys.model0  like '%Spectre Pro 13 G1%' Or
CompSys.model0  like '%Spectre x2 13%' Or
CompSys.model0  like '%Spectre x360 G1%' Or
CompSys.model0  like '%Stream 11 G3%' Or
CompSys.model0  like '%Tablet 408 G1%' Or
CompSys.model0  like '%TX1 POS 200%' Or
CompSys.model0  like '%Ultrabook 210 G1%' Or
CompSys.model0  like '%Ultrabook 215 G1%' Or
CompSys.model0  like '%x360 310 G2%' Or
CompSys.model0  like '%ZBook 14 G1%' Or
CompSys.model0  like '%ZBook 14 G2%' Or
CompSys.model0  like '%ZBook 14 G3%' Or
CompSys.model0  like '%ZBook 15 G1%' Or
CompSys.model0  like '%ZBook 15 G2%' Or
CompSys.model0  like '%ZBook 15 G3%' Or
CompSys.model0  like '%ZBook 15u G2%' Or
CompSys.model0  like '%ZBook 15u G3%' Or
CompSys.model0  like '%ZBook 17 G1%' Or
CompSys.model0  like '%ZBook 17 G2%' Or
CompSys.model0  like '%ZBook 17 G3%' )
And
(CompSys.Manufacturer0 like '%HP%' or CompSys.Manufacturer0 like '%Hewlett-Packard%')
)



 GROUP BY CompSys.Manufacturer0,CompSys.model0
 ORDER BY CompSys.Model0






Dell machines supporting Windows 10
SELECT CompSys.Manufacturer0 as 'OEM', CompSys.model0 as 'Model', COUNT(Sys.Resourceid) AS 'TotalSystems'
 FROM V_R_System as Sys
 LEFT JOIN V_GS_PC_BIOS as BIOS on Sys.resourceid = BIOS.resourceid
 LEFT JOIN V_GS_COMPUTER_SYSTEM as CompSys on Sys.resourceid = CompSys.resourceid
 WHERE

 (CompSys.model0  like '%3150%' or
 CompSys.model0  like '%3160%' or
 CompSys.model0  like '%3330%' or
 CompSys.model0  like '%3340%' Or
 CompSys.model0  like '%3350%' Or
 CompSys.model0  like '%3379%' Or
 CompSys.model0  like '%3450%' Or
 CompSys.model0  like '%3460%' Or
 CompSys.model0  like '%3470%' Or
 CompSys.model0  like '%3540%' Or
 CompSys.model0  like '%3550%' Or
 CompSys.model0  like '%3560%' Or
 CompSys.model0  like '%3570%' Or
 CompSys.model0  like '%5175%' Or
 CompSys.model0  like '%5179%' Or
 CompSys.model0  like '%E5250%' Or
 CompSys.model0  like '%E5270%' Or
 CompSys.model0  like '%5404%' Or
 CompSys.model0  like '%5414%' Or
 CompSys.model0  like '%E5430%' Or
 CompSys.model0  like '%E5440%' Or
 CompSys.model0  like '%E5450%' Or
 CompSys.model0  like '%E5450%' Or
 CompSys.model0  like '%E5470%' Or
 CompSys.model0  like '%E5530%' Or
 CompSys.model0  like '%E5530%' Or
 CompSys.model0  like '%E5540%' Or
 CompSys.model0  like '%E5550%' Or
 CompSys.model0  like '%E5570%' Or
 CompSys.model0  like '%E6230%' Or
 CompSys.model0  like '%E6330%' Or
 CompSys.model0  like '%E6430%' Or
 CompSys.model0  like '%E6430%' Or
 CompSys.model0  like '%E6440%' Or
 CompSys.model0  like '%E6530%' Or
 CompSys.model0  like '%E6540%' Or
 CompSys.model0  like '%7202%' Or
 CompSys.model0  like '%7204%' Or
 CompSys.model0  like '%7214%' Or
 CompSys.model0  like '%E7240%' Or
 CompSys.model0  like '%E7250%' Or
 CompSys.model0  like '%E7270%' Or
 CompSys.model0  like '%7275%' Or
 CompSys.model0  like '%E7350%' Or
 CompSys.model0  like '%E7370%' Or
 CompSys.model0  like '%7404%' Or
 CompSys.model0  like '%7414%' Or
 CompSys.model0  like '%E7440%' Or
 CompSys.model0  like '%E7450%' Or
 CompSys.model0  like '%E7470%' Or
 CompSys.model0  like '%3010%' Or
 CompSys.model0  like '%3020%' Or
 CompSys.model0  like '%3030%' Or
 CompSys.model0  like '%3040%' Or
 CompSys.model0  like '%3046%' Or
 CompSys.model0  like '%3240%' Or
 CompSys.model0  like '%5040%' Or
 CompSys.model0  like '%7010%' Or
CompSys.model0  like '%7020%' Or
CompSys.model0  like '%7040%' Or
CompSys.model0  like '%9010%' Or
CompSys.model0  like '%9020%' Or
CompSys.model0  like '%9030%' Or
CompSys.model0  like '%3420%' Or
CompSys.model0  like '%3620%' Or
CompSys.model0  like '%5810%' Or
CompSys.model0  like '%7810%' Or
CompSys.model0  like '%7910%' Or
CompSys.model0  like '%7910%' Or
CompSys.model0  like '%3510%' Or
CompSys.model0  like '%M3800%' Or
CompSys.model0  like '%M4800%' Or
CompSys.model0  like '%5510%' Or
CompSys.model0  like '%M6800%' Or
CompSys.model0  like '%M7510%' Or
CompSys.model0  like '%M7710%' Or
CompSys.model0  like '%5055%' Or
CompSys.model0  like '%5056%' Or
CompSys.model0  like '%5855%' Or
CompSys.model0  like '%7140%' )and
CompSys.Manufacturer0 like '%Dell%'
 GROUP BY CompSys.Manufacturer0,CompSys.model0
 ORDER BY CompSys.Model0


Dell machines NOT supporting Windows 10
SELECT CompSys.Manufacturer0 as 'OEM', CompSys.model0 as 'Model', COUNT(Sys.Resourceid) AS 'TotalSystems'
 FROM V_R_System as Sys
 LEFT JOIN V_GS_PC_BIOS as BIOS on Sys.resourceid = BIOS.resourceid
 LEFT JOIN V_GS_COMPUTER_SYSTEM as CompSys on Sys.resourceid = CompSys.resourceid
 WHERE CompSys.Manufacturer0 like '%Dell%' and CompSys.model0 not like '%PowerEdge%' and Sys.ResourceID Not in
 (SELECT Sys.ResourceID
 FROM V_R_System as Sys
 LEFT JOIN V_GS_PC_BIOS as BIOS on Sys.resourceid = BIOS.resourceid
 LEFT JOIN V_GS_COMPUTER_SYSTEM as CompSys on Sys.resourceid = CompSys.resourceid
 WHERE

 (CompSys.model0  like '%3150%' or
 CompSys.model0  like '%3160%' or
 CompSys.model0  like '%3330%' or
 CompSys.model0  like '%3340%' Or
 CompSys.model0  like '%3350%' Or
 CompSys.model0  like '%3379%' Or
 CompSys.model0  like '%3450%' Or
 CompSys.model0  like '%3460%' Or
 CompSys.model0  like '%3470%' Or
 CompSys.model0  like '%3540%' Or
 CompSys.model0  like '%3550%' Or
 CompSys.model0  like '%3560%' Or
 CompSys.model0  like '%3570%' Or
 CompSys.model0  like '%5175%' Or
 CompSys.model0  like '%5179%' Or
 CompSys.model0  like '%E5250%' Or
 CompSys.model0  like '%E5270%' Or
 CompSys.model0  like '%5404%' Or
 CompSys.model0  like '%5414%' Or
 CompSys.model0  like '%E5430%' Or
 CompSys.model0  like '%E5440%' Or
 CompSys.model0  like '%E5450%' Or
 CompSys.model0  like '%E5450%' Or
 CompSys.model0  like '%E5470%' Or
 CompSys.model0  like '%E5530%' Or
 CompSys.model0  like '%E5530%' Or
 CompSys.model0  like '%E5540%' Or
 CompSys.model0  like '%E5550%' Or
 CompSys.model0  like '%E5570%' Or
 CompSys.model0  like '%E6230%' Or
 CompSys.model0  like '%E6330%' Or
 CompSys.model0  like '%E6430%' Or
 CompSys.model0  like '%E6430%' Or
 CompSys.model0  like '%E6440%' Or
 CompSys.model0  like '%E6530%' Or
 CompSys.model0  like '%E6540%' Or
 CompSys.model0  like '%7202%' Or
 CompSys.model0  like '%7204%' Or
 CompSys.model0  like '%7214%' Or
 CompSys.model0  like '%E7240%' Or
 CompSys.model0  like '%E7250%' Or
 CompSys.model0  like '%E7270%' Or
 CompSys.model0  like '%7275%' Or
 CompSys.model0  like '%E7350%' Or
 CompSys.model0  like '%E7370%' Or
 CompSys.model0  like '%7404%' Or
 CompSys.model0  like '%7414%' Or
 CompSys.model0  like '%E7440%' Or
 CompSys.model0  like '%E7450%' Or
 CompSys.model0  like '%E7470%' Or
 CompSys.model0  like '%3010%' Or
 CompSys.model0  like '%3020%' Or
 CompSys.model0  like '%3030%' Or
 CompSys.model0  like '%3040%' Or
 CompSys.model0  like '%3046%' Or
 CompSys.model0  like '%3240%' Or
 CompSys.model0  like '%5040%' Or
 CompSys.model0  like '%7010%' Or
CompSys.model0  like '%7020%' Or
CompSys.model0  like '%7040%' Or
CompSys.model0  like '%9010%' Or
CompSys.model0  like '%9020%' Or
CompSys.model0  like '%9030%' Or
CompSys.model0  like '%3420%' Or
CompSys.model0  like '%3620%' Or
CompSys.model0  like '%5810%' Or
CompSys.model0  like '%7810%' Or
CompSys.model0  like '%7910%' Or
CompSys.model0  like '%7910%' Or
CompSys.model0  like '%3510%' Or
CompSys.model0  like '%M3800%' Or
CompSys.model0  like '%M4800%' Or
CompSys.model0  like '%5510%' Or
CompSys.model0  like '%M6800%' Or
CompSys.model0  like '%M7510%' Or
CompSys.model0  like '%M7710%' Or
CompSys.model0  like '%5055%' Or
CompSys.model0  like '%5056%' Or
CompSys.model0  like '%5855%' Or
CompSys.model0  like '%7140%' )and
CompSys.Manufacturer0 like '%Dell%'
)

 GROUP BY CompSys.Manufacturer0,CompSys.model0
 ORDER BY CompSys.Model0

Monday, February 13, 2017

Software Updates that require multiple reboots

Ever have the problem of a software update installing only to find out that you need the next one and the next one.  By the time you are finished it has required several restarts. This can cause a problem for your users if they are restarting every day or left for the day and another patch restart occurred.





Now available in 1610, you can force the client to rescan and update if necessary.  This is not enabled by default when you upgrade.  You will need to revisit your Software Updates / Servicing Updates deployments and enable this.  Also, you will need to update your ADR so that all future updates will also have this feature installed. 


This is especially critical when a machine needs several office or .Net patches that require restarts before the next update can install.