Developer's Closet A place where I can put my PHP, SQL, Perl, JavaScript, and VBScript code.

Use PowerShell to Retrieve a List of Servers from VMM

Useful script to go after servers in VMM and output the list to a csv file.

Import-Module virtualmachinemanager;
$MasterList = @();
$serverList = Get-VM -VMMServer seacorpmgmt05 | Where-Object { (($_.Name -like '*DEMO*' -or $_.Name -like '*APP*' -or $_.Name -like '*WEB*' -or $_.Name -like '*CORP*' -or $_.Name -like '*UTIL*' -or $_.Name -like '*MGMT*') -and $_.Name -notlike '*dev*') };
foreach ($server in $serverList) {
   $serverNetworkAdapter = Get-SCVirtualNetworkAdapter -VM $server;
   $MyObject = New-Object PSObject -Property @{
      ServerName = $server.Name;
      ServerEnvironment = $server.Name.Substring(3,4);
      HostName = $server.HostName;
      VMStatusString = [string]$server.StatusString;
      VMState = [string]$server.VirtualMachineState;
      ServerIPAddress = $serverNetworkAdapter.IPv4Addresses;
      CPUCount = $server.CPUCount;
      Memory = $server.Memory;
      OperatingSystem = $server.OperatingSystem;
      IsHighlyAvailable = $server.IsHighlyAvailable;
      Description = $server.Description.Replace([Environment]::NewLine, '');
      CreationTime = $server.CreationTime;
   $MasterList += $MyObject;
$MasterList | Select-Object @{label='Server Name';expression={$_.ServerName.ToUpper()}}, @{label='Environment';expression={$_.ServerEnvironment.ToUpper()}}, @{label='VM Status';expression={if ($_.VMStatusString -eq $_.VMState) { $_.VMStatusString } else { $_.VMState + ' (' + $_.VMStatusString + ')'}}}, @{label='Host Name';expression={$_.HostName.Replace('.ds-iq.corp','').ToUpper()}},@{Name=’ServerIPAddress’;Expression={if ([string]::join(";", ($_.ServerIPAddress))) {[string]::join(";", ($_.ServerIPAddress))} else { " " }}},@{label='Cores';expression={$_.CPUCount}},@{label='Memory';expression={$_.Memory}},@{label='OS';expression={$_.OperatingSystem}},@{label='Highly Available';expression={$_.IsHighlyAvailable}},@{label='Comments';expression={if ($_.Description) {$_.Description + ', Created ' + $_.CreationTime} else { 'Created ' + $_.CreationTime }}} | EXPORT-CSV c:\temp\app-server-2.csv -notype;
Search for string and append string to file

A very useful string search and append that I find myself rewriting over and over. So I've decided to write a post and capture the script:

if grep -q sensu-client "/etc/rc.local"; then
echo "exists";
echo "does not exist"
sudo sed -i '/exit 0$/i sudo service sensu-client restart' /etc/rc.local;


Filed under: Bash, Linux, Ubuntu No Comments
Manage Traces in SQL Server

Quick notes on managing traces in SQL Server:

View all running traces:

SELECT * FROM sys.traces

Disable and delete the trace with the @status option = 2:

EXEC sp_trace_setstatus @traceid = 2 , @status = 0
EXEC sp_trace_setstatus @traceid = 2 , @status = 2

Use option 0 = disable, 1 = start, 2 = disable and delete.

Filed under: SQL No Comments
Delete a Kafka Topic

Typical delete command:

kafka-topics --zookeeper zk_host:port/chroot --delete --topic my_topic_name

The topic will be "marked for deletion" and should be removed soon. What happens is an /admin/delete_topics/<topic> node is created in zookeeper and triggers a deletion. When the broker sees this update, the topic will no longer accept a new produce/consume request and eventually the topic will be deleted. If the delete command doesn't work right away, try restarting the Kafka service.

To delete a Kafka topic after the Broker has lost connection to the topic:

Manually delete the topic:

If the delete commands fails (marked for deletion forever):

1. Stop the Kafka Broker.

2. Delete the topic from disk.

For example:

sudo mv /space1/kafka/FourSquareSearchApiResults-0 /space1/kafka/FourSquareSearchApiResults-0.bak

3. Delete the topic znode from ZooKeeper:


Here are the ZooKeeper commands to use to delete the topic from ZK:

1. Log onto zkcli:

hbase zkcli

2. Search for topics:

ls /brokers/topics

rmr /brokers/topics/my_topic_name

ls /brokers/topics

3. Start Kafka.

4. You can then recreate the Kafka topic.


Output in red:

echo "$(tput setaf 1)Red Text$(tput sgr0)"

Filed under: Linux No Comments
PowerShell script to convert string to encrypted password and back again

I've found it useful to store an encrypted password in a file on disk. However, this is not generally a good idea but at least the password is encrypted to the user/machine. Here is a rough outline of the process:

-- convert to encrypted password from clear text
$password = ConvertTo-SecureString 'SomePassword' -AsPlainText –Force
-- write to file as user/machine encrypted string which cannot be unencrypted by any other user on any other machine
$password|convertFrom-SecureString|set-content "c:\temp\cred.txt"


To make the file really useable, preface the password with the username and query for the username:

-- query for user name from file

$username = "UserName";
-- convert password to a secure string that can be sent through to Windows for authentication
$password = Get-Content "c:\temp\cred.txt" | Select-String $Username | foreach{$_ -replace $Username, ""} | foreach{$_ -replace " ",""} | ConvertTo-SecureString;


Finally, you can go a step further and crack open the password:

-- convert from encrypted password to clear text
$Ptr = [System.Runtime.InteropServices.Marshal]::SecureStringToCoTaskMemUnicode($password);
$password = [System.Runtime.InteropServices.Marshal]::PtrToStringUni($Ptr);[System.Runtime.InteropServices.Marshal]::ZeroFreeCoTaskMemUnicode($Ptr);
Filed under: PowerShell No Comments
Find and Delete Running SQL Trace

Yesterday we had a long running trace take up too much space on disk. To find the running trace, look for the location of the file to identify your trace, and get the id:

SELECT * FROM sys.traces

Next, disable and delete the trace with the @status option = 2:

EXEC sp_trace_setstatus @traceid = # , @status = 2

Use option 0 = disable, 1 = start, 2 = disable and delete.

Have fun with traces, but make sure to stop them before you go home for the weekend!

Filed under: SQL No Comments
Performance Tuning in SQL Server

I wanted to capture a few notes on performance tuning in SQL Server.

CPU and memory:

select * from sys.dm_os_performance_counters
where counter_name in ('Batch Requests/sec', 'SQL Compilations/sec' , 'SQL Re-Compilations/sec', 'Buffer Cache Hit Ratio', 'Buffer Cache Hit Ratio Base')

-- these counters accumulate since server startup, so we need to calculate usage over time.
-- for example, over the past 10 seconds (I'd expect to see this lower than 100, but that is only a guess until we learn how this server behaves - it depends on hardware, CPU type, etc.):

DECLARE @BatchRequests BIGINT;
SELECT @BatchRequests = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio                                                                                                          ';
WAITFOR DELAY '00:00:10';
SELECT (cntr_value - @BatchRequests) / 10 AS 'Buffer cache hit ratio                                                                                                          '
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio

-- The Batch Requests/sec value depends on hardware used, but it should be under 1000. The recommended value for SQL Compilations/sec is less than 10% of Batch Requests/sec and for SQL Re-Compilations/sec is less than 10% of SQL Compilations/sec

Page Life Expectancy:

SELECT [cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

Wait stats:

[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats



Filed under: SQL No Comments
Monitoring SQL AlwaysOn

SQL AlwaysOn offers DMVs that make monitoring simple.

use master
select * from sys.dm_hadr_cluster_members
select * from sys.dm_hadr_cluster
select * from sys.dm_hadr_cluster_networks
select * from sys.dm_hadr_availability_group_states

select * from sys.dm_hadr_cluster
select * from sys.dm_hadr_cluster_members
select * from sys.dm_hadr_cluster_networks
select * from sys.availability_groups
select * from sys.availability_groups_cluster
select * from sys.dm_hadr_availability_group_states
select * from sys.availability_replicas
select * from sys.dm_hadr_availability_replica_cluster_nodes
select * from sys.dm_hadr_availability_replica_cluster_states
select * from sys.dm_hadr_availability_replica_states
select * from sys.dm_hadr_auto_page_repair
select * from sys.dm_hadr_database_replica_states
select * from sys.dm_hadr_database_replica_cluster_states
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_group_listeners
select * from sys.dm_tcp_listener_states

SELECT * FROM sys.dm_xe_objects WHERE name LIKE '%hadr%'


Filed under: SQL No Comments
Recover Lost Files on Linux

TestDisk includes PhotoRec which is very good at recovering files:

sudo apt-get install testdisk

To run PhotoRec:

sudo photorec



Filed under: Linux, Ubuntu No Comments