Useful Excel Tips

How to compare two columns in Excel

 

  1. Select the specific column
  2. Navigate to Home- Conditional Formatting
  3. Select New Rule
  4. Select the option ” Use a formula which cells to format
  5. Add the formula =countif($B:$B, $A1)
  6. Navigate to Format Cells and select Fill
  7. Select any colour to differentiate the results
  8. Now the matching values will have the selected colour
Advertisements

Useful Powershell commands for System Center Operations Manager

  1. Export Management Packs :

To Export all the available Management Packs

Export-SCOMManagementPack -Path “C:\MPArchive”

To Export the List to CSV

Get-SCOMManagementPack | Export-CSV C:\MP.csv

To Export a Specific Management Pack

Get-SCOMManagementPack -Name *ManagementPack Name* | Export-SCOMManagementPack -Path “C:\MPArchive”

To Export Monitors of a Specific Management Pack 

Get-SCOMManagementPack -Name *ManagementPack Name* | Get-SCOMMonitor | Export-csv spmonitor.csv

To Export Rules of a Specific Management Pack 

Get-SCOMManagementPack -Name *ManagementPack Name* | Get-SCOMRule | Export-csv sprule.csv

 

To get disabled discoveries in a Management Pack

Get-SCOMManagementPack -Name *sharepoint* | where-object {$_.sealed -eq $false} | export-csv disableddiscoveries.csv

 

To Get the critical errors in SCOM for a particular period

Get-SCOMAlert | Where-Object{$_.Timeraised -gt “5/13/2017”} | where-Object{$_.Severity -eq “error”} | measure | export-csv Warnings.csv

 

To get the list of all rules/Monitors from all Management Packs

Get-SCOMMonitor | select DisplayName, ManagementPackName, Enabled, ManagementGroup | Export-csv ConsolidatedMonitors.csv

To get the ManagementServer Name to which the Agent/Gateway Server reports to

Get-SCOMGatewayManagementServer |Where {$_.DisplayName -eq “GatewayServerFQDN”} |Get-SCOMParentManagementServer

Get-SCOMAgent |Where{$_.DisplayName -eq “AgentFQDN”} |Get-SCOMParentManagementServer

 

 

 

 

Enterprise Mobility and Security – Software updates with Windows Intune

What’s Changed in Enterprise Mobility Suite:

Enterprise Mobility Suite is renamed as Enterprise Mobility and Security. The existing enterprise Mobility Suite becomes Enterprise Mobility + Security E3 with no change for existing customers. A new upcoming plan will be known as Enterprise Mobility + Security E5.

Intune and its changes:

New Management Capabilities which includes Windows Updates, Windows Firewall and Endpoint protection

Azure AD Premium and its changes:

The existing Azure Active Directory Premium becomes Azure Active Directory Premium P1 with no change for existing customers

Azure Active Directory Premium P2 which will be available in coming days includes all the capabilities of Azure Active Directory Premium P1 as well as Identity Protection and Privileged Identity Management capabilities

Azure RMS and its Changes:

Azure Rights Management Premium becomes Azure Information Protection Premium P1 with no change in existing customers and Azure Information Protection Premium P2 adds advanced capabilities


Managing Windows with Microsoft Intune Client software:

Get a trial version of EMS here

Instead of enrolling windows PC as a mobile device, we can now enroll and manage windows PC’s by installing a client software. This has got the new management capabilities which supports Software updates, windows firewall and Endpoint protection

 

The following management capabilities are added with Intune client software:

  1. Application Management : Deploying Applications
  2. Endpoint protection : Managing and monitor malware attacks
  3. Windows Firewall : Configuring windows firewall settings
  4. Hardware and software inventory
  5. Remote control : Remote assistance request
  6. Software updates : Managing software updates

In this discussion, I am showcasing the software updates capabilities with Windows Intune Client software

  1. Download the client software

Intune Client software can be downloaded from here Or from the Intune Admin Console as shown below

Login to Intune portal at https://manage.microsoft.com

1.png

2.png

3.png

2. Enroll the windows Machine

Once the Intune Client software is downloaded and installed, the windows machine reports to Intune

4

 

We can check the status of the machine in company portal too  at https://portal.manage.microsoft.com

5

6.png

Now we can manage the updates for this Windows Machine with Intune

Software Updates in Windows Intune:

This feature is similar to the software update feature in System Center Configuration Manager where we can keep the windows Machines up to date with the latest software updates. These updates can be from Microsoft/non-Microsoft. When we enroll a Windows Machine in Intune with Intune Client software, that Machine reports to Intune wherein we can see the no of updates required, manage the updates by approving/declining, see the status of the installation and compliance.

A sample Intune Dashboard showing software updates

7.png

Different Types of Updates: There are 7 different types of updates available out of which some are mandatory updates which doesn’t prompt for approval

8.png


Microsoft vs Non-Microsoft Updates:

Software Updates by Microsoft:  Before we configure Microsoft updates, we have to configure product categories and update classifications

Navigate to Intune console – > Admin -> Updates where we can select the category and classification as per our requirement

9.png

39.PNG

 Now, as we selected the product category and update classification, all the updates are synchronized to Intune console

11.png

Automatic approval rules – These rules automatically approve specified types of update and reduce your administrative overhead. For example, you might want to automatically approve all critical software updates.

12.png

Update Software not made from Microsoft:

We can also update the software which is not from Microsoft. To achieve this, we have to upload the software through upload wizard which will be saved in the cloud storage and later we can approve/decline and deploy to the specific collection as we do for Microsoft updates


Deploying a sample Microsoft update to enrolled computer:

Now, we installed a Intune client software, enrolled a computer to Intune console, selected the product category and classification, synchronised the updates to Intune. Let us try deploying a security update to the enrolled computer.

The enrolled computer has 96 software updates that need approval

13.1.png

Select any update and approve it

14.png

Create a collection ( group ) and deploy the update to the collection

16.png

Select the approval settings. These are similar to the settings in System Center Configuration Manager

17.png

Select the deadline to install the update

18.png

Open Microsoft Intune Center ( This is similar to Software Center in System Center Configuration Manager ) in the client machine and check for updates

20

You can see that the updates are getting installed

Check for the updates installation in control panel

21.png


Deploying a sample Non – Microsoft update to enrolled computer:

We can even deploy Non-Microsoft Applications and updates with Intune by uploading the application/update to the Intune storage and then deploying to the specific collection or a group. In this case, I have chosen Google chrome as a Non-Microsoft application which is to be deployed to the enrolled computer. We can also try with Java updates as Non-Microsoft updates if Java is installed in the machine

Navigate to Intune console -> updates -> All Updates and click on upload

23.png

24

Specify the location of update setup file

25.png

26.png

This is quite interesting section. This will allow to select the architecture and Operating system so that we can have these filters at deployment level

27.png

This section will gives the system the ability to check if the update/application is already installed in the targeted machine. This will avoid the re installation of the same application and avoids the overriding of previous versions

28.png

In this section we can specify command line arguments for custom installation

29.png

30.png

31.png

32.png

33.png

Approve

34.png

Deploy to the collection ( group )

36.png

Select the approval settings

37.png

Open Microsoft Intune Center in the client machine and check for the updates.

13.PNG

Confirm the installation in Control Panel

38.PNG


Adding a Custom Domain to Office 365

Before proceeding to this topic, I would like to talk about the necessity of adding a custom domain to your Cloud ( Azure in this scenario )

By default, you have all the flexibility to choose your domain when you have On-Premise infrastructure. You install Active directory domain services, bring up a domain controller and choose a domain as per your convenience. So, all the identities which are created in that domain will carry the same domain name.

In Cloud, you will be asked to give the domain name during the registration process of Azure. For example, if you give ABC as your domain, if you create a new user as user1 in cloud, he has the extension of user1@ABC.onmicrosoft.com. Similarly User2@ABC.onmicrosoft.com and so on. It will be difficult for the organisation to have the identities as such. Instead, they can add their domain to Azure so that they can have User1@customdomain.com.  In this demo, I use a custom domain  kartikkopalle.com and a new user who is created will have the extension of user1@kartikkopalle.com


Prerequisites: 

  • A registered domain name
  • Access to O365 Portal. you can get a free Azure account from here

For this demonstration, I am using my custom domain kartikkopalle.com

Login to O365 Portal at https://login.microsoftonline.com

O365 Login page.PNG

Navigate to Domains and click on Add Domain

Domain.PNG

You will be directed to the below page. Get started

Add a new domain.PNG

Give your domain name

give domain name

Adding DNS records:

You will be shown with a TXT value. You have to go to the website, where you registered your domain and enter these values

Step by step procedure to add a TXT value : https://g.microsoftonline.com/0BX20en/949

vivek.PNG

After giving the TXT records, go back to O365 portal, select the domain and start the setup process.

21.PNG

1.PNG

Now, all the users will be prompted for an update to the new domain

2.PNG

Now you can see that the user kartik us updated from kartik@ML21.onmicrosoft.com   to kartik@kartikkopalle.com

3.PNG

Sign out and sign in with the new username

4.PNG

Now, when you create a new user in O365 portal, you will have an option to select the custom domain

user.PNG

login.PNG

Managing Office 365 Updates with SCCM 1603

Till SCCM 2012, if we have to manage the Microsoft Office updates, we have to manually download the update, create a package, distribute this package to distribution point and the application need to be shut down while applying the update.

Managing Office 365 updates is a new feature from SCCM 1511. Also, there are improvements in how we manage the updates from SCCM 1511 to SCCM 1606

Before we proceed with how we manage the updates of O365, we first have a basic understanding of O365 Client, the deployment method and the available channels as this information is needed in choosing the relevant updates.

Overview of O365 Client :

Office 365 Client is similar to other versions of Office. The programs in O365 have the same features and functionalities as other versions of Office and the applications work the same way in all the available versions.

The difference is only with respect to Licencing and deployment methods.

There are different versions of O365 Clients:

  1. Office 365 ProPlus
  2. Office 365 Business
  3. Visio Pro for Office 365
  4. Project for Office 365

I have used O365 ProPlus Client for this demonstration

Licencing:

To use Office 365 ProPlus, a user must have an Office 365 account and should be assigned to a licence .And this is the reason the client machine is to be connected to internet at least once in a month so that the user’s licence can be validated

I have used the O365 Client Trial Version which is valid for 30 days. After 30 days, office apps works with reduced functionalities

Deployment Methods:

O-365 Proplus  uses different method of installation “Click-to-Run” which is different from the traditional MSI.

The advantages of Click-to-run over MSI installation are:

  1. User can run these apps and doesn’t have to wait till the installation completes
  2. By default Click-to-run products are configured to be updated automatically
  3. Flexibility to choose among the channels ( Current, Deferred )
  4. Run different versions of Office products on the same computer
  5. Flexibility to restrict the applications during product installations

Overview of Channels :

Microsoft has changed the terminology from Branch to Channel. This is the key factor in managing the updates. Which updates are to be applied to which client depends on which channel the Client has

There are 4 different types of Channels:

  1. Current Channel : Provides the users with all the new features as soon as they are released by Microsoft
  2. First release Current Channel : Provides an earlier look of the next current Channel
  3. Deferred Channel : Provides the users with the updates few times a year
  4. First release Deferred Channel : Provides an earlier look for the next Deferred Channel

How to decide on Channels ?

It is recommended to use the Deferred Channel in production as we have time to test the new updates and upgrades through their respective First releases

Current Channel, First release of Current Channel, First release of Deferred Channel can be used by set of pilot users who want to test the features and functionalities

Check this Microsoft Technet Article  for Version and Build numbers

Managing O365 Updates with SCCM (System Center Configuration Manager) 1603:

Prerequisites :

  1. SCCM 1602 or later
  2. Windows Update Services 4.0
  3. Office 365 Client with the below min version

For Current Channel, Version should be at least  1602 (Build 6741.2017)

For First Release for Deferred Channel, Version should be at least  1602 (Build 6741.2014)

For Deferred Channel, Version should be at least  1602 (Build 6741.2048)

4.   Office Deployment Tool

Enable Configuration Manager to receive O365 Client updates:

Navigate to Administration-> Site Configuration->Sites-> Right click on the site->Configure site components->Select Software Update Point

1

Navigate to Classifications and select check updates and upgrades

2

Navigate to Products and select Office 365 Client under Office

3.PNG

Navigate to Software Library and Synchronize Software Updates

4.PNG

Enable O365 Client to receive software updates from System Center Configuration Manager:

There are two ways to enable to O365 Clients to receive the updates

  1. Office Deployment Tool . This supports only the new installations
  2. Group Policy Administrative Template. This  supports already installed O365 as well as new installations

Enabling O365 updates with Office Deployment Tool:

Download the Office  Office Deployment Tool

Extract the contents to a shared location. The extraction will have setup.exe and configuration.xml files

sccm 2012 Office 2016 deployment

Edit the .xml file as per your requirements in version and channel

You can refer the below link for changing the xml file to your requirement

Click to run installation with Office Deployment Tool xml file

Technet Article

The standard command to install the O365 client is

setup.exe /configure configuration.xml

5.png

Enabling Office 365 Client to receive updates with Group Policy with Office Admin Template:

6.PNG

  • Import Office16.admx to GPO repository ( C:\windows\PolicyDefinitions)
  • Also copy the necessary language files to GPO repository

7.PNG

  • Enable the Office 365 parameters in gpedit.msc

Enable Office 365 Client Management and channel identifier  parameters

8.PNG

Channel identifier :

“Validation” for First Release Deferred Channel

“Current” for Current Channel

“Business” for Deferred Channel

11

Checking for the O365 Updates in SCCM

O365 Client version in my client Machine is 6741.2047 which is First release for Deferred channel and it was released on June 7th 2016.

We can use this article to check the build and version information of the clients

12.PNG

Now, we can see in SCCM console that a newer update is available for the existing O365 Client under required section

13.PNG

Creating a package with O365 Update:

Select the update, right click on that and create a software update group

14.PNG

15.PNG

Select the software update group, right click and select download

16.PNG

Create a  new Software deployment Package

17.PNG

Select the Distribution Point

18.png

19.png

Check for the download progress

20.png

Deploy the software update to the collection :

Select the O365 update which is showing as required, right click and select deploy

21.PNG

Give the name to the deployment

22

Select the deployment method

31.PNG

Select the schedule for the deployment

23

Select the user experience

24

Configure alerts if required

25

Select the download settings

26

Select the option to download the package from internet

28

Select the language

29

Review the settings and download the package

30

Now login to the client machine and check for the available update

Open software Center and check for the update

32.png

Download and install the available O365 Update

33.png

Now, we see that available update is successfully installed in the client machine

34.png

Configuring Azure AD Application Proxy

Many  Organizations have their web portals and applications which are hosted on- premise and there are many methods to give access to these applications on internet

One of the traditional methods is to have a Virtual Private Network configured, which will establish a secured connection between corporate network and internet users

Azure helps us to give access to in-house portals to internet with the help of Azure Application Proxy which avoids the need of using a separate virtual private Network configuration

For this demo, I am using Azure classic portal ( https://manage.windowsazure.com )

Components for Configuring Azure Application Proxy

  1. Azure Subscription
  2. Azure Application Proxy connector ( This can be downloaded from Azure Portal )
  3. An in-house portal which can be added to Azure

Azure Subscription :

you can get a free trial subscription from https://manage.windowsazure.com

If you have any existing Azure subscription ( either through EMS or O365, login to the Azure portal with Global Admin credentials )

Configuration of Azure Application Proxy 

Navigate to Azure portal from https://manage.windowsazure.com

Azure Sign in page

Navigate to Active Directory

AD Navigation

Click on Dashboard and scroll down to the end, where you can see Application Proxy

Click on Configure

Application Proxy enable

Scroll down to Application Proxy section

By default, Application proxy is disabled. Switch to enabled

Download the connector from the url

Download APplication proxy connector

It is recommended to install the connector in Windows Server 2012 R2 Server which has access to Corporate Network as well as internet

Connector installation 1

The installation will prompt for the Azure Admin credentials

connector login

Run the Connector troubleshooter

Connector Finish

You will be prompted with a command prompt on successful installation

Cmd prompt

Go back to the azure portal ( https://manage.windowsazure.com ) and come to the section of application proxy through the dashboard

Click on Manage Connectors if you install multiple connectors for redundancy

Manage Connectors

Connector status

Adding in-house portal to Azure :

For this demo, I am using an in-house portal http://*****mysalary/, which can be accessed only through my corporate network

Url : http://*****mysalary/

kartik salary page.PNG

Now, I will add this portal in Azure and configure it, to access from internet

Access Azure portal ( https://manage.windowsazure.com )

Navigate to Active Directory and click on Applications

Applications

Click on Add button which is at the bottom of the page and select the third option which is “publish an application that will be accessible from outside your network”

Add application

Give the details of the portal

portal name

Add the users to which you want to give access to this portal and navigate to Configure section as highlighted below

configuring portal

External url is the one which will allow you to access this portal through internet as you can see it has https in the url

payroll config2.PNG

To access this portal, Navigate to https://myapps.microsoft.com and sign in with Azure credentials

This will give you the list of applications and portals for which you were given access from Azure

In our scenario, payroll proxy is the App which will allow us to access my in-house portal through internet

Myapps.PNG

I am able to login to the portal through internet and you can see the url with https://*****

payroll https.PNG

Cross Check :

If you want to cross check whether proxy is configured correctly, you can access the app in mobile phone

Download “My Apps” application from Play store which will provide the list of applications under your login

List of apps available :

My apps in mobile (1).png

As you can see, I am able to access my payroll portal through https://***  in my mobile phone

payroll proxy in mobile.png

Disaster Recovery with Microsoft ASR ( Hyper-V to Azure)

Every Organization needs a Strategy for planned and unplanned Outages to keep their workloads, apps, services, data running all the time. This strategy should really assure them to achieve the continuity of services

What is ASR : Disaster recovery Solution Provided by Microsoft Azure

Azure Portal Used for this Demo : Classic

Using Microsoft ASR ( Azure Site Recovery), one can achieve the BCDR ( Business Continuity and Disaster Recovery) in a very simple way which is far more efficient compared to the traditional disaster recovery methods

The traditional way to have a disaster recovery plan is to have a on-premise secondary site which has the equal compute and can take the work loads in case of a disaster and replicate the data between primary and secondary sites.

Advantages of having Azure Site recovery :

  1. Simple BCDR Strategy
  2. Flexibility in Replication
  3. Easy Recovery
  4. Eliminate Secondary DataCenters
  5. Integrate with existing BCDR strategies

Replication is the key in any Disaster Recovery plan and ASR does this in a very sophisticated way

What can I replicate ?

  1. On-Premises Physical Servers (Both  Hyper-V and VMWare)
  2. On-Premises VMWare Virtual Machines
  3. On-Premises Hyper-V Virtual Machines
  4. On-Premises Hyper-V Hosts in VMM Cloud

Note : We can configure only the orchestration between On-premises Linux, On-Premise Hyper-V Hosts in VMM Cloud with SAN-Storage replication.

Now, I am going to show you the configuration of Azure Site Recovery between Hyper-V Virtual Machines and Azure Cloud

Prerequisites for Azure :

  1. Azure Account
  2. Azure Storage Account
  3. Azure Site Recovery Vault
  4. Azure Virtual Network

Prerequisites for Hyper-V

  1. Server running with Windows Server 2012 R2 with Hyper-V role installed
  2. At least 2 Virtual Machines running with this Hyper-V
  3. Hyper-V host connected to internet

Note : If Hyper-V host cannot face internet, configure the proxy server which will allow the below url’s

  • *.hypervrecoverymanager.windowsazure.com
  • *.accesscontrol.windows.net
  • *.backup.windowsazure.com
  • *.blob.core.windows.net
  • *.store.core.windows.net

Step :1

Create Azure Vault

Sign into https://manage.windows azure.com with azure account

  1. Expand Dataservices -> Recovery Services and click Site recovery Vault
  2. Click create new -> Quick Create and give the details of region and subscription
  3. Click Create Vault

AzureVault

Step-2:

Create Hyper-V Site

Click on the Newly created Vault and select the highlighted pane

Dashboard

Select the recovery model as said below

select recovery site

steps for creating site

  1. Create Hyper-V Site 

click on “Create Hyper-V Site”

Create Hyper-V SiteHyper-V Site

2. Prepare Hyper-V Server  ( These steps are to be performed in Hyper-V Host )

A sample Virtual Machine, VM1 is created in Hyper-V host for this demo

Download the Provider and  registration key to Hyper-V Host

Provider 1.PNG

Browse the Vault credentials which were downloaded

Vault Settings

Finish

Come back to Azure Portal ( https://manage.windowsazure.com)

Create a Storage account 

Storage Account

Create Azure Network AccountNetwork Account

Go back to the Azure Vault and create a Protection Group 

Protection Group

Replication settings

Select the Protection Group and add Virtual Machines to the Protection Group

Add VM2

Once we add the Virtual Machine, it is replicated to Azure 

VM2 Replication Status

Synchronizing.png

Select the Virtual Machine and click on ” Test Failover”

Test Failover.png

Select the VM and review the configuration settings and change the Microsoft Azure Network to the network which we created

VM configurations

Select the Virtual Network for the failover

Failover network selection

VM Replication Status

VM replication

Disk Replication Status

Disk replicatioon