Export data from All SQL tables within a Database in CSV format- Using Powershell

Hi

Here is the script you can use to export all the data from SQL tables within a given database into csv format. This script is purely powershell based.

 

List all ARM VMs in azure

Below script will list all ARM VMs in Azure

 

 

List all the Classsic Azure VMs

Below script will list all the Classic VMs in azure .

 

 

Some useful OMS Queries

Percentage CPU Usage

Perf | where CounterName == “% Processor Time” | summarize avg(CounterValue) by Computer

Percentage Memory Usage

Perf | where CounterName == “% Committed Bytes In Use” | summarize avg(CounterValue) by Computer

Remove Header from Powershell get output

When you select one column in output of get command and then try to use it in a for loop you see each line is embedded in Curly braces {}

Instead of using select xyz you will use ForEach-Object {$_.xyz}

 

 

How to remove @ from array when working with JSON on Azure

When you work with JSON on powershell to chnage/replace arrays/list . you will see Convertto-Json will change your Azure compliant ARM template and put @ in front of arrays. This may cause issues in deployment.

Filter : {@{resource=; resourcegroup=}}

Output : 

“resource”: {
“property”: “name”,
“condition”: “like”,
“resourcetype”: “*”
},
“resourcegroup”: {
“property”: “resourcegroupname”,
“condition”: “like”,
“subscription”: “sub-xyz”
}

Export /Import Claim Rules for your relying party trusts to a text file

Sometimes you might be bored creating similar/same claim rules accross your enviornments . Below method will quickly help you export your claim rules and apply to different relying party trust

Step1 : Export to a text file 

 

Step 2 : Apply /Set your claim rules from text file you exported in last step 

 

Multi-Home OMS Agent using powershell

You can run below script on the VM iteself to talk to multiple workspaces. One Microsoft Monitoring Agent can talk to upto 4 workspaces. You can embed this into Remote PS-Session if you want to use PSRemoting.

 

ADFS second node not authenticating after certificate renewal

If you are getting below error

Event 15021 HttpEvent

An error occurred while using SSL configuration for endpoint sts.domain.com:443.  The error status code is contained within the returned data.

 

Here is the fix.

  1. Import New Certificate on Personal store.
  2. Get Service Comms Certificate thumbprint and run below on node 2

 

 

Enable Accelerated Networking

When Azure planned reboots occurred we have seen performance degradation on several VMs . Here is the quick fix which doesn’t cost anything extra.

We need to enable Accelerated networking to overcome this problem . process is pretty simple

Note : This solution only works on VM that is not a member of availability set.

Steps to enable accelerated networking

  1. Deallocate VM
  2. Note NIC Name and resource group name where nic reside
  3. Assign new NIC to VM
  4. Deallocated old nic .
  5. Run below on cloudshell

 

  1. Assign old nic back to the VM
  2. Deallocated new NIC and start VM

How to use Classic Azure Express Route Powershell

get-module

 

Cross Subscription or Tenant VNET to VNET VPN Gateway

Scenario 

There are 2 tenants , Tenant A and B.

They both have existing infrastructure in their subscriptions. Due to organisational merge they want to connect VNETs together on Azure Fabric keep both sides secure.

On both the sides they have Express route and doesnt have enough bandwidth on gateway subnet to house a gateway.

How do we do this ? 

they will need to use VNET to VNET VPN gateway is the solution.

In subscription A
VNET 1 VNET2

In subscription B
VNET 3 VNET 4

VNET 1 to VNET 2 are connected via VNET- VNET Peering you choose the option “Use remote gateways”. For vnet 2 you select “Allow gateway transit”.
VNET 3 to VNET 4 are connected via VNET- VNET Peering Do the same for subscription B, except, vnet D will use remote gateway, and C will allow it.

VNET 2 VNET 4 are connected via cross subscription VPN Site-to-Site Gateway

 

Use VPN Gateway between 3 and 4 .