How to prompt for credentials just once

Usually when I open a shell at the beginning of the day it stays open the whole day. During that time I may run SQL queries, web requests, you name it. I don’t mind entering credentials for these (verses storing them in a text file), but it is nice when I only have to enter them once.

Enter a short function called ‘creds’. Creds will look for a variable named $creds and if it doesn’t find one it will prompt me for a password.

 

function creds {
    if( ! $creds ) {
     $script:creds = get-credential -Message "webSvc creds" -UserName "user@domain.net"
    }
    $creds
}

When I run a command that uses the creds function it will prompt me when it can’t find them. In this example I’m calling a web service.

function new-webSvcQuery {
param( $Query )
    $creds = creds
    try {
        $webSvr = "http://mywebsvc.domain.net/apppath/appXmlIF.asmx"
        $webSvrPxy = New-WebServiceProxy -Uri $webSvr -credential $creds
        $webSvrData = ( $webSvrPxy.AsXml($Query) ).Data1
    }
    catch [System.Net.WebException] {
     throw "your query is broke man"
    }
    finally {
     $webSvrData
    }
}

You can adapt the idea to any interface that takes a PS Credential object

 

Posted in Uncategorized | Leave a comment

PowerShell classes and password gizmos…

Over the years I have rewritten every useful chunk of code I’ve found according to what my skill could provide and also (much less often, I’ll admit) what PowerShell offered at the time. PowerShell introduced classes a few months back and I’ve decided it’s time to update some more code. This time, I’m going to try to make a PowerShell class to handle my credentialing needs. And trust me, my credentials are needy.

I’m going to use a couple of functions from previous articles that will merge here. In my first ever post about accessing MySQL programmatically, I showed you a function called myCStr (I used ‘filter’ in the post) that will create a database connection string with a rudimentary database query tool. Later on I ranted about another way to handle authentication with PowerShell secure strings and trimmed down the original


filter myCStr {
    param (
        [System.String]$Server,
        [System.String]$uid = $env:USERNAME,
        [System.String]$pwd = (get-mycreds).GetNetworkCredential().password
    )

    if ($Server -eq "myFavServer") {
        $myCStr = "Server=$Server;port=23306;uid=$uid;pwd=$pwd"
    }
    if ($Server -eq "my2ndFavServer") {
        $myCStr = "Server=$Server;port=33306;uid=$uid;pwd=$pwd;"
    }
    else {
        $myCStr = "Server=$Server;port=3306;uid=$uid;pwd=$pwd;"
    }
    $myCStr
}

In that article I also made a ‘password gizmo’

function set-mycreds{
    param(
        [string]$username = $env:USERNAME,
        [string]$message = "mycreds for mysql"
    )
    $creds = get-credential -Message $message -UserName $username
    $global:myCred = $creds.Password
}
function get-mycreds {

     if(-not $myCred){ set-mycreds }
     [PSCredential]::new( $env:USERNAME, $myCred )

}

They work together to create a reliable data object. By reliable, I mean I always know what I am going to get from it. Since it’s so trusty I can use it in a diverse set of coding circumstances and I am pretty sure I can make it handle 100% of my shell console authentication needs. What would this look like if I were to use it as a PowerShell class? Well, what would a basic authentication ‘class’ look like? I guess we need a class structure that has user name and password properties

class myCreds
{
    [string]$UserName
    [string]$Password
}

This is similar to using New-Object (or some variation) to spin up a PSObject or Add-Type with a definition, this way just takes a lot less code, but stores the password as plain text in memory.

PS C:\bjm\pwrshl> $myCreds = [myCreds]::new()
PS C:\bjm\pwrshl> $myCreds.UserName = "brendan"
PS C:\bjm\pwrshl> $myCreds.Password = "brendansPassword"
PS C:\bjm\pwrshl> $myCreds | ft -a

UserName Password
-------- --------
brendan brendansPassword

PS C:\bjm\pwrshl>

It would be advantageous for the class to possess a constructor, that will make it much more efficient to instantiate (create) an instance.

class myCreds
{
    [string]$UserName
    [string]$Password
    myCreds ([string]$UserName, [string]$Password)
    {
        $this.UserName = $UserName
        $this.Password = $Password
    }
}

Notice the constructor uses ‘strongly typed’ input params, that is, they both have [string] in front of the $variable name (we’ll get back to that). Now I create the $myCreds array with a single command.

PS C:\bjm\pwrshl> $myCreds = [myCreds]::new('brendan','brendansPassword')
PS C:\bjm\pwrshl> $myCreds | ft -a

UserName Password
-------- --------
brendan brendansPassword

PS C:\bjm\pwrshl>

While there are applications for something like this with Invoke-WebRequest, MySQL, ssh, plink or pscp and other plaint-text auth, it is not very versatile. Now I have to type my UserName… I also miss the fact that my password is no longer encrypted… Add to that, most of my authentication is AD based and requires a credential object, like I used to have. I can flip this over, though, and make it a little more like my function.

class myCreds
{
    [string]$UserName
    [SecureString]$Password

    static $script:myCreds = [myCreds]::set()

    static [void] set()
    {
        if ( -not $script:myPw ) {
            $script:myPw = (
                Get-Credential -UserName $env:USERNAME -Message "$($env:USERNAME)'s password"
            ).Password
        }
    }
    static [pscredential] get()
    {
        return [pscredential]::new($env:USERNAME, $script:myPw)
    }
}

Before I go on, let me explain a little bit. Line 4:

[SecureString]$Password

$Password is no longer a [string], it’s a [SecureString]. At this point, it doesn’t really matter because I’m never going to return a [myCreds] object anymore, this class only returns a [PSCredential]. Should I need a clear text copy of my password, [PSCredential] can easily create it for me.

PS C:\bjm\pwrshl> [myCreds]::get().GetNetworkCredential().Password
brendansPassword

On line 6:

static $script:myCreds = [myCreds]::set()

I call (instantiate) the class, this is sort of a ‘short cut’. Without this line I would have to make a set call to load the $myPw variable, with that tidbit I can just get an instance. If the $script:myPw variable is not set I will get prompted for my password. As a bonus, when I do get prompted I will no longer be hassled with typing the seven letters of my first name!

On line 8 I have replaced the constructor syntax with a set method. I have also removed ConvertFrom-SecureString, which was a holdover from when I wrote passwords into a file and then read them when during run time.

static [void] set()
{
    if ( -not $script:myPw ) {
        $script:myPw = (
            Get-Credential -UserName $env:USERNAME -Message "$($env:USERNAME)'s password"
        ).Password
    }
}

On line 11 I have reduced the scope of the variable that contains the Secure.String password. Formerly I loaded this into $global: but after consideration, it doesn’t need to leave my script.

$script:myPw = (

Line 16 starts the get method. The get method reads the $env:USERNAME and $script:myPw variables and gives out a [PSCredential] object.

static [pscredential] get()
{
    return [pscredential]::new($env:USERNAME, $script:myPw)
}

Now, my class has all the functionality of the functions I used to use. Right now, the code takes a few more lines than the functions did. It will make up the difference when I consume the object. Plus it’s a PowerShell class now, so I can inherit it’s properties to do some other cool stuff. For now, I just am just going to blend my new class with my old function.

filter myCStr {
param (
[System.String]$Server,
[System.String]$uid = [myCreds]::get().UserName,
[System.String]$pwd = [myCreds]::get().GetNetworkCredential().Password
)

if ($Server -eq "myFavServer") {
$myCStr = "Server=$Server;port=23306;uid=$uid;pwd=$pwd"
}
if ($Server -eq "my2ndFavServer") {
$myCStr = "Server=$Server;port=33306;uid=$uid;pwd=$pwd;"
}
else {
$myCStr = "Server=$Server;port=3306;uid=$uid;pwd=$pwd;"
}
$myCStr
}
PS C:\bjm\pwrshl> myCStr MyMySQLServer
Server=MyMySQLServer;port=3306;uid=brendan;pwd=brendansPassword;

So there you have it, yet another way to handle credentials! I hope this information comes in handy for conjuring a PSCredential in your shell or creating MySQL connection strings dynamically. Next time I plan to use these same functions to demonstrate even more advanced features of PowerShell classes.

When writing this article I used a few other sites for reference:
Michael Willis@xainey gives an indepth discussion of classes and some great examples.
ps1 from PowerShell.com/Idera has 6 short articles to get you started creating your own classes. If you aren’t getting their Daily Tips email, I highly recommend them.
And what would a Google search for PowerShell info be without a link to an article by Ed Wilson (a.k.a. The Scripting Guy). Here he gives his down and dirty on classes (and mispells Camaro).

Posted in powershell | Tagged , | Leave a comment

Yet another way to handle authentication with PowerShell secure strings

I realize there are tons of articles out there about handling authentication. What I don’t see are a lot of examples where the methods are demonstrated within a set of tools. Authentication schemes can turn a simple shell task into a chore. You can find a wide range of methods to secure a password for a Schedule Task that’s manageable for one or two jobs, though are probably not simple enough to help you speed things up for routine tasks.

I’ve been using Secure-String to hash passwords for a long time. Here is a basic credential stash that prompts you for your password when you launch a shell and then stores the secure-string in a variable. Later you can fetch the variable for various other commands that can accept a credential or user/pass as an argument.

# popup a login prompt
[string]$username = $env:USERNAME
[string]$message = "mycreds for mysql"
$creds = get-credential -Message $message -UserName $username
# store the secure string
$global:myCred = $creds.Password | ConvertFrom-SecureString

This code will popup a Windows credential dialog where I can securely type my password. Since my MySQL user name is the same as my $env:username variable, I’ll just use that. I like to make that a little easier to handle from a shell prompt, so I can call them efficiently when I need them.

function Set-myCreds{
 param(
 [string]$username = $env:USERNAME,
 [string]$message = "mycreds for mysql"
 )
 $creds = get-credential -Message $message -UserName $username
 $global:myCred = $creds.Password | ConvertFrom-SecureString
}

Now I can store them with a single command

set-mycreds-login-prompt.png

The function stashes my hashed password in the variable $global:myCred, it’s available in the shell as $myCred, also.

set-mycreds-global-var

Now I can recreate the credential object pretty easily:


$myCreds = [PSCredential]::new(
  $env:USERNAME,($myCred | ConvertTo-SecureString)
 )

Thats it… I’ve used several similar schemes where the password was stored in a txt file to be read using Get-Content. But I don’t mind typing a password when I open a new shell, if I need to. And, just like the set-mycreds funtion, having a tool to get a credential object makes it handy.


function Get-myCreds {
    [PSCredential]::new(
       $env:USERNAME,($myCred | ConvertTo-SecureString)
    )
}

I can add a little safety to this but checking to see if $myCred has anything in it before creating the credential, like this:


function Get-myCreds {
    if(-not $myCred){ set-mycreds }
    [PSCredential]::new(
        $env:USERNAME,($myCred | ConvertTo-SecureString)
    )
}

That way, if the $myCred variable isn’t set (like the first time I use it) it prompts me to set a password for the $myCred variable. By itself, it is already useful for cmdlets that take a PSCrendtial object like the VMware tools.


Connect-ViServer -Server some.vc.com -Credential (get-mycreds)

To use the stored password for something like MySQL it has to be decoded into plain text. The PSCredential class has a method named GetNetworkCredentail() just for that purpose. Once you have the credential object you can convert it like this.

$mycreds = Get-myCreds
$mycreds.GetNetworkCredential().password
brendansPassword

 

In a previous article I discussed connection accessing MySQL with PowerShell programmatically. I put a poorly introduced function I use to configure connection strings for MySQL. If can easily be ported to other platforms like SQL Server and Oracle. Anyway, it’s worth a (simplified) repost. This time I’ll use the Get-myCreds function I just created.


filter myCStr {
    param (
        [String]$Server,
        [String]$uid = $env:USERNAME,
        [String]$pwd = (Get-myCreds).GetNetworkCredential().password
    )

    if ($Server -eq "myFavServer") {
        $myCStr = "Server=$Server;port=23306;uid=$uid;pwd=$pwd"
    }
    if ($Server -eq "my2ndFavServer") {
        $myCStr = "Server=$Server;port=33306;uid=$uid;pwd=$pwd;"
    }
    else {
        $myCStr = "Server=$Server;port=3306;uid=$uid;pwd=$pwd;"
    }
    $myCStr
}

The difference now is that I will only be prompted for a password the first time I connect to a MySQL instance with the connection string generator. This is extremely handy when you run a process, like an inventory, since I only have to enter my password one time!

Storing credentials for use in your shell can simplify authentication for shell tools you may use. Storing the credentials in memory can help you avoid unnecessary security concerns over storing credentials written in a text file. You can use a tool like this for PowerCLI, UCS Manager, or homegrown code like my MySQL connection string function.  Having a means to streamline authentication can make your daily tasks easier.

Posted in powershell | 1 Comment

Homegrown PowerShell tools for MySQL

In my previous article I created a function that uses the MySQL connector for .Net to run MySQL queries that return as an object inside either a script or a shell. Now I’ll show you how to use that function as a base for a few other tools.

Lets start with a simple function to fetch the version of MySQL:

function get-mysqlVersion {
param ($server)
 $query = "select @@hostname as hostname, @@version as version;"
 $result = Start-mysqlQuery -Server $server -Query $query
 $result
}

Running against a few servers I can see which MySQL versions I have.

PS C:\> cat .\myservers.txt | %{ get-mysqlVersion $_ } | ft -a

hostname                version
--------                -------
mysqlserver04.local.com 5.6.23-enterprise-commercial-advanced-log
mysqlserver02.local.com 5.7.17-enterprise-commercial-advanced-log
mysqlserver03.local.com 5.7.17-enterprise-commercial-advanced-log
mysqlserver01.local.com 5.7.17-enterprise-commercial-advanced-log

Now I have a handy way to identify servers due for upgrade or patching. Let’s take another look at the $Query from my last article where we looked up users:

 
$query = "select user,host,password,@@hostname as hostname from mysql.user;"

And wrap that in a simple function to make a tool, just like the Version function:

function get-mysqlUsers {
 param ($server)
  $query = "select user,host,password,@@hostname as hostname from mysql.user;"
 Start-mysqlQuery $query $server | select User, Host, Password, Hostname
}
PS C:\> get-mysqlUsers mysqlserver04| ft -a

user    host      password                                  hostname
----    ----      --------                                  --------
root    localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver04.local.com
myuid   %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver04.local.com
svcuser %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver04.local.com

Now all this works great until we hit a server version 5.7 or newer:

PS C:\> get-mysqlUsers mysqlserver01 1| ft -a
ERROR : Unable to run query : mysqlserver01 select user,host,password,@@hostname as hostname from mysql.user;
PS C:\>

Well at least we know the try{} catch{} is working right, unfortunately it suppresses the real error message. To see what really happened we need to look at $error[0]

PS C:\> $error[0]
Exception calling "Fill" with "2" argument(s): "Unknown column 'password' in 'field list'"
At C:\myscripts\mysqlcrap.ps1:137 char:3
+         $RecordCount = $dataAdapter.Fill($dataSet, "dataSet")
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : MySqlException

Since the ‘password’ field doesn’t exist in versions 5.7 or later we have an issue. The same data does exist, though, in a new field called ‘authentication_string’. So what now? We could build a separate function to run only against newer versions… We could also check the MySQL version in the function and submit a query based on that:

function get-mysqlUsers {
param ($server)
 $ver = get-mysqlVersion $server
 if ($ver.version -match '5.7') {
  $query = "select user,host,authentication_string as password,@@hostname as hostname from mysql.user;"
 }
 else {
  $query = "select user,host,password,@@hostname as hostname from mysql.user;"
 }
 Start-mysqlQuery $query $server | select User, Host, Password, Hostname
}
PS C:\> get-mysqlUsers mysqlserver01 | ft -a

user      host      password                                  hostname
----      ----      --------                                  --------
root      localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver01.local.com
mysql.sys localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver01.local.com
myuid     %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver01.local.com
svcuser   %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver01.local.com

Awesome! Now it works against a 5.7 server, but most importantly, I can run it against all my MySQL versions and get output all in the same table:

PS C:\> cat .\myservers.txt | %{ get-mysqlUsers $_ } | ft -a

user      host      password                                  hostname
----      ----      --------                                  --------
root      localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver01.local.com
mysql.sys localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver01.local.com
myuid     %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver01.local.com
svcuser   %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver02.local.com
root      localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver02.local.com
mysql.sys localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver02.local.com
myuid     %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver02.local.com
svcuser   %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver03.local.com
root      localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver03.local.com
mysql.sys localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver03.local.com
myuid     %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver03.local.com
svcuser   %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver03.local.com
root      localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver04.local.com
myuid     %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver04.local.com
svcuser   %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver04.local.com

Using the Start-mysqlQuery and myCStr functions as a base, I was able to add two more commands to my MySQL toolbox for fetching version and user information. I hope you find this useful. If you do or if you have any problems or suggestions then let me know in a comment!

Posted in powershell | Leave a comment

Accessing MySQL with PowerShell programmatically


Being able to access MySQL data from a PowerShell session or script can be useful in many ways. Using the MySQL .Net Connector it is possible to create a set of functions to query and manage your databases. The connector can be downloaded and installed from the MySQL website.

Once downloaded and installed, you should find MySql.Data.dll somewhere like:
C:\Program Files (x86)\MySQL\Connector.NET 6.9\Assemblies\v4.5\MySql.Data.dll

Make the MySql.Data.MySqlClient namespace available to your shell or script with a loadfrom command:

 

 

[void][system.reflection.Assembly]::LoadFrom(

'C:\Program Files (x86)\MySQL\Connector.NET 6.9\Assemblies\v4.5\MySql.Data.dll'

)

To create a client instance you’ll need at least a user id, a server name and a password. You can specify some other stuff, also, like a TCP port or database name. Create a connection string like $myCStr below:

 

$pwd = "mypass"
$Server = "myServer"
$Database = "myDatabase"
# without a database name
$myCStr = "Server=$server; uid=$uid; pwd=$pwd; "
# with a database name and using $env:USERNAME variable
$myCStr = "Server=$server;uid=$env:USERNAME;pwd=$pwd;database=$Database; "

Make up some query and store it in a variable:

$Query = "select user, host from mysql.user;"

Once all that is squared away, create a new connection object:

$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$Connection.ConnectionString = $myCStr
$Connection.Open()

Create the appropriate object to connect and query your database server:

$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)

Create a bucket to receive the query output:

$DataSet = New-Object System.Data.DataSet

Execute the query and catch the query info (rows affected, etc.) with the $RecordCount variable and the actual data in the $DataSet variable.

$RecordCount = $DataAdapter.Fill($DataSet, "dataSet")

Close the connection:

$Connection.Close()

Display the data:

PS C:\> $DataSet.Tables[0] | ft -a

user          host
----          ----
devuser       devhost
testuser      testhost
myuid         %
root          localhost

Pretty simple, huh? If you plan on doing this a lot you may want to lock this process down. I use a couple of functions that streamline the query process. The first function (filter)myCStr I use to create the connection string:

filter myCStr {
param (
[Parameter(Position = 0, Mandatory = $false, ValueFromPipeline = $true)]
[System.String]$Server = "myFavServer",
[Parameter(Position = 1, Mandatory = $false, ValueFromPipeline = $true)]
[System.String]$Database,
[Parameter(Position = 2, Mandatory = $true, ValueFromPipeline = $true)]
[System.String]$uid,
[System.String]$pwd = (Get-Credential $uid).GetNetworkCredential().password
)
if ($Database) {
$myCStr = "Server=$Server;uid=$uid;pwd=$pwd;database=$Database;"
}
else {
$myCStr = "Server=$Server;uid=$uid;pwd=$pwd;"
}
if ($Server -eq "myFavServer") {
$myCStr = "Server=$Server;port=23306;uid=$uid;pwd=$pwd;database=$Database;"
}
if ($Server -eq "my2ndFavServer" -and $Database -eq 'myFavDb') {
$myCStr = "Server=$Server;port=33306;uid=$uid;pwd=$pwd;database=$Database;"
}
$myCStr
}

I’ll write more about this function later. I use the ‘filter’ statement to differentiate a functions ‘functionality’ internally, although functionally ‘function’ and ‘filter’ are the same. After configuring the filter to suit your environment, you can call it from your shell or script. This makes fetching a connection string for a given connection much more efficient.

PS C:\> myCStr my2ndFavServer myFavDb myuid
Server=my2ndFavServer;port=33306;uid=myuid;pwd=somepw7;database=myFavDb;
PS C:\>

If you don’t specify a user id (uid) then myCStr will prompt you. You can set a static password in the function instead of relying on ‘get-credentials’ in the params, if you are comfortable with that. Or use your own userid/password tools.

Now a tool to run a MySQL query. The Start-mysqlQuery function puts it altogether for me. Start-mysqlQuery will call myCStr  and fetch a preconfigured connection string for you.

function Start-mysqlQuery {
[CmdletBinding()]
param (
[Parameter(Position = 0, Mandatory = $true,ValueFromPipeline = $true)]
[System.String]$Server,
[Parameter(Position = 1)]
[ValidateNotNull()]
[System.String]$Query,
[Parameter(Position = 2)]
[System.String]$Database
)
Try {
[void][system.reflection.Assembly]::LoadFrom(
'C:\Program Files (x86)\MySQL\Connector.NET 6.9\Assemblies\v4.5\MySql.Data.dll'
)
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$Connection.ConnectionString = myCStr $Server $Database
# $Connection.ConnectionString
$Connection.Open()
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
if ($Query -like 'call%') {
$query
$Command.CommandType = [System.Data.CommandType]::StoredProcedure
}
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $DataAdapter.Fill($DataSet, "dataSet")
$Connection.Close()
$DataSet.Tables[0]
}
Catch {
Write-Host "ERROR : Unable to run query : $Server $query" # $Error[0] | fl *
}
Finally {
}
}

Now you can query your MySQL servers from the command line or use the code in your own scripts. Start-mysqlQuery returns a data object and viewed or used using other PowerShell operations:

PS C:\> start-mysqlQuery -Server mysqlserver01 -Query $query | ft -a

cmdlet myCStr at command pipeline position 1
Supply values for the following parameters:
uid: myuid

user           host
----           ----
myuid          %
mysql.sys      localhost
root           localhost

Notice that myCStr is harassing me about a user id and password. That’s fine if I’m only going to query one or two servers and not use the code within a scheduled task. You can configure myCStr with a password by altering the $uid and $pwd params with a default value:

[System.String]$uid = "myuid",
[System.String]$pwd = "mypassword"

While not the most secure example, there are many ways to make it secure. Using the command I can do a quick user inventory on a list of MySQL database hosts:

PS C:\> $Query = "select @@hostname as hostname, user, host from mysql.user;"
PS C:\> cat .\myservers.txt | %{ start-mysqlQuery -Server $_ -Query $query } | ft -a

hostname                user           host
--------                ----           ----
mysqlserver04.local.com myuid          %
mysqlserver04.local.com mysql.sys      localhost
mysqlserver04.local.com root           localhost
mysqlserver01.local.com myuid          %
mysqlserver01.local.com mysql.sys      localhost
mysqlserver01.local.com root           localhost
mysqlserver02.local.com myuid          %
mysqlserver02.local.com mysql.sys      localhost
mysqlserver02.local.com root           localhost
mysqlserver03.local.com myuid          %
mysqlserver03.local.com mysql.sys      localhost
mysqlserver03.local.com root           localhost

From here the possibilities are unlimited. By sorting or grouping the output of this query I could make short work of finding standard users that are missing, or non-standard users that are present. Comment below and let me know what you think!

Posted in powershell, Uncategorized | Tagged , , , , | 2 Comments