File System Assessment with PowerShell and Splunk

Posted on October 8, 2017
by dmuegge

It has been a while since I posted, but here goes. I have been experimenting with Splunk to make one of my old processes better and easier.

In the past I have done file system assessments for customers to provide capacity planning information as well as usage patterns. There are a number of ways I have collected data depending on if the platform is Windows, Linux, or a NAS device. In this post I will focus on collecting file system metadata from a Windows file server. To do this I use a PowerShell script to collect the data and dump to a CSV file. Historically I would use the CSV output and load it into SQL Server as the first step. Then I would use the SQL connectivity and pivot charting functionality in Excel for the reporting. It occurred to me as I have been working with Splunk that I could improve this process using Splunk.

Another thought also occurred to me, this process could be performed by owners of Splunk with no impact on licensing costs. Splunk is licensed on daily ingest volume and that volume can be exceeded 3 times per month without penalty. File system assessment data is something that would typically only be collected on a periodic basis so this data could be ingested without increasing the daily license capacity. Using the methods I show below organizations who own Splunk could easily do free periodic file system assessments.

The first step is to collect the file system metadata using the following PowerShell script.

&lt;#
.SYNOPSIS 
    Collect filesystem metadata information to CSV file
 
.DESCRIPTION 
    Uses configuration information from xml file to scan file systems

.EXAMPLE
    StartXml-FileSystemMetadata.ps1 -XMLConfigurationFile "C:\Temp\Get-FileSystemMetadata.xml"
        
.NOTES
   
  Author:          David Muegge
    
  Requirements:    PowerShell Version 4
   
        
  Disclaimer
  ****************************************************************
  * DO NOT USE IN A PRODUCTION ENVIRONMENT UNTIL YOU HAVE TESTED *
  * THOROUGHLY IN A LAB ENVIRONMENT. USE AT YOUR OWN RISK.  IF   *
  * YOU DO NOT UNDERSTAND WHAT THIS SCRIPT DOES OR HOW IT WORKS, *
  * DO NOT USE IT OUTSIDE OF A SECURE, TEST SETTING.             *
  ****************************************************************     
#&gt;
[CmdletBinding()]
Param([Parameter(Mandatory=$false,Position=1)][String]$XMLConfigurationFile="C:\Temp\FileSystemMetadata_Config.xml")


#region Functions ##


function Start-ScanFileStructure{
[CmdletBinding()]
Param(
    [Parameter(Mandatory=$true)][String]$ScanBasePath,
    [Parameter(Mandatory=$true)][String]$OutputCSVFile,
    [Parameter(Mandatory=$false)][String]$ShareName=$null,
    [Parameter(Mandatory=$false)][String]$KeywordSearch=$null,
    [Parameter(Mandatory=$false)][Int]$KeywordDirPart=$null,
    [Parameter(Mandatory=$false)][Switch]$MD5
)

    
    Try{
        # File Structure Extract Keyword from Regex Search
        if($KeywordSearch){
        
            if($MD5 -eq "True"){
            
                Get-ChildItem -Path $ScanBasePath -File -Recurse | Select-Object @{Name="FileName";Expression={$_.Name}},DirectoryName,@{Name="Extension";Expression={$_.Extension.Replace(".","")}},Mode,CreationTime,LastAccessTime,LastWriteTime,@{Name="Bytes";Expression={$_.Length}},@{Name="Share";Expression={$ShareName}},@{Name="MD5";Expression={(get-filehash -Path $_.fullname -Algorithm MD5).Hash}},@{Name="Keyword";Expression={$_.FullName -match $KeywordSearch | Out-Null; $matches[1]}} | export-csv -Path $OutputCSVFile -NoTypeInformation   
            }else{
                Get-ChildItem -Path $ScanBasePath -File -Recurse | Select-Object @{Name="FileName";Expression={$_.Name}},DirectoryName,@{Name="Extension";Expression={$_.Extension.Replace(".","")}},Mode,CreationTime,LastAccessTime,LastWriteTime,@{Name="Bytes";Expression={$_.Length}},@{Name="Share";Expression={$ShareName}},@{Name="MD5";Expression={$null}},@{Name="Keyword";Expression={$_.FullName -match $KeywordSearch | Out-Null; $matches[1]}} | export-csv -Path $OutputCSVFile -NoTypeInformation
            
            }
         
        }else{

            # File Structure Extract Keyword from Directory Part
            if($KeywordDirPart){
                if($MD5 -eq "True"){
                 
                    Get-ChildItem -Path $ScanBasePath -File -Recurse | Select-Object @{Name="FileName";Expression={$_.Name}},DirectoryName,@{Name="Extension";Expression={$_.Extension.Replace(".","")}},Mode,CreationTime,LastAccessTime,LastWriteTime,@{Name="Bytes";Expression={$_.Length}},@{Name="Share";Expression={$ShareName}},@{Name="MD5";Expression={(get-filehash -Path $_.fullname -Algorithm MD5).Hash}},@{Name="Keyword";Expression={($_.FullName.Split("\"))[$KeywordDirPart]}} | export-csv -Path $OutputCSVFile -NoTypeInformation
                }else{
                    Get-ChildItem -Path $ScanBasePath -File -Recurse | Select-Object @{Name="FileName";Expression={$_.Name}},DirectoryName,@{Name="Extension";Expression={$_.Extension.Replace(".","")}},Mode,CreationTime,LastAccessTime,LastWriteTime,@{Name="Bytes";Expression={$_.Length}},@{Name="Share";Expression={$ShareName}},@{Name="MD5";Expression={$null}},@{Name="Keyword";Expression={($_.FullName.Split("\"))[$KeywordDirPart]}} | export-csv -Path $OutputCSVFile -NoTypeInformation
                }
            }else{

                # File Structure No Keyword
                if($MD5 -eq "True"){
                
                    Get-ChildItem -Path $ScanBasePath -File -Recurse | Select-Object @{Name="FileName";Expression={$_.Name}},DirectoryName,@{Name="Extension";Expression={$_.Extension.Replace(".","")}},Mode,CreationTime,LastAccessTime,LastWriteTime,@{Name="Bytes";Expression={$_.Length}},@{Name="Share";Expression={$ShareName}},@{Name="MD5";Expression={(get-filehash -Path $_.fullname -Algorithm MD5).Hash}},@{Name="Keyword";Expression={$null}} | export-csv -Path $OutputCSVFile -NoTypeInformation
                }else{
                
                    Get-ChildItem -Path $ScanBasePath -File -Recurse | Select-Object @{Name="FileName";Expression={$_.Name}},DirectoryName,@{Name="Extension";Expression={$_.Extension.Replace(".","")}},Mode,CreationTime,LastAccessTime,LastWriteTime,@{Name="Bytes";Expression={$_.Length}},@{Name="Share";Expression={$ShareName}},@{Name="MD5";Expression={$null}},@{Name="Keyword";Expression={$null}} | export-csv -Path $OutputCSVFile -NoTypeInformation
                }
            }
        }
    
    }Catch{
        Write-Error $_
	    continue
    }
    
}


#endregion

#region MAIN ##

[Int64]$ErrorCount = 0 

# Check if XML configuration file parameter provided
if($XMLConfigurationFile -eq $null){ 
     Write-Error -Message "XMLConfigurationFile parameter not provided"
}
else
{
	
    # Execute Configuration
    Try{

        # Read Configuration File
        [xml]$XMLConfig = Get-Content $XMLConfigurationFile
        $ErrorLogPath = $XMLConfig.config.ErrorLogPath
        $FSInstances = $XMLConfig.config.FileSystem

        
        # Setup error log
        $errorfile = "{0}\{1}{2}.{3}" -f $ErrorLogPath,"Get-FileSystemMetadata_Error_",(Get-Date -Format yyyyMMdd-HHmmss),"txt"
        # Start Transcript
        if ($host.name -eq 'ConsoleHost'){Start-Transcript $errorfile}
        Write-Verbose -Message ((Get-Date -Format yyyyMMdd-HHmmss) + " : Error log file created " + $errorfile)
        

        foreach($fs in $FSInstances){
            Write-Verbose -Message ((Get-Date -Format yyyyMMdd-HHmmss) + " : Scanning " + ($fs.scanpath))
            if(($fs.KeywordSearch)){
                if($fs.MD5){
                    Start-ScanFileStructure -ScanBasePath $fs.scanpath -OutputCSVFile $fs.outputfile -KeywordSearch $fs.KeywordSearch -ShareName $fs.ShareName -MD5
                }else{
                    Start-ScanFileStructure -ScanBasePath $fs.scanpath -OutputCSVFile $fs.outputfile -KeywordSearch $fs.KeywordSearch -ShareName $fs.ShareName
                }
            }else{

                if(($fs.KeywordDirPart)){
                    if($fs.MD5){
                        Start-ScanFileStructure -ScanBasePath ($fs.scanpath) -OutputCSVFile ($fs.outputfile) -KeywordDirPart ($fs.KeywordDirPart) -ShareName $fs.ShareName -MD5
                    }else{
                        Start-ScanFileStructure -ScanBasePath ($fs.scanpath) -OutputCSVFile ($fs.outputfile) -KeywordDirPart ($fs.KeywordDirPart) -ShareName $fs.ShareName
                    }
                }else{
                    if($fs.MD5){
                        Start-ScanFileStructure -ScanBasePath ($fs.scanpath) -OutputCSVFile ($fs.outputfile) -ShareName $fs.ShareName -MD5
                    }else{
                        Start-ScanFileStructure -ScanBasePath ($fs.scanpath) -OutputCSVFile ($fs.outputfile) -ShareName $fs.ShareName
                    }
                }
            }
        }

    }
    Catch
    {
	    Write-Error $_

        $ErrorCount ++
	    continue
    }
    Finally
    {
        # Stop Transcript
        Write-Host ''
        Write-Host "-- Error Count: $ErrorCount ------ " 

        if ($host.name -eq 'ConsoleHost'){Stop-Transcript}
    }
}

#endregion

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

.SYNOPSIS

Collect filesystem metadata information to CSV file

.DESCRIPTION

Uses configuration information from xml file to scan file systems

.EXAMPLE

StartXml-FileSystemMetadata.ps1 -XMLConfigurationFile "C:\Temp\Get-FileSystemMetadata.xml"

.NOTES

Author: David Muegge

Requirements: PowerShell Version 4

Disclaimer

****************************************************************

* DO NOT USE IN A PRODUCTION ENVIRONMENT UNTIL YOU HAVE TESTED *

* THOROUGHLY IN A LAB ENVIRONMENT. USE AT YOUR OWN RISK. IF *

* YOU DO NOT UNDERSTAND WHAT THIS SCRIPT DOES OR HOW IT WORKS, *

* DO NOT USE IT OUTSIDE OF A SECURE, TEST SETTING. *

****************************************************************

[CmdletBinding()]

Param([Parameter(Mandatory=$false,Position=1)][String]$XMLConfigurationFile="C:\Temp\FileSystemMetadata_Config.xml")

#region Functions ##

function Start-ScanFileStructure{

[CmdletBinding()]

Param(

[Parameter(Mandatory=$true)][String]$ScanBasePath,

[Parameter(Mandatory=$true)][String]$OutputCSVFile,

[Parameter(Mandatory=$false)][String]$ShareName=$null,

[Parameter(Mandatory=$false)][String]$KeywordSearch=$null,

[Parameter(Mandatory=$false)][Int]$KeywordDirPart=$null,

[Parameter(Mandatory=$false)][Switch]$MD5

)

Try{

# File Structure Extract Keyword from Regex Search

if($KeywordSearch){

if($MD5 -eq "True"){

Get-ChildItem -Path $ScanBasePath -File -Recurse | Select-Object @{Name="FileName";Expression={$_.Name}},DirectoryName,@{Name="Extension";Expression={$_.Extension.Replace(".","")}},Mode,CreationTime,LastAccessTime,LastWriteTime,@{Name="Bytes";Expression={$_.Length}},@{Name="Share";Expression={$ShareName}},@{Name="MD5";Expression={(get-filehash -Path $_.fullname -Algorithm MD5).Hash}},@{Name="Keyword";Expression={$_.FullName -match $KeywordSearch | Out-Null; $matches[1]}} | export-csv -Path $OutputCSVFile -NoTypeInformation

}else{

Get-ChildItem -Path $ScanBasePath -File -Recurse | Select-Object @{Name="FileName";Expression={$_.Name}},DirectoryName,@{Name="Extension";Expression={$_.Extension.Replace(".","")}},Mode,CreationTime,LastAccessTime,LastWriteTime,@{Name="Bytes";Expression={$_.Length}},@{Name="Share";Expression={$ShareName}},@{Name="MD5";Expression={$null}},@{Name="Keyword";Expression={$_.FullName -match $KeywordSearch | Out-Null; $matches[1]}} | export-csv -Path $OutputCSVFile -NoTypeInformation

}

}else{

# File Structure Extract Keyword from Directory Part

if($KeywordDirPart){

if($MD5 -eq "True"){

Get-ChildItem -Path $ScanBasePath -File -Recurse | Select-Object @{Name="FileName";Expression={$_.Name}},DirectoryName,@{Name="Extension";Expression={$_.Extension.Replace(".","")}},Mode,CreationTime,LastAccessTime,LastWriteTime,@{Name="Bytes";Expression={$_.Length}},@{Name="Share";Expression={$ShareName}},@{Name="MD5";Expression={(get-filehash -Path $_.fullname -Algorithm MD5).Hash}},@{Name="Keyword";Expression={($_.FullName.Split("\"))[$KeywordDirPart]}} | export-csv -Path $OutputCSVFile -NoTypeInformation

}else{

Get-ChildItem -Path $ScanBasePath -File -Recurse | Select-Object @{Name="FileName";Expression={$_.Name}},DirectoryName,@{Name="Extension";Expression={$_.Extension.Replace(".","")}},Mode,CreationTime,LastAccessTime,LastWriteTime,@{Name="Bytes";Expression={$_.Length}},@{Name="Share";Expression={$ShareName}},@{Name="MD5";Expression={$null}},@{Name="Keyword";Expression={($_.FullName.Split("\"))[$KeywordDirPart]}} | export-csv -Path $OutputCSVFile -NoTypeInformation

}

}else{

# File Structure No Keyword

if($MD5 -eq "True"){

Get-ChildItem -Path $ScanBasePath -File -Recurse | Select-Object @{Name="FileName";Expression={$_.Name}},DirectoryName,@{Name="Extension";Expression={$_.Extension.Replace(".","")}},Mode,CreationTime,LastAccessTime,LastWriteTime,@{Name="Bytes";Expression={$_.Length}},@{Name="Share";Expression={$ShareName}},@{Name="MD5";Expression={(get-filehash -Path $_.fullname -Algorithm MD5).Hash}},@{Name="Keyword";Expression={$null}} | export-csv -Path $OutputCSVFile -NoTypeInformation

}else{

Get-ChildItem -Path $ScanBasePath -File -Recurse | Select-Object @{Name="FileName";Expression={$_.Name}},DirectoryName,@{Name="Extension";Expression={$_.Extension.Replace(".","")}},Mode,CreationTime,LastAccessTime,LastWriteTime,@{Name="Bytes";Expression={$_.Length}},@{Name="Share";Expression={$ShareName}},@{Name="MD5";Expression={$null}},@{Name="Keyword";Expression={$null}} | export-csv -Path $OutputCSVFile -NoTypeInformation

}

}Catch{

Write-Error $_

continue

}

#endregion

#region MAIN ##

[Int64]$ErrorCount = 0

# Check if XML configuration file parameter provided

if($XMLConfigurationFile -eq $null){

Write-Error -Message "XMLConfigurationFile parameter not provided"

}

else

{

# Execute Configuration

Try{

# Read Configuration File

[xml]$XMLConfig = Get-Content $XMLConfigurationFile

$ErrorLogPath = $XMLConfig.config.ErrorLogPath

$FSInstances = $XMLConfig.config.FileSystem

# Setup error log

$errorfile = "{0}\{1}{2}.{3}" -f $ErrorLogPath,"Get-FileSystemMetadata_Error_",(Get-Date -Format yyyyMMdd-HHmmss),"txt"

# Start Transcript

if ($host.name -eq 'ConsoleHost'){Start-Transcript $errorfile}

Write-Verbose -Message ((Get-Date -Format yyyyMMdd-HHmmss) + " : Error log file created " + $errorfile)

foreach($fs in $FSInstances){

Write-Verbose -Message ((Get-Date -Format yyyyMMdd-HHmmss) + " : Scanning " + ($fs.scanpath))

if(($fs.KeywordSearch)){

if($fs.MD5){

Start-ScanFileStructure -ScanBasePath $fs.scanpath -OutputCSVFile $fs.outputfile -KeywordSearch $fs.KeywordSearch -ShareName $fs.ShareName -MD5

}else{

Start-ScanFileStructure -ScanBasePath $fs.scanpath -OutputCSVFile $fs.outputfile -KeywordSearch $fs.KeywordSearch -ShareName $fs.ShareName

}

}else{

if(($fs.KeywordDirPart)){

if($fs.MD5){

Start-ScanFileStructure -ScanBasePath ($fs.scanpath) -OutputCSVFile ($fs.outputfile) -KeywordDirPart ($fs.KeywordDirPart) -ShareName $fs.ShareName -MD5

}else{

Start-ScanFileStructure -ScanBasePath ($fs.scanpath) -OutputCSVFile ($fs.outputfile) -KeywordDirPart ($fs.KeywordDirPart) -ShareName $fs.ShareName

}

}else{

if($fs.MD5){

Start-ScanFileStructure -ScanBasePath ($fs.scanpath) -OutputCSVFile ($fs.outputfile) -ShareName $fs.ShareName -MD5

}else{

Start-ScanFileStructure -ScanBasePath ($fs.scanpath) -OutputCSVFile ($fs.outputfile) -ShareName $fs.ShareName

}

Catch

{

Write-Error $_

$ErrorCount ++

continue

}

Finally

{

# Stop Transcript

Write-Host ''

Write-Host "-- Error Count: $ErrorCount ------ "

if ($host.name -eq 'ConsoleHost'){Stop-Transcript}

}

#endregion

The script requires a single argument which specifies the path to an XML file. This XML file is used to define configuration for the script. Here is an example of how you would call the script.

StartXml-FileSystemMetadata.ps1 -XMLConfigurationFile "C:\Temp\Get-FileSystemMetadata.xml"

1	StartXml-FileSystemMetadata.ps1 -XMLConfigurationFile "C:\Temp\Get-FileSystemMetadata.xml"

The XML configuration file defines the file system(s) to scan. Here is an example:

	C:\Temp
	<!-- Notes 
		XML properties for each file system to be scanned 
			scanpath = UNC or local file path to be scanned
				Note: account executing script must have full access to entire file system to scan metadata successfully
			KeywordSearch = optional regex to search path and poulate keyword column
			KeywordDirPart = optional int to poulate keyword column with specific Directory part
			ShareName = will populate Share field in output file
			MD5 = (True or False) if true an MD5 hash will be calculated and populate the MD5 output field
				Note: This can take extemely long to complete, use with caution and test on small set of data. Useful for identifying duplicate files.
			
		
		Examples:
		Simple Scan - Keyword column is not populated
		<FileSystem scanpath="" outputfile="" />
	
		Directory Scan capture directory part - Keyword column is populated with 3rd element(0 based array) of directory path
		<FileSystem scanpath="" outputfile="" KeywordDirPart="" />
		
		Directory Scan capture regex match - Keyword column is populated with regex match of directory path
		<FileSystem scanpath="" outputfile="" KeywordSearch="" />
		
		Regex Examples
		"C:\\Temp\\Users\\(.*)\\.*|C:\\Temp\\Users\\(.*)$"
        "C:\\Temp\\Users\\(.*[^\\])\\.*"
        "C:\\Temp\\Users\\([^\\]+)/g\\.*"
		
		
	Usage Examples
	<FileSystem scanpath="\\isilon\home" outputfile="C:\Data\Analysis\RTT_Lab\Collected_Data\FileSystem\homedata.csv" KeywordDirPart="4" ShareName="home" MD5="0"/>
	<FileSystem scanpath="\\isilon\cch" outputfile="C:\Data\Analysis\RTT_Lab\Collected_Data\FileSystem\cch.csv" ShareName="cch" MD5="False"/>
	<FileSystem scanpath="\\isilon\DataProtection" outputfile="C:\Data\Analysis\RTT_Lab\Collected_Data\FileSystem\DataProtection.csv" ShareName="DataProtection" MD5="False"/>
	<FileSystem scanpath="\\isilon\QHome" outputfile="C:\Data\Analysis\RTT_Lab\Collected_Data\FileSystem\QHome.csv" ShareName="QHome" MD5="False"/>
	<FileSystem scanpath="\\isilon\SmartQuotas" outputfile="C:\Data\Analysis\RTT_Lab\Collected_Data\FileSystem\SmartQuotas.csv" ShareName="SmartQuotas" MD5="False"/>
	<FileSystem scanpath="\\isilon\StorageTeam" outputfile="C:\Data\Analysis\RTT_Lab\Collected_Data\FileSystem\StorageTeam.csv" ShareName="StorageTeam" MD5="False"/>
	
	
	CSV Output example
	"FileName","DirectoryName","Extension","Mode","CreationTime","LastAccessTime","LastWriteTime","Bytes","Share","MD5","Keyword"
	"AppEvent.cls","\\FS01\Archive_Data\Code Library\!DAM Libraries\Classes\VB6\WriteNTEventLogs","cls","-----","8/17/2011 2:45:58 AM","1/7/2015 11:12:52 AM","7/20/2001 1:20:14 PM","19212","Archive_Data",,"Code Library"
	"Common.bas","\\FS01\Archive_Data\Code Library\!DAM Libraries\Classes\VB6\WriteNTEventLogs","bas","-----","8/17/2011 2:45:58 AM","1/7/2015 11:12:52 AM","5/6/2001 4:25:58 PM","9335","Archive_Data",,"Code Library"
	"FormatMsg.bas","\\FS01\Archive_Data\Code Library\!DAM Libraries\Classes\VB6\WriteNTEventLogs","bas","-----","8/17/2011 2:45:58 AM","1/7/2015 11:12:52 AM","5/6/2001 4:57:50 PM","10240","Archive_Data",,"Code Library"
	"MSSCCPRJ.SCC","\\FS01\Archive_Data\Code Library\!DAM Libraries\Classes\VB6\WriteNTEventLogs","SCC","--r--","8/17/2011 2:45:58 AM","1/7/2015 11:12:52 AM","12/30/2005 2:46:09 PM","192","Archive_Data",,"Code Library"
	"modError.vb","\\FS01\Archive_Data\Code Library\!DAM Libraries\Modules\VB.NET","vb","-----","8/17/2011 2:45:58 AM","1/7/2015 11:12:41 AM","6/24/2005 11:40:35 AM","3762","Archive_Data",,"Code Library"
	

	-->

C:\Temp

<!-- Notes

XML properties for each file system to be scanned

scanpath = UNC or local file path to be scanned

Note: account executing script must have full access to entire file system to scan metadata successfully

KeywordSearch = optional regex to search path and poulate keyword column

KeywordDirPart = optional int to poulate keyword column with specific Directory part

ShareName = will populate Share field in output file

MD5 = (True or False) if true an MD5 hash will be calculated and populate the MD5 output field

Note: This can take extemely long to complete, use with caution and test on small set of data. Useful for identifying duplicate files.

Examples:

Simple Scan - Keyword column is not populated

Directory Scan capture directory part - Keyword column is populated with 3rd element(0 based array) of directory path

Directory Scan capture regex match - Keyword column is populated with regex match of directory path

Regex Examples

"C:\\Temp\\Users\\(.*)\\.*|C:\\Temp\\Users\\(.*)$"

"C:\\Temp\\Users\\(.*[^\\])\\.*"

"C:\\Temp\\Users\\([^\\]+)/g\\.*"

Usage Examples

CSV Output example

"FileName","DirectoryName","Extension","Mode","CreationTime","LastAccessTime","LastWriteTime","Bytes","Share","MD5","Keyword"

"AppEvent.cls","\\FS01\Archive_Data\Code Library\!DAM Libraries\Classes\VB6\WriteNTEventLogs","cls","-----","8/17/2011 2:45:58 AM","1/7/2015 11:12:52 AM","7/20/2001 1:20:14 PM","19212","Archive_Data",,"Code Library"

"Common.bas","\\FS01\Archive_Data\Code Library\!DAM Libraries\Classes\VB6\WriteNTEventLogs","bas","-----","8/17/2011 2:45:58 AM","1/7/2015 11:12:52 AM","5/6/2001 4:25:58 PM","9335","Archive_Data",,"Code Library"

"FormatMsg.bas","\\FS01\Archive_Data\Code Library\!DAM Libraries\Classes\VB6\WriteNTEventLogs","bas","-----","8/17/2011 2:45:58 AM","1/7/2015 11:12:52 AM","5/6/2001 4:57:50 PM","10240","Archive_Data",,"Code Library"

"MSSCCPRJ.SCC","\\FS01\Archive_Data\Code Library\!DAM Libraries\Classes\VB6\WriteNTEventLogs","SCC","--r--","8/17/2011 2:45:58 AM","1/7/2015 11:12:52 AM","12/30/2005 2:46:09 PM","192","Archive_Data",,"Code Library"

"modError.vb","\\FS01\Archive_Data\Code Library\!DAM Libraries\Modules\VB.NET","vb","-----","8/17/2011 2:45:58 AM","1/7/2015 11:12:41 AM","6/24/2005 11:40:35 AM","3762","Archive_Data",,"Code Library"

-->

The example includes an explanation of optional attributes for the XML element(s). This allows control of how the data is organized and tagged, which provides more useful reporting options. The sample also shows several configuration examples and some example output. Once the metadata is collected into CSV files. It can be easily loaded into Splunk using the ad-hoc data load feature or a file system input on a forwarder. Here is an example of a file metadata record/event in Splunk.

One thing to note here is the event timestamp in Splunk. The Time field is derived from the modified time of the file. This was done on purpose, because in my experience doing file system assessments it is the only timestamp that is generally accurate. I have found many cases where last accessed is not available or incorrect. I have also seen many cases where the create date is incorrect. Sometimes the create date is more recent than the modified date and occasionally it is even in the future. Here is the Splunk props.conf sourcetype stanza I defined for the data. It includes TIME_FORMAT and TIMESTAMP_FIELDS to use the modified date for the _time field. It also uses MAX_DAYS_AGO since the modified date can go back many years.

[posh_file_metadata]
DATETIME_CONFIG = 
INDEXED_EXTRACTIONS = csv
KV_MODE = none
MAX_DAYS_AGO = 100000
NO_BINARY_CHECK = true
SHOULD_LINEMERGE = false
TIMESTAMP_FIELDS = LastWriteTime
TIME_FORMAT = %m/%d/%Y %I:%M:%S %p
category = Structured
description = File System MetaData
disabled = false
pulldown_type = true

[posh_file_metadata]

DATETIME_CONFIG =

INDEXED_EXTRACTIONS = csv

KV_MODE = none

MAX_DAYS_AGO = 100000

NO_BINARY_CHECK = true

SHOULD_LINEMERGE = false

TIMESTAMP_FIELDS = LastWriteTime

TIME_FORMAT = %m/%d/%Y %I:%M:%S %p

category = Structured

description = File System MetaData

disabled = false

pulldown_type = true

Once the data is loaded into Splunk here is the type of information we can easily find.

These are just some simple charts, but the metadata provides many reporting options. There are benefits of using Splunk besides the fact that it can be done without additional cost.

This eliminates the need to create tables and/or ETL processes for a relational database
The data can be loaded very easily compared to using a relational database
The dashboard can be reused very easily for new reports. Simply use a dedicated index and clean or delete/recreate as needed for updated reporting

If I were doing this in an environment I managed on a day to day basis. I would send the data directly to Splunk via the HTTP event collector. I’ll need to modify the collection code a bit to provide an example, but I’ll try to post a follow-up with that info.

I hope some folks find this useful.

Regards,

Dave

EMC Unity REST API and PowerShell

Posted on May 14, 2016
by dmuegge

Earlier this month at EMC World the new Unity storage array was unveiled. There are some cool new features with this product and the architecture is a bit different from the VNX. I like to experiment in my home lab and especially with PowerShell and REST APIs. So when I heard a Unity VSA would be available including the EMC Unity REST API. I was waiting for the EMC World release so I could get my hands on it.
Unity

I downloaded the EMC Unity VSA and set it up the first day of EMC World, then started some initial testing with the Unity REST API and PowerShell. I quickly found some distinct differences in the API when compared to the Isilon and XtremIO API’s. The first difference is the authentication mechanism which took some time to figure out. I found some quirks with the PowerShell Invoke-RestMethod and Invoke-WebRequest cmdlets. These cmdlets did not provide enough control over the web requests so I had to resort to creating my own with the .NET framework.

The EMC Unity REST API uses basic authentication via a request header, but also uses login session cookies. While trying to get this working I found I needed to use the .NET class System.Net.HttpWebRequest object for greater control. The two problems I had with the PowerShell cmdlets was the inability to control auto redirect and use request cookies. The use of request cookies is required to maintain session after initial login. The data required to create one of the cookies was returned in a response after an auto redirect so this had to be turned off to capture the information successfully.

The two cookies which have to be captured during the login process are MOD_AUTH_CAS_S and mod_sec_emc which are then used in all subsequent requests to the API. There are also a couple of additional cookies which are passed back and forth during the authentication process. I created a couple of functions to complete the login process, one of which is a recursive function which handles the auto redirects and collects the required login session cookies into global variables. The complete code is more than makes sense to show in this post, but the below example shows the main elements of building the request. This code is called recursively and collects the required session information to be passed in subsequent requests.

$URI = $Response.Headers.Item('Location')
$Cookies = New-Object -TypeName System.Net.CookieContainer
If($Global:JSESSIONID){$Cookies.Add($Global:JSESSIONID)}
If($Global:CASTGC){$Cookies.Add($Global:CASTGC)}
If($Global:mod_sec_emc){$Cookies.Add($Global:mod_sec_emc)}
If($Global:MOD_AUTH_CAS_S){$Cookies.Add($Global:MOD_AUTH_CAS_S)}
If($Global:CASPRIVACY){$Cookies.Add($Global:CASPRIVACY)}

$Request = [System.Net.HttpWebRequest]::Create($URI)
$Request.CookieContainer = $Cookies
$Request.AllowAutoRedirect = $false
$Request.Accept = "application/json"
$Request.ContentType = "application/json"
$Request.Headers.Add("X-EMC-REST-CLIENT","true")
$Request.Headers.Add('Authorization',"Basic $($EncodedPassword)")
$NewResponse = $Request.GetResponse()

$URI = $Response.Headers.Item('Location')

$Cookies = New-Object -TypeName System.Net.CookieContainer

If($Global:JSESSIONID){$Cookies.Add($Global:JSESSIONID)}

If($Global:CASTGC){$Cookies.Add($Global:CASTGC)}

If($Global:mod_sec_emc){$Cookies.Add($Global:mod_sec_emc)}

If($Global:MOD_AUTH_CAS_S){$Cookies.Add($Global:MOD_AUTH_CAS_S)}

If($Global:CASPRIVACY){$Cookies.Add($Global:CASPRIVACY)}

$Request = [System.Net.HttpWebRequest]::Create($URI)

$Request.CookieContainer = $Cookies

$Request.AllowAutoRedirect = $false

$Request.Accept = "application/json"

$Request.ContentType = "application/json"

$Request.Headers.Add("X-EMC-REST-CLIENT","true")

$Request.Headers.Add('Authorization',"Basic $($EncodedPassword)")

$NewResponse = $Request.GetResponse()

Once the login process is complete and the required session cookies are collected data requests to the EMC Unity REST API can be issued. The below code is an example of issuing a request for system information.

$Cookies = New-Object -TypeName System.Net.CookieContainer
If($Global:mod_sec_emc){$Cookies.Add($Global:mod_sec_emc)}
If($Global:MOD_AUTH_CAS_S){$Cookies.Add($Global:MOD_AUTH_CAS_S)}


$UriString = ($UriObject + "/instances/system?fields=id,
                                                     name,
                                                     model,
                                                     platform,
                                                     macAddress,
                                                     isEULAAccepted")

$URI = $Global:APIBaseUri + $UriString
$DataRequest = [System.Net.HttpWebRequest]::Create($URI)
$DataRequest.AllowAutoRedirect = $false
$DataRequest.CookieContainer = $Cookies
$DataRequest.Method = $Method
$DataRequest.Accept = "application/json"
$DataRequest.Headers.Add("X-EMC-REST-CLIENT","true")
$DataRequest.Headers.Add('Authorization',"Basic $($EncodedPassword)")
If($Global:EMC_CSRF_TOKEN){$DataRequest.Headers.Add('EMC-CSRF-TOKEN',$Global:EMC_CSRF_TOKEN)}
$DataResponse = $DataRequest.GetResponse()

if($DataResponse -ne $null) {
	$rs = $DataResponse.GetResponseStream();
	[System.IO.StreamReader] $sr = New-Object System.IO.StreamReader -argumentList $rs;
	[string] $results = $sr.ReadToEnd();
	Return ConvertFrom-Json $results
}

$Cookies = New-Object -TypeName System.Net.CookieContainer

If($Global:mod_sec_emc){$Cookies.Add($Global:mod_sec_emc)}

If($Global:MOD_AUTH_CAS_S){$Cookies.Add($Global:MOD_AUTH_CAS_S)}

$UriString = ($UriObject + "/instances/system?fields=id,

name,

model,

platform,

macAddress,

isEULAAccepted")

$URI = $Global:APIBaseUri + $UriString

$DataRequest = [System.Net.HttpWebRequest]::Create($URI)

$DataRequest.AllowAutoRedirect = $false

$DataRequest.CookieContainer = $Cookies

$DataRequest.Method = $Method

$DataRequest.Accept = "application/json"

$DataRequest.Headers.Add("X-EMC-REST-CLIENT","true")

$DataRequest.Headers.Add('Authorization',"Basic $($EncodedPassword)")

If($Global:EMC_CSRF_TOKEN){$DataRequest.Headers.Add('EMC-CSRF-TOKEN',$Global:EMC_CSRF_TOKEN)}

$DataResponse = $DataRequest.GetResponse()

if($DataResponse -ne $null) {

$rs = $DataResponse.GetResponseStream();

[System.IO.StreamReader] $sr = New-Object System.IO.StreamReader -argumentList $rs;

[string] $results = $sr.ReadToEnd();

Return ConvertFrom-Json $results

}

The above code also includes the header EMC_CSRF_TOKEN which is actually only required when doing a POST or DELETE. Another thing to notice in the code above is the use of the fields query string. The desired object properties to be returned must be specified using this method. The output is below.

id             : 0
name           : MLABUNT01
model          : UnityVSA
platform       : Tungsten_SingleSP
macAddress     : 00:50:56:A3:15:C0
isEULAAccepted : True

id : 0

name : MLABUNT01

model : UnityVSA

platform : Tungsten_SingleSP

macAddress : 00:50:56:A3:15:C0

isEULAAccepted : True

The EMC Unity REST API was a bit of a challenge to get started and took some time with fiddler to figure out. I will say though for the initial release, the API documentation was pretty good. Unity does seem pretty cool, easy to use, and it’s awesome to have the Unity VSA for experimentation. I will try to get some more comprehensive example code on GitHub soon.

Regards,

Dave

XtremIO PowerShell Module Update

Posted on September 29, 2015
by dmuegge

I have been working with version 4.0 of XtremIO. This version of XtremIO has an updated REST API which provides a bunch of new functionality. The MTSXtremIO PowerShell Module provides PowerShell management of the EMC XtremIO storage array.

This new version of the REST API includes new performance features, new snapshot functionality, and many new objects. I have updated the MTSXtremIO PowerShell module for XtremIO to support the new API and functionality. The current version of the XtremIO PowerShell module contains 85 cmdlets for working with XtremIO.

The first four Cmdlets are to setup a connection to the XtremIO XMS, which is how you connect to the REST API. One XMS can manage multiple XtremIO clusters.

Disable-CertificateValidation

Get-PasswordFromFile

New-PasswordFile

Set-XIOAPIConnectionInfo

There are 41 get Cmdlets to retrieve various XtremIO objects.

Get-XIOPerformance

Get-XIOAlert

Get-XIOAlertDefinition

Get-XIOAPITypes

Get-XIOBBU

Get-XIOBrick

Get-XIOCluster

Get-XIOConsistencyGroup

Get-XIOConsistencyGroupVolume

Get-XIODAE

Get-XIODAEController

Get-XIODAEPSU

Get-XIODataProtectionGroup

Get-XIOEmailNotifier

Get-XIOEvent

Get-XIOInfinibandSwitch

Get-XIOInitiator

Get-XIOInitiatorGroup

Get-XIOIscsiPortal

Get-XIOIscsiRoute

Get-XIOItem

Get-XIOLDAPConfiguration

Get-XIOLocalDisk

Get-XIOLunMap

Get-XIOPerformance

Get-XIOScheduler

Get-XIOSlot

Get-XIOSnapshot

Get-XIOSnapshotSet

Get-XIOSNMPNotifier

Get-XIOSSD

Get-XIOStorageController

Get-XIOStorageControllerPSU

Get-XIOSYRNotifier

Get-XIOSyslogNotifier

Get-XIOTag

Get-XIOTarget

Get-XIOTargetGroup

Get-XIOUserAccount

Get-XIOVolume

Get-XIOXenvs

Get-XIOXms

There are 11 Cmdlets for creating XtremIO objects.

New-XIOConsistencyGroup

New-XIOInitiator

New-XIOInitiatorGroup

New-XIOIscsiPortal

New-XIOIscsiRoute

New-XIOLunMap

New-XIOScheduler

New-XIOSnapshot

New-XIOTag

New-XIOUserAccount

New-XIOVolume

There are 13 Cmdlets for removing XtremIO objects.

Remove-XIOConsistencyGroup

Remove-XIOConsistencyGroupVolume

Remove-XIOInitiator

Remove-XIOInitiatorGroup

Remove-XIOIscsiPortal

Remove-XIOIscsiRoute

Remove-XIOLunMap

Remove-XIOScheduler

Remove-XIOSnapshot

Remove-XIOSnapshotSet

Remove-XIOTag

Remove-XIOUserAccount

Remove-XIOVolume

There are 16 Cmdlets for changing various XtremIO objects.

Add-XIOConsistencyGroupVolume

Set-XIOAlertDefinition

Set-XIOConsistencyGroup

Set-XIOEmailNotifier

Set-XIOInitiator

Set-XIOInitiatorGroup

Set-XIOLDAPConfiguration

Set-XIOScheduler

Set-XIOSnapshot

Set-XIOSNMPNotifier

Set-XIOSYRNotifier

Set-XIOSyslogNotifier

Set-XIOTag

Set-XIOTarget

Set-XIOVolume

Update-XIOSnapshot

This provides a good basic set of functionality and coverage over 100% of the API. There are still many improvements to be made but this provides a good base set of functionality. There are some items which are at the top of the list to address such as;

Improvements in pipeline support across functions. One known issue that exists with the cmdlets is the ability to pipe the output of a get into a set. There is currently a problem with the set cmdlet using property name as input correctly.
Complete multiple cluster support across all functions
Implement ShouldProcess in all new, set, remove functionality.

Other items on roadmap or under consideration

Complete Get-XIOPerformance function. This function only provides very raw object output and has an incomplete implementation.
Implement use of certificate based authentication
Test use of server certificates on XMS
Add informational output objects on new, set, and remove functions
Implement additional error handling to include informational messages

Get the latest version of the MTSXtremIO XtremIO PowerShell Module . I will work on pipeline improvements, multiple cluster support, and support for should process next. These items will provide a solid foundation for the module functionality. I also have a fair amount of help to complete. Please let me know what other items are missing and where things can be improved.

Regards,

Dave

XtremIO Snapshots SQL Server and PowerShell Part IV

Posted on September 23, 2015
by dmuegge

I have continued to work on the MTSXtremIO module and adding functionality for XtremIO 4.0. One interesting feature in 4.0 that provides even more benefit with snapshots is the ability to refresh a snapshot. With SQL server this saves some steps with LUN mapping and mounting volumes. I have done some testing using two methods to control the snapshot. I will talk about both methods and show a scripting example using each method.

The first method uses a PowerShell Cmdlet included in the PowerShell Toolkit in EMC Storage Integrator for Windows. The XtremIO 4.0 features are found in the latest version on the software, version 3.8. To use this method the EMC Storage Integrator for Windows must be installed on the machine to be used for scripting. This could potentially be each of the participating SQL Servers.

Here is an example script refreshing a database using the PowerShell Toolkit included with version 3.8 of EMC Storage Integrator for Windows.

Import-Module ESIPSToolkit
Import-Module MTSMSSQL

# Initial Snapshot Setup and LUN Mapping
$XtremIOCluster = 'XtremIO01'
$SourceVolume = 'DMSQLVol01'
$SourceDatabase = 'SnapTest01'
$TargetDatabaseName = 'SnapTest01_QA'
$TargetDeviceName = 'DMSQLVol01.Snap_20150923-102044'
$TargetSnapDeviceID = '514f0c53d2600159'
$VMName = 'DAMAnalysis'
$DriveLetter = 'K:'
$AccessPath = 'K:\'
$datapath = 'K:\SnapTest01.mdf'
$logpath = 'K:\SnapTest01_log.ldf'


# Detach Database
Set-DBDetach -SQLServer $VMName -dbname $SourceDatabase

# Get Disk Number
$OldDisk = Get-Disk | Where UniqueId -eq $TargetSnapDeviceID

# Offline Disk
Set-Disk -Number ($OldDisk.Number) -IsOffline $true

# Connect to XtremIO
$XioSystem = Get-EmcStorageSystem -ID $XtremIOCluster

# Refresh SnapShot
Restore-EmcXtremIOSnapshots -XioStorageSystem $XIOSystem -FromVolumeName $SourceVolume -ToVolumeName $TargetDeviceName

# Rescan Windows Disks
Update-HostStorageCache

# Get Disk Number
$NewDisk = Get-DIsk | Where UniqueId -eq $TargetSnapDeviceID

# Set mount and drive path 
Set-Disk -Number ($NewDisk.Number) -IsReadOnly $false
Set-Disk -Number ($NewDisk.Number) -IsOffline $false

Start-Sleep -Seconds 1
if(( -not (get-partition -DiskNumber ($NewDisk.Number)).DriveLetter -eq $DriveLetter)){
    Add-PartitionAccessPath -DiskNumber ($NewDisk.Number) -PartitionNumber 1 -AccessPath $AccessPath
}

# Verify Files and attach database
if ((Test-Path -Path $datapath) -and (Test-path -Path $logpath)){
 
	# Attach Database
	$DBAttachResult = Set-DBAttach -dbname $TargetDatabaseName -datapath $datapath -logpath $logpath
 
}

Import-Module ESIPSToolkit

Import-Module MTSMSSQL

# Initial Snapshot Setup and LUN Mapping

$XtremIOCluster = 'XtremIO01'

$SourceVolume = 'DMSQLVol01'

$SourceDatabase = 'SnapTest01'

$TargetDatabaseName = 'SnapTest01_QA'

$TargetDeviceName = 'DMSQLVol01.Snap_20150923-102044'

$TargetSnapDeviceID = '514f0c53d2600159'

$VMName = 'DAMAnalysis'

$DriveLetter = 'K:'

$AccessPath = 'K:\'

$datapath = 'K:\SnapTest01.mdf'

$logpath = 'K:\SnapTest01_log.ldf'

# Detach Database

Set-DBDetach -SQLServer $VMName -dbname $SourceDatabase

# Get Disk Number

$OldDisk = Get-Disk | Where UniqueId -eq $TargetSnapDeviceID

# Offline Disk

Set-Disk -Number ($OldDisk.Number) -IsOffline $true

# Connect to XtremIO

$XioSystem = Get-EmcStorageSystem -ID $XtremIOCluster

# Refresh SnapShot

Restore-EmcXtremIOSnapshots -XioStorageSystem $XIOSystem -FromVolumeName $SourceVolume -ToVolumeName $TargetDeviceName

# Rescan Windows Disks

Update-HostStorageCache

# Get Disk Number

$NewDisk = Get-DIsk | Where UniqueId -eq $TargetSnapDeviceID

# Set mount and drive path

Set-Disk -Number ($NewDisk.Number) -IsReadOnly $false

Set-Disk -Number ($NewDisk.Number) -IsOffline $false

Start-Sleep -Seconds 1

if(( -not (get-partition -DiskNumber ($NewDisk.Number)).DriveLetter -eq $DriveLetter)){

Add-PartitionAccessPath -DiskNumber ($NewDisk.Number) -PartitionNumber 1 -AccessPath $AccessPath

}

# Verify Files and attach database

if ((Test-Path -Path $datapath) -and (Test-path -Path $logpath)){

# Attach Database

$DBAttachResult = Set-DBAttach -dbname $TargetDatabaseName -datapath $datapath -logpath $logpath

}

The Second method is using an open source PowerShell Module I developed to manage XtremIO. It is a PowerShell interface to the XtremIO REST API. It provides a comprehensive PowerShell management interface including the snapshot refresh functionality in 4.0.

Here is an example refreshing a database using the Update-XIOSnapshot Cmdlet found in MTSXtremIO module.

Import-Module MTSXtremIO -Force
Import-Module MTSMSSQL

Disable-CertificateValidation
Set-XIOAPIConnectionInfo -username "admin" -passwordfile "C:\Users\dmuegge\pwd\XtremIO.txt" -hostname "XtremIO01"

# Initial Snapshot Setup and LUN Mapping 
$SourceVolume = 'DMSQLVol01'
$SourceDatabase = 'SnapTest01'
$TargetDatabaseName = 'SnapTest01_QA'
$TargetDeviceName = 'DMSQLVol01.Snap_QA'
$TargetSnapDeviceID = '514f0c53d2600159'
$VMName = 'DAMAnalysis'
$DriveLetter = 'K:'
$AccessPath = 'K:\'
$datapath = 'K:\SnapTest01.mdf'
$logpath = 'K:\SnapTest01_log.ldf'


# Detach Database
Set-DBDetach -SQLServer $VMName -dbname $SourceDatabase

# Get Disk Number
$OldDisk = Get-Disk | Where UniqueId -eq $TargetSnapDeviceID

# Offline Disk
Set-Disk -Number ($OldDisk.Number) -IsOffline $true

# Refresh SnapShot
Update-XIOSnapshot -SourceVol $SourceVolume -DestVol $TargetDeviceName

# Rescan Windows Disks
Update-HostStorageCache

# Get Disk Number
$NewDisk = Get-Disk | Where UniqueId -eq $TargetSnapDeviceID

# Set mount and drive path 
Set-Disk -Number ($NewDisk.Number) -IsReadOnly $false
Set-Disk -Number ($NewDisk.Number) -IsOffline $false

Start-Sleep -Seconds 1
if(( -not (get-partition -DiskNumber ($NewDisk.Number)).DriveLetter -eq $DriveLetter)){
    Add-PartitionAccessPath -DiskNumber ($NewDisk.Number) -PartitionNumber 1 -AccessPath $AccessPath
}

# Verify Files and attach database
if ((Test-Path -Path $datapath) -and (Test-path -Path $logpath)){
 
	# Attach Database
	$DBAttachResult = Set-DBAttach -dbname $TargetDatabaseName -datapath $datapath -logpath $logpath
 
}

Import-Module MTSXtremIO -Force

Import-Module MTSMSSQL

Disable-CertificateValidation

Set-XIOAPIConnectionInfo -username "admin" -passwordfile "C:\Users\dmuegge\pwd\XtremIO.txt" -hostname "XtremIO01"

# Initial Snapshot Setup and LUN Mapping

$SourceVolume = 'DMSQLVol01'

$SourceDatabase = 'SnapTest01'

$TargetDatabaseName = 'SnapTest01_QA'

$TargetDeviceName = 'DMSQLVol01.Snap_QA'

$TargetSnapDeviceID = '514f0c53d2600159'

$VMName = 'DAMAnalysis'

$DriveLetter = 'K:'

$AccessPath = 'K:\'

$datapath = 'K:\SnapTest01.mdf'

$logpath = 'K:\SnapTest01_log.ldf'

# Detach Database

Set-DBDetach -SQLServer $VMName -dbname $SourceDatabase

# Get Disk Number

$OldDisk = Get-Disk | Where UniqueId -eq $TargetSnapDeviceID

# Offline Disk

Set-Disk -Number ($OldDisk.Number) -IsOffline $true

# Refresh SnapShot

Update-XIOSnapshot -SourceVol $SourceVolume -DestVol $TargetDeviceName

# Rescan Windows Disks

Update-HostStorageCache

# Get Disk Number

$NewDisk = Get-Disk | Where UniqueId -eq $TargetSnapDeviceID

# Set mount and drive path

Set-Disk -Number ($NewDisk.Number) -IsReadOnly $false

Set-Disk -Number ($NewDisk.Number) -IsOffline $false

Start-Sleep -Seconds 1

if(( -not (get-partition -DiskNumber ($NewDisk.Number)).DriveLetter -eq $DriveLetter)){

Add-PartitionAccessPath -DiskNumber ($NewDisk.Number) -PartitionNumber 1 -AccessPath $AccessPath

}

# Verify Files and attach database

if ((Test-Path -Path $datapath) -and (Test-path -Path $logpath)){

# Attach Database

$DBAttachResult = Set-DBAttach -dbname $TargetDatabaseName -datapath $datapath -logpath $logpath

}

The way both of these scripts work relies on some initial setup of a snapshot copy mounted to a server. This could be the same or different servers. The diagram below shows the test scenario used in the example scripts above.

In either scenario above the copy process happens very quickly. In my tests it only took a few seconds. The only difference in the scripts above is the command to connect to the resources and the command to refresh the snapshot. Either method is simple and really makes database snapshots easy. The scripting is very straightforward and easy to understand.

The main benefit of the ESI Toolkit is it is officially supported by EMC. It does require an install an just provides some core provisioning functionality. The MTSXtremIO PowerShell Module does not require installation and can be used for many other XtremIO management and reporting tasks. It is an open source project located here.

Regards,

Dave

SQL Server, PowerShell, and XtremIO Snapshots Part 3 (VSS Native)

Posted on July 6, 2015
by dmuegge

During the writing of this post EMC announced the GA release of XtremIO 4.0 on July 2nd. The new documentation and the native VSS provider are now available on EMC support. This will provide the ability to script application consistent snapshots without using AppSync. Unfortunately we do not have our lab upgraded to 4.0 yet, but that will be coming soon and I will test the script and process soon. In the meantime I will talk about how this will provide another way to do application consistent snapshots for XtremIO. I will show the architecture and a mock script of how I think it will work at this point.

In order to use the XtremIO VSS provider it must be installed on the server where we want to do an application consistent snapshot. The install is downloaded from EMC support, at the time of this post the file name for the latest version is XtremIOVSSProvider-1.0.8.msi. After installation the connection to XtremIO is configured through the control panel using the applet.

The control panel applet is only to configure the connection to the XtremIO XMS.

The VSS Provider installation can be verified by opening a command line as administrator and typing vssadmin list providers. This shows us the XtremIO VSS Provider.

The process to use an XtremIO snapshot for a database copy using the VSS provider is very similar to the process used in part 1 of this blog series. The primary difference is the VSS Provider is called to create the XtremIO snapshot. The following image shows the basic VSS architecture.

The test environment is a SQL Server virtual machine on vSphere. The SnapTest01 volume is on a 50GB RDM on XtremIO and the SnapTest01_QA volume is a snapshot of the SnapTest01 volume.

The example script will show the process to refresh the QA volume with a new snapshot copy. The script is almost identical to the part 1 script. The first step is to load a few PowerShell modules, define some constants, and connect to VCenter and XtremIO. This is done by using the PowerCli and a function from my MTSXtremIO module, read about that here. This function uses the XtremIO REST API to create the snapshot. I also use a couple of other modules with some of my common functions and a NTFS Security Module which I did not write. I will put links to those at the end of the post.

Import-Module MTSXtremIO
Import-Module NTFSSecurity
Import-Module MTSAuthentication 
 
# Setup PowerCli and VCenter Connection
Set-Variable -Name vCenterUserPasswordFile -Value C:\!Passwords\VCenter01.txt
if(-not (get-pssnapin | Where-Object { $_.Name -eq 'VMware.VimAutomation.Core'})){add-pssnapin -Name VMware.VimAutomation.Core}
$vCenterUserPassword = Get-PasswordFromFile -FullPath $vCenterUserPasswordFile -AsSecureString
$VCCred = Set-WindowsAuthenticationCredential -userid 'LAB\vcuser' -password $vCenterUserPassword
$VCConnectResult = Connect-VIServer 'vcenter.lab.com' -Credential $VCCred
 
# Load SQL Server Management Objects
$LoadResult = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") 
 
# XtremIO Connection
Disable-CertificateValidation
Set-XIOAPIConnectionInfo -username "admin" -passwordfile "C:\!Passwords\XtremIO.txt" -baseuri "https://192.168.1.100/api/json/types/"
 

# Define a few constants for this operation
$SnapPrefix = 'SQLVol01_SNAP_'
$SourceVolume = 'SQLVol01'
$TargetDatabaseName = 'SnapTest01_QA'
$TargetHardDiskName = 'Hard Disk 7'
$VMName = 'Server01'
$TargetSearchString = 'esx*'
$datapath = 'E:\SnapTest01.mdf'
$logpath = 'E:\SnapTest01_log.ldf'
$DiskNumber = 6
$PartitionNumber  = 1
$SnapSearchString = '_SNAP_*'
$MountPath = 'E:'
$ScriptName = 'SQLDShadow'
$MetadataFile = 'E:\SnapMeta'
$VerboseMode = 'ON'

Import-Module MTSXtremIO

Import-Module NTFSSecurity

Import-Module MTSAuthentication

# Setup PowerCli and VCenter Connection

Set-Variable -Name vCenterUserPasswordFile -Value C:\!Passwords\VCenter01.txt

if(-not (get-pssnapin | Where-Object { $_.Name -eq 'VMware.VimAutomation.Core'})){add-pssnapin -Name VMware.VimAutomation.Core}

$vCenterUserPassword = Get-PasswordFromFile -FullPath $vCenterUserPasswordFile -AsSecureString

$VCCred = Set-WindowsAuthenticationCredential -userid 'LAB\vcuser' -password $vCenterUserPassword

$VCConnectResult = Connect-VIServer 'vcenter.lab.com' -Credential $VCCred

# Load SQL Server Management Objects

$LoadResult = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

# XtremIO Connection

Disable-CertificateValidation

Set-XIOAPIConnectionInfo -username "admin" -passwordfile "C:\!Passwords\XtremIO.txt" -baseuri "https://192.168.1.100/api/json/types/"

# Define a few constants for this operation

$SnapPrefix = 'SQLVol01_SNAP_'

$SourceVolume = 'SQLVol01'

$TargetDatabaseName = 'SnapTest01_QA'

$TargetHardDiskName = 'Hard Disk 7'

$VMName = 'Server01'

$TargetSearchString = 'esx*'

$datapath = 'E:\SnapTest01.mdf'

$logpath = 'E:\SnapTest01_log.ldf'

$DiskNumber = 6

$PartitionNumber = 1

$SnapSearchString = '_SNAP_*'

$MountPath = 'E:'

$ScriptName = 'SQLDShadow'

$MetadataFile = 'E:\SnapMeta'

$VerboseMode = 'ON'

The example above loads module dependencies and connects to VCenter and XtremIO. The SQL Management Objects are loaded to provide SQL Server functionality.

The next step is to detach the current QA database copy, remove the virtual hard disk, and remove snapshots.

# Detach current copy of database
$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)
$srv.DetachDatabase($dbname,$False,$False)

# Remove Disk From VM
#Remove-HardDisk -DeletePermanently -Confirm $false
Remove-HardDisk -HardDisk (Get-HardDisk -VM $VMName -Name $TargetHardDiskName)

# Rescan Windows disks
Update-HostStorageCache

# Delete LUNMAP for existing snap
$LMResult = Get-XIOLunMap | Where-Object vol-name -like ($SourceVolume + $SnapSearchString) | Foreach-object{Remove-XIOLunMap -Name $_.'mapping-id'[1]}

# Rescan VMware HBA's
$CLResult = Get-Cluster | Get-VMHost | Get-VMHostStorage -RescanAllHBA

# Remove last snapshots
$SPResult = Get-XIOSnapshot | Where-Object Name -like ($SourceVolume + $SnapSearchString) | ForEach-Object {Remove-XIOSnapshot -VolName $_.Name}

# Detach current copy of database

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)

$srv.DetachDatabase($dbname,$False,$False)

# Remove Disk From VM

#Remove-HardDisk -DeletePermanently -Confirm $false

Remove-HardDisk -HardDisk (Get-HardDisk -VM $VMName -Name $TargetHardDiskName)

# Rescan Windows disks

Update-HostStorageCache

# Delete LUNMAP for existing snap

$LMResult = Get-XIOLunMap | Where-Object vol-name -like ($SourceVolume + $SnapSearchString) | Foreach-object{Remove-XIOLunMap -Name $_.'mapping-id'[1]}

# Rescan VMware HBA's

$CLResult = Get-Cluster | Get-VMHost | Get-VMHostStorage -RescanAllHBA

# Remove last snapshots

$SPResult = Get-XIOSnapshot | Where-Object Name -like ($SourceVolume + $SnapSearchString) | ForEach-Object {Remove-XIOSnapshot -VolName $_.Name}

The example above uses SQL Management Objects to access SQL and detach the database. It the uses the VMware PowerCli to remove the RDM from the virtual machine. Then connects to the XtremIO via REST API and deletes snapshots

Now we are ready to create a new snapshot, add it to the lunmap, add the disk to the vm, and attach the database.

# Create snapshot of source volume
$Snapname = ($SnapPrefix + (Get-Date -Format yyyyMMdd-HHmmss))
$SNResult = New-XIOSnapshot -VolName $SourceVolume -SnapName $Snapname -FolderID $SnapFolder

# Application Consistent Method - XtremIO VSS Provider
$dsh = "./$ScriptName.dsh"
'RESET',
'SET CONTEXT PERSISTENT',
'SET OPTION TRANSPORTABLE',
"SET METADATA $MetadataFile",
"SET VERBOSE $VerboseMode",
'BEGIN BACKUP',
"ADD VOLUME $Volume ALIAS $ShadowCopyAlias PROVIDER {b57190af-454a-4dd0-8afd-e57facd5d9af}",
'CREATE',
'END BACKUP' | Set-Content $dsh
DISKSHADOW /s $dsh
Remove-Item $dsh

# Create Lun Map for snap volume
$LMResult = Get-XIOInitiatorGroup | Where-Object Name -Like $TargetSearchString | Select-Object name,index | foreach-object{New-XIOLunMap -Name $Snapname -InitiatorGroup $_.index}

# Rescan VMware HBA's
$CLResult = Get-Cluster | Get-VMHost | Get-VMHostStorage -RescanAllHBA

# Add Disk to VM
$vm = Get-VM -Name $VMName
$vmhost = Get-VMHost -Name $vm.VMHost
$deviceName = ($vmhost | Get-ScsiLun | Where-Object {$_.CanonicalName -match ('naa.' + (Get-XIOVolume | Where-Object name -EQ $Snapname).'naa-name')})[0].ConsoleDeviceName
$HDResult = New-HardDisk -DeviceName $deviceName -VM $vm -DiskType RawPhysical

# Rescan Windows
Update-HostStorageCache

# Set mount and drive path 
Set-Disk -Number $DiskNumber -IsReadOnly $false
Set-Disk -Number $DiskNumber -IsOffline $false
Start-Sleep -Seconds 1
if(( -not (get-partition -DiskNumber $DiskNumber).DriveLetter -eq $MountPath)){
	Add-PartitionAccessPath -DiskNumber $DiskNumber -PartitionNumber $PartitionNumber -AccessPath $MountPath
}
 
# Verify Files and Attach Database
Enable-NTFSAccessInheritance -Path $datapath
Enable-NTFSAccessInheritance -Path $logpath
if ((Test-Path -Path $datapath) -and (Test-path -Path $logpath)){

	# Attach Database
	$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)
	$db = New-Object Microsoft.SqlServer.Management.Smo.Database
	$db = $srv.Databases.Item($dbname)
	$sc = new-object System.collections.specialized.stringcollection 
	$sc.Add($datapath)
	$sc.Add($logpath)
	$srv.AttachDatabase($dbname,$sc,[Microsoft.SqlServer.Management.Smo.AttachOptions]::None)

}

# Create snapshot of source volume

$Snapname = ($SnapPrefix + (Get-Date -Format yyyyMMdd-HHmmss))

$SNResult = New-XIOSnapshot -VolName $SourceVolume -SnapName $Snapname -FolderID $SnapFolder

# Application Consistent Method - XtremIO VSS Provider

$dsh = "./$ScriptName.dsh"

'RESET',

'SET CONTEXT PERSISTENT',

'SET OPTION TRANSPORTABLE',

"SET METADATA $MetadataFile",

"SET VERBOSE $VerboseMode",

'BEGIN BACKUP',

"ADD VOLUME $Volume ALIAS $ShadowCopyAlias PROVIDER {b57190af-454a-4dd0-8afd-e57facd5d9af}",

'CREATE',

'END BACKUP' | Set-Content $dsh

DISKSHADOW /s $dsh

Remove-Item $dsh

# Create Lun Map for snap volume

$LMResult = Get-XIOInitiatorGroup | Where-Object Name -Like $TargetSearchString | Select-Object name,index | foreach-object{New-XIOLunMap -Name $Snapname -InitiatorGroup $_.index}

# Rescan VMware HBA's

$CLResult = Get-Cluster | Get-VMHost | Get-VMHostStorage -RescanAllHBA

# Add Disk to VM

$vm = Get-VM -Name $VMName

$vmhost = Get-VMHost -Name $vm.VMHost

$deviceName = ($vmhost | Get-ScsiLun | Where-Object {$_.CanonicalName -match ('naa.' + (Get-XIOVolume | Where-Object name -EQ $Snapname).'naa-name')})[0].ConsoleDeviceName

$HDResult = New-HardDisk -DeviceName $deviceName -VM $vm -DiskType RawPhysical

# Rescan Windows

Update-HostStorageCache

# Set mount and drive path

Set-Disk -Number $DiskNumber -IsReadOnly $false

Set-Disk -Number $DiskNumber -IsOffline $false

Start-Sleep -Seconds 1

if(( -not (get-partition -DiskNumber $DiskNumber).DriveLetter -eq $MountPath)){

Add-PartitionAccessPath -DiskNumber $DiskNumber -PartitionNumber $PartitionNumber -AccessPath $MountPath

}

# Verify Files and Attach Database

Enable-NTFSAccessInheritance -Path $datapath

Enable-NTFSAccessInheritance -Path $logpath

if ((Test-Path -Path $datapath) -and (Test-path -Path $logpath)){

# Attach Database

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)

$db = New-Object Microsoft.SqlServer.Management.Smo.Database

$db = $srv.Databases.Item($dbname)

$sc = new-object System.collections.specialized.stringcollection

$sc.Add($datapath)

$sc.Add($logpath)

$srv.AttachDatabase($dbname,$sc,[Microsoft.SqlServer.Management.Smo.AttachOptions]::None)

}

The above example creates a snapshot via the VSS provider and maps the volume to the host using the XtremIO REST API. It also rescans the disks and then adds the RDM to the virtual machine. Then the database is attached using SQL SMO.

Although I have not been able to test this code it as we need to upgrade XtremIO first. I had hoped the VSS provider would work with 3.0 but unfortunately I received the following message when I tried it.

“The registered provider does not support shadow copy of this volume”

Hopefully this example should be pretty close.

Regards,

Dave

MTSXtremIO Module
NTFSSecurity Module
MTSAuthentication Module

SQL Server, PowerShell, and XtremIO Snapshots Part 2 (EMC AppSync)

Posted on July 1, 2015
by dmuegge

In my last post I talked about using PowerShell to do crash consistent snapshots on XtremIO. Then mount the snapshot and use for a secondary SQL Server database copy.

In this post I will talk about scripting application consistent XtremIO snapshots using EMC AppSync and EMC Storage Integrator for Windows (ESI). I will also talk about how the process and architecture differs from part 1 where we just did crash consistent snapshots. EMC AppSync is a server application that can create and manage snapshots for application and virtualization environments using EMC storage arrays. It uses a Host plug-in which allows the AppSync server to control host operations. ESI is a windows application and set of tools which provides EMC management capabilities from a windows machine. One of the features of ESI is a PowerShell toolkit that allows controlling EMC arrays and EMC AppSync using PowerShell CmdLets.

We’ll use the same basic scenario as the last time, but utilizing EMC AppSync. The test environment is a SQL Server virtual machine on vSphere. The SnapTest01 volume is on a 50GB RDM on XtremIO and the SnapTest01_QA volume is a snapshot of the SnapTest01 volume.

With our previous test the script would generally run on the SQL Server to perform the snapshot operations. With the AppSync application the script does not have a requirement to run locally as the agent performs local operations. Here is a diagram of the architecture.

In this scenario the script would run on the ESI host using a windows account that has SQL admin rights on the SQL server. This is required to attach the database. The latest version of appsync as of this writing (2.2) does not have the ability to attach a copy from an XtremIO volume. Here is the statement from the admin guide on page 105.

“This version of Appsync does not support automated restore of copies on XtremIO. Learn

to use AppSync and XtremIO to restore SQL Server databases to a point in time with a

semi-manual restore process.”

However, this is easily worked around using PowerShell and SQL SMO to fill in the gap. The end script is still simpler than in part 1 because AppSync does a lot of the tedious work for us and we get application consistent snapshots to boot. The other benefit of the architecture is it allows you to use a central host to control all of your database refresh operations. Here is the first part of the script which loads the ESI module, SQL SMO, and defines some constants.

Import-Module ESIPSToolkit

# Load SQL Server Management Objects
$LoadResult = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") 

$SourceVolume = 'SQLVol01'
$SourceDatabase = 'SnapTest01'
$TargetDatabaseName = 'SnapTest01_QA'
$VMName = 'Server01'
$AppSYncServicePlan = 'sql bronze'
$MountPath = 'C:\AppSyncMounts\Server01'
$datapath = 'C:\AppSyncMounts\Server01\FDrive\SnapTest01.mdf'
$logpath = 'C:\AppSyncMounts\Server01\FDrive\SnapTest01_log.ldf'

Import-Module ESIPSToolkit

# Load SQL Server Management Objects

$LoadResult = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$SourceVolume = 'SQLVol01'

$SourceDatabase = 'SnapTest01'

$TargetDatabaseName = 'SnapTest01_QA'

$VMName = 'Server01'

$AppSYncServicePlan = 'sql bronze'

$MountPath = 'C:\AppSyncMounts\Server01'

$datapath = 'C:\AppSyncMounts\Server01\FDrive\SnapTest01.mdf'

$logpath = 'C:\AppSyncMounts\Server01\FDrive\SnapTest01_log.ldf'

The next section detaches the database, initializes the AppSync Objects, gets and dismounts the current snapshot.

# Detach current copy of database
$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($VMName)
$srv.DetachDatabase($TargetDatabaseName,$False,$False)

# Initialize AppSync Objects
$ReplService = Get-EmcReplicationService -ID emcappsync
$SvrInstance = Get-EmcAppSyncSqlServerInstance -AppSyncSystem $ReplService | Where-Object Name -eq $VMName
$DbInstance = Get-EmcAppSyncSqlServerDatabase -AppSyncSystem $ReplService -UserDatabase $SOurceDatabase
$SPlan = Get-EmcAppSyncServicePlan -AppSyncSystem $ReplService | Where-Object Name -eq $AppSYncServicePlan
$SQLServerHost = Get-EmcSqlServer -HostNamesOrIPAddresses $VMName
$AppSYncHost = Get-EmcAppSyncRegisteredHost -ID $VMName -AppSyncSystem $ReplService

# Unmount Current Copy
$DBCopy = Get-EmcAppSyncCopy -SqlServerDatabase $DbInstance | Where {(($_.CopyMountStatus -eq 'Mounted') -and ($_.Name -eq $SOurceDatabase))} | Sort -Desc | Select -First 1
Dismount-EmcAppSyncCopy -SqlServerDatabaseCopy $DBCopy -Silent -Confirm -Force

# Detach current copy of database

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($VMName)

$srv.DetachDatabase($TargetDatabaseName,$False,$False)

# Initialize AppSync Objects

$ReplService = Get-EmcReplicationService -ID emcappsync

$SvrInstance = Get-EmcAppSyncSqlServerInstance -AppSyncSystem $ReplService | Where-Object Name -eq $VMName

$DbInstance = Get-EmcAppSyncSqlServerDatabase -AppSyncSystem $ReplService -UserDatabase $SOurceDatabase

$SPlan = Get-EmcAppSyncServicePlan -AppSyncSystem $ReplService | Where-Object Name -eq $AppSYncServicePlan

$SQLServerHost = Get-EmcSqlServer -HostNamesOrIPAddresses $VMName

$AppSYncHost = Get-EmcAppSyncRegisteredHost -ID $VMName -AppSyncSystem $ReplService

# Unmount Current Copy

$DBCopy = Get-EmcAppSyncCopy -SqlServerDatabase $DbInstance | Where {(($_.CopyMountStatus -eq 'Mounted') -and ($_.Name -eq $SOurceDatabase))} | Sort -Desc | Select -First 1

Dismount-EmcAppSyncCopy -SqlServerDatabaseCopy $DBCopy -Silent -Confirm -Force

The detach database operation uses SQL Server Management Objects and the AppSync objects are all created using the ESI PowerShell Toolkit CmdLets.

The next section creates a new snapshot mounts it to the host and attaches the database.

# Create snapshot of source volume/database
$DBCopy = New-EmcAppSyncSqlServerDatabaseCopy -SqlServerDatabase $DbInstance -ServicePlan $SPlan

# Mount snapshot of source volume/database
Mount-EmcAppSyncSqlServerDatabaseCopy -SqlServerDatabaseCopy $DBCopy -TargetHost $AppSYncHost -MountPath $MountPath

# Verify Files and attach database
if ((Test-Path -Path $datapath) -and (Test-path -Path $logpath)){

	# Attach Database
	$DBAttachResult = Set-DBAttach -dbname $TargetDatabaseName -datapath $datapath -logpath $logpath

}

# Create snapshot of source volume/database

$DBCopy = New-EmcAppSyncSqlServerDatabaseCopy -SqlServerDatabase $DbInstance -ServicePlan $SPlan

# Mount snapshot of source volume/database

Mount-EmcAppSyncSqlServerDatabaseCopy -SqlServerDatabaseCopy $DBCopy -TargetHost $AppSYncHost -MountPath $MountPath

# Verify Files and attach database

if ((Test-Path -Path $datapath) -and (Test-path -Path $logpath)){

# Attach Database

$DBAttachResult = Set-DBAttach -dbname $TargetDatabaseName -datapath $datapath -logpath $logpath

}

This also uses ESI for the AppSync operations and SQL SMO for the database attach.

An important point to note is we did not have to create steps for all of the individual disk operations and LUN mapping. The AppSync server and host agent took care of the details for us.

That is it for application consistent snapshots with AppSync. Next time I will talk about the method we will be able to use when XtremIO 4.0 gets here and the native VSS Provider is available.

Regards,

Dave

SQL Server, PowerShell, and XtremIO Snapshots Part 1

Posted on June 28, 2015
by dmuegge

XtremIO is becoming a popular platform for SQL Server. It performs excellent and has a great space benefit for database copies when using XtremIO snapshots. I have had a few customers asking questions about scripting snapshots for SQL Server on XtremIO using PowerShell.

Most Windows administrators these days are using PowerShell and many SQL DBA’s are also starting to use it. I decided to setup EMC AppSync with XtremIO in our lab, do a little testing, and create some PowerShell scripts to help our customers get started. My plan was to test two different scenarios, one for crash consistent and one for application consistent snapshots with EMC AppSync, but EMC announced a new native VSS provider with XtremIO 4.0 so now there will also be another way to do application consistent snapshots when 4.0 is released. This post will cover creating a crash consistent copy of a SQL database volume using XtremIO snapshots and mounting the snapshot to create a secondary QA database. This method will use the XtremIO REST API to create a snapshot of the source volume. PowerShell will be used to execute the required steps on XtremIO, VMware, and Windows.

The test environment is a SQL Server virtual machine on vSphere. The SnapTest01 volume is on a 50GB RDM on XtremIO and the SnapTest01_QA volume is a snapshot of the SnapTest01 volume.

SQL_Server

The example script will show the process to refresh the QA volume with a new snapshot copy. Here is the basic logical flow of the process.

SnapSshot

The first step is to load a few PowerShell modules, define some constants, and connect to VCenter and XtremIO. This is done by using the PowerCli and a function from my MTSXtremIO module, read about that here. This function uses the XtremIO REST API to create the snapshot. I also use a couple of other modules with some of my common functions and a NTFS Security Module which I did not write. I will put links to those at the end of the post.

Import-Module MTSXtremIO
Import-Module NTFSSecurity
Import-Module MTSAuthentication 

# Setup PowerCli and VCenter Connection
Set-Variable -Name vCenterUserPasswordFile -Value C:\!Passwords\VCenter01.txt
if(-not (get-pssnapin | Where-Object { $_.Name -eq 'VMware.VimAutomation.Core'})){add-pssnapin -Name VMware.VimAutomation.Core}
$vCenterUserPassword = Get-PasswordFromFile -FullPath $vCenterUserPasswordFile -AsSecureString
$VCCred = Set-WindowsAuthenticationCredential -userid 'LAB\vcuser' -password $vCenterUserPassword
$VCConnectResult = Connect-VIServer 'vcenter.lab.com' -Credential $VCCred

# Load SQL Server Management Objects
$LoadResult = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") 

# XtremIO Connection
Disable-CertificateValidation
Set-XIOAPIConnectionInfo -username "admin" -passwordfile "C:\!Passwords\XtremIO.txt" -baseuri "https://192.168.1.100/api/json/types/"


# Define a few constants for this operation
$SnapPrefix = 'SQLVol01_SNAP_'
$SourceVolume = 'SQLVol01'
$TargetDatabaseName = 'SnapTest01_QA'
$TargetHardDiskName = 'Hard Disk 6'
$VMName = 'Server01'
$MountPath = "E:"
$DiskNumber = 6
$PartitionNumber = 1
$TargetSearchString = 'esx*' 
$datapath = "E:\SnapTest01.mdf"
$logpath = "E:\SnapTest01_log.ldf"

Import-Module MTSXtremIO

Import-Module NTFSSecurity

Import-Module MTSAuthentication

# Setup PowerCli and VCenter Connection

Set-Variable -Name vCenterUserPasswordFile -Value C:\!Passwords\VCenter01.txt

if(-not (get-pssnapin | Where-Object { $_.Name -eq 'VMware.VimAutomation.Core'})){add-pssnapin -Name VMware.VimAutomation.Core}

$vCenterUserPassword = Get-PasswordFromFile -FullPath $vCenterUserPasswordFile -AsSecureString

$VCCred = Set-WindowsAuthenticationCredential -userid 'LAB\vcuser' -password $vCenterUserPassword

$VCConnectResult = Connect-VIServer 'vcenter.lab.com' -Credential $VCCred

# Load SQL Server Management Objects

$LoadResult = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

# XtremIO Connection

Disable-CertificateValidation

Set-XIOAPIConnectionInfo -username "admin" -passwordfile "C:\!Passwords\XtremIO.txt" -baseuri "https://192.168.1.100/api/json/types/"

# Define a few constants for this operation

$SnapPrefix = 'SQLVol01_SNAP_'

$SourceVolume = 'SQLVol01'

$TargetDatabaseName = 'SnapTest01_QA'

$TargetHardDiskName = 'Hard Disk 6'

$VMName = 'Server01'

$MountPath = "E:"

$DiskNumber = 6

$PartitionNumber = 1

$TargetSearchString = 'esx*'

$datapath = "E:\SnapTest01.mdf"

$logpath = "E:\SnapTest01_log.ldf"

The example above loads module dependencies and connects to VCenter and XtremIO. The SQL Management Objects are loaded to provide SQL Server functionality.

The next step is to detach the current QA database copy, remove the virtual hard disk, and remove snapshots. We will also do a couple of rescans in the process and remove the old snapshots.

# Detach current copy of database
$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)
$srv.DetachDatabase($dbname,$False,$False)

# Remove Disk From VM
Remove-HardDisk -HardDisk (Get-HardDisk -VM $VMName -Name $TargetHardDiskName) -DeletePermanently -Confirm

# Rescan Windows disks
Update-HostStorageCache

# Delete LUNMAP for existing snap
$LMResult = Get-XIOLunMap | Where-Object vol-name -like ($SourceVolume + $SnapSearchString) | Foreach-object{Remove-XIOLunMap -Name $_.'mapping-id'[1]}

# Rescan VMware HBA's
$CLResult = Get-Cluster | Get-VMHost | Get-VMHostStorage -RescanAllHBA

# Remove last snapshots
$SPResult = Get-XIOSnapshot | Where-Object Name -like ($SourceVolume + $SnapSearchString) | ForEach-Object {Remove-XIOSnapshot -VolName $_.Name}

# Detach current copy of database

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)

$srv.DetachDatabase($dbname,$False,$False)

# Remove Disk From VM

Remove-HardDisk -HardDisk (Get-HardDisk -VM $VMName -Name $TargetHardDiskName) -DeletePermanently -Confirm

# Rescan Windows disks

Update-HostStorageCache

# Delete LUNMAP for existing snap

$LMResult = Get-XIOLunMap | Where-Object vol-name -like ($SourceVolume + $SnapSearchString) | Foreach-object{Remove-XIOLunMap -Name $_.'mapping-id'[1]}

# Rescan VMware HBA's

$CLResult = Get-Cluster | Get-VMHost | Get-VMHostStorage -RescanAllHBA

# Remove last snapshots

$SPResult = Get-XIOSnapshot | Where-Object Name -like ($SourceVolume + $SnapSearchString) | ForEach-Object {Remove-XIOSnapshot -VolName $_.Name}

Now we are ready to create a new snapshot, add it to the lunmap, add the disk to the vm, and attach the database.

# Create snapshot of source volume
$Snapname = ($SnapPrefix + (Get-Date -Format yyyyMMdd-HHmmss))
$SNResult = New-XIOSnapshot -VolName $SourceVolume -SnapName $Snapname -FolderID $SnapFolder

# Create Lun Map for snap volume
$LMResult = Get-XIOInitiatorGroup | Where-Object Name -Like $TargetSearchString | Select-Object name,index | foreach-object{New-XIOLunMap -Name $Snapname -InitiatorGroup $_.index}

# Rescan VMware HBA's
$CLResult = Get-Cluster | Get-VMHost | Get-VMHostStorage -RescanAllHBA

# Add Disk to VM
$vm = Get-VM -Name $VMName
$vmhost = Get-VMHost -Name $vm.VMHost
$deviceName = ($vmhost | Get-ScsiLun | Where-Object {$_.CanonicalName -match ('naa.' + (Get-XIOVolume | Where-Object name -EQ $Snapname).'naa-name')})[0].ConsoleDeviceName
$HDResult = New-HardDisk -DeviceName $deviceName -VM $vm -DiskType RawPhysical

# Rescan Windows
Update-HostStorageCache

# Set mount and drive path 
Set-Disk -Number $DiskNumber -IsReadOnly $false
Set-Disk -Number $DiskNumber -IsOffline $false
Start-Sleep -Seconds 1
if(( -not (get-partition -DiskNumber $DiskNumber).DriveLetter -eq "$MountPath)){
	Add-PartitionAccessPath -DiskNumber $DiskNumber -PartitionNumber $PartitionNumber -AccessPath $MountPath
}
 
# Set permissions Verify Files and Attach Database
Enable-NTFSAccessInheritance -Path $datapath
Enable-NTFSAccessInheritance -Path $logpath
if ((Test-Path -Path $datapath) -and (Test-path -Path $logpath)){

	# Attach Database
	$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)
	$db = New-Object Microsoft.SqlServer.Management.Smo.Database
	$db = $srv.Databases.Item($dbname)
	$sc = new-object System.collections.specialized.stringcollection 
	$sc.Add($datapath)
	$sc.Add($logpath)
	$srv.AttachDatabase($dbname,$sc,[Microsoft.SqlServer.Management.Smo.AttachOptions]::None)

}

# Create snapshot of source volume

$Snapname = ($SnapPrefix + (Get-Date -Format yyyyMMdd-HHmmss))

$SNResult = New-XIOSnapshot -VolName $SourceVolume -SnapName $Snapname -FolderID $SnapFolder

# Create Lun Map for snap volume

$LMResult = Get-XIOInitiatorGroup | Where-Object Name -Like $TargetSearchString | Select-Object name,index | foreach-object{New-XIOLunMap -Name $Snapname -InitiatorGroup $_.index}

# Rescan VMware HBA's

$CLResult = Get-Cluster | Get-VMHost | Get-VMHostStorage -RescanAllHBA

# Add Disk to VM

$vm = Get-VM -Name $VMName

$vmhost = Get-VMHost -Name $vm.VMHost

$deviceName = ($vmhost | Get-ScsiLun | Where-Object {$_.CanonicalName -match ('naa.' + (Get-XIOVolume | Where-Object name -EQ $Snapname).'naa-name')})[0].ConsoleDeviceName

$HDResult = New-HardDisk -DeviceName $deviceName -VM $vm -DiskType RawPhysical

# Rescan Windows

Update-HostStorageCache

# Set mount and drive path

Set-Disk -Number $DiskNumber -IsReadOnly $false

Set-Disk -Number $DiskNumber -IsOffline $false

Start-Sleep -Seconds 1

if(( -not (get-partition -DiskNumber $DiskNumber).DriveLetter -eq "$MountPath)){

Add-PartitionAccessPath -DiskNumber $DiskNumber -PartitionNumber $PartitionNumber -AccessPath $MountPath

}

# Set permissions Verify Files and Attach Database

Enable-NTFSAccessInheritance -Path $datapath

Enable-NTFSAccessInheritance -Path $logpath

if ((Test-Path -Path $datapath) -and (Test-path -Path $logpath)){

# Attach Database

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)

$db = New-Object Microsoft.SqlServer.Management.Smo.Database

$db = $srv.Databases.Item($dbname)

$sc = new-object System.collections.specialized.stringcollection

$sc.Add($datapath)

$sc.Add($logpath)

$srv.AttachDatabase($dbname,$sc,[Microsoft.SqlServer.Management.Smo.AttachOptions]::None)

}

The above example creates a snapshot and maps the volume to the host using the XtremIO REST API. It also rescans the disks and then adds the RDM to the virtual machine. Then the database is attached using SQL SMO.

This completes the database refresh. I hope someone finds this helpful. Next post will introduce AppSync into the mix for application consistency and some additional benefits.

Regards,

Dave

MTSXtremIO Module
NTFSSecurity Module
MTSAuthentication Module

EMC XtremIO and PowerShell

Posted on March 10, 2015
by dmuegge

I have had the opportunity to work with XtremIO quite a bit lately. One of the benefits of working for an EMC partner is lab gearJ. XtremIO hast a REST based API and I wondered what others in the community had done with the API for XtremIO and PowerShell. I started searching for the available PowerShell functionality and found a couple of different modules.

The first one I ran across is here; a module created by Matt Boren. It has some great functionality and I explored it quite a bit. It is written to take advantage of PowerShell features such as the pipeline. The primary challenge I had was understanding the code well enough to be able to extend it and have a good handle on how it works. It has some complex decision and looping structures which I had difficulty following. The next module I found was here; a module created by Brandon Kvarda. This module also has good functionality and although I did not explore it as much as the module mentioned above, it had some tidbits to leverage. The primary reason I did not want to take this module and run with it was some of the development paradigms. I personally like to use a more “PowerShelly” type of approach utilizing PowerShell objects and leveraging the pipeline differently than this module.

I decided to learn some things from the code and create my own module. The articles and code provided some helpful insight and shortened the learning process. My end goal was to create a module which I better understood and could easily extend, while keeping things as simple as possible. I will provide some examples on using the PowerShell module for XtremIO and you can find it here on GitHub.

The module provides standard CRUD functionality from the XtremIO REST API. It has full coverage of all the HTTP GET functions as well as PUT, POST, and DELETE for the most common objects. To start with I am just ignoring certificate errors and it is on my list to add in use of certificates in the login process for both validation and authentication. I am currently using a simple method to store password information in a file to enable creation of scripts that can be run automatically. So let’s go through the process of setting the module up, preparing a password file and executing some commands.

The first thing that is required is to place the module in your modules folder. I will not go into specifics here as there are many sources of that information. Once the module is loaded the first step is to create a password file. The great thing about this method is the password file is encrypted based on the credentials of the currently logged on user and machine. It cannot be used on another machine or by another account on the same machine. So if you are going to setup a password file to be used in a scheduled task make sure you create the file using the account that will run the scheduled task.

Here is a screenshot of the password file creation process.

Here we see the contents of the encrypted password file.

Now that the authentication is setup and ready to use I will talk about the functionality of the module. If we issue a Get-Command –Module MTSXtremIO it will return a list of all the cmdlets currently included in the module.

The first step is to connect to the XtremIO XMS management server. This is done by using the Set-XIOAPIConnectionInfo cmdlet. The example below shows the connection process and an example of the Get-XIOCluster cmdlet returning some basic information about the cluster.

Now I will show an example of creating a volume folder and some volumes. First we will list the names of the current volume folders.

The next step is to create a new volume folder to hold the volumes. The example below shows this along with a listing of the new folder. Now the folder is ready to create the new volumes.

The example below shows how PowerShell can help to create 8 volumes very quickly.

The screenshot below shows an example of how we can view only the resulting volumes. Also in this example I show the VAAI thin provisioning alert setting. One thing to point out is the REST API does not actually set the various alert settings on volume creation and it is done as an additional task after volume creation.

The example below shows how this is accomplished using the Update-XIOVolume cmdlet and lists the updated volumes.

One note here, the New-XIOVolume and Update-XIOVolume cmdlets will be updated soon to accept pipeline input and not require the use of the foreach-object cmdlet. I discovered this flaw creating this post, Doh! That update will also enable putting the New-XIOVolume and Update-XIOVolume together on the pipeline to allow creation and setting options to happen in a simple one liner.

The other common tasks which can be managed with the module today is LUN mapping and snapshots. I will be working on other functionality and adding it to the module as time permits. I hope folks find this useful and I always appreciate feedback and comments. One last thing, if you have not had a chance to do any testing with an XtremIO all flash array I recommend it highly. It is quite fun.

MTSXtremIO PowerShell Module Download https://github.com/dmuegge/MTSXtremIO/releases

Regards,

Dave

EMC Isilon Platform API and PowerShell Part II

Posted on March 24, 2013
by dmuegge

In my last post I talked about the Isilon REST based platform API. I have been experimenting more with the Isilon API and PowerShell. I thought I would share the progress so far.

I decided to create a PowerShell module to leverage the functionality exposed by the Isilon platform API. The module provides some advanced functions, script cmdlets if you prefer, to provide API access. Right now I have some ‘get’ cmdlets and a lot of work to do before I have full coverage of the API. Maybe I can catch up by the time there is full coverage of the Isilon functionality in the platform APIJ

The module uses basic authentication at this time as I am still working on other authentication options. The first step in using the module is to download it and place it in your modules directory. Then load the module and create a password file for logging on to the Isilon cluster. The New-PasswordFile command creates an encrypted file containing the supplied password. This file is used to supply authentication automatically. The file can only be used by the user who was logged on when the file was created.

import-module IsilonPlatform
 
PS C:Windowssystem32> New-PasswordFile -Path c:temp -Filename is7test01-admin_pwd.txt
Password: ********
 
 
    Directory: C:temp
 
 
Mode                LastWriteTime     Length Name
----                -------------     ------ ----
-a---         3/10/2013   7:06 PM          0 pwdfile.txt

import-module IsilonPlatform

PS C:Windowssystem32> New-PasswordFile -Path c:temp -Filename is7test01-admin_pwd.txt

Password: ********

Directory: C:temp

Mode LastWriteTime Length Name

---- ------------- ------ ----

-a--- 3/10/2013 7:06 PM 0 pwdfile.txt

Once this is complete the following code will load a console with the cmdlets and setup authentication.

# Load Module
Import-Module IsilonPlatform
 
# Ignore certificate errors
Disable-CertificateValidation
 
# Set required connection information
Set-ISIAPIConnectionInfo -username "admin" -passwordfile "C:Tempis7test01-admin_pwd.txt" -baseurl "https://is7test01:8080"
 
# Show module commands
Get-Command -Module IsilonPlatform

# Load Module

Import-Module IsilonPlatform

# Ignore certificate errors

Disable-CertificateValidation

# Set required connection information

Set-ISIAPIConnectionInfo -username "admin" -passwordfile "C:Tempis7test01-admin_pwd.txt" -baseurl "https://is7test01:8080"

# Show module commands

Get-Command -Module IsilonPlatform

This code uses the password file we created earlier. Once it is executed we see the available Isilon cmdlets and we have a console to execute Isilon commands and scripts. I put this code into a script to launch an Isilon management console.

DM - Isilon Platform API Toolkit
 
CommandType     Name                                               ModuleName
-----------     ----                                               ----------
Function        Disable-CertificateValidation                      IsilonPlatform
Function        Get-ISIADSProvider                                 IsilonPlatform
Function        Get-ISIADSProviderDomainControllers                IsilonPlatform
Function        Get-ISIADSTrustedDomains                           IsilonPlatform
Function        Get-ISIFileProvider                                IsilonPlatform
Function        Get-ISIGroup                                       IsilonPlatform
Function        Get-ISIGroupMembers                                IsilonPlatform
Function        Get-ISINFSExports                                  IsilonPlatform
Function        Get-ISINFSExportsSummary                           IsilonPlatform
Function        Get-ISINFSSettings                                 IsilonPlatform
Function        Get-ISIProviderSummary                             IsilonPlatform
Function        Get-ISIQuota                                       IsilonPlatform
Function        Get-ISIQuotaLicense                                IsilonPlatform
Function        Get-ISIQuotaNotification                           IsilonPlatform
Function        Get-ISIQuotaReport                                 IsilonPlatform
Function        Get-ISIQuotaReportAbout                            IsilonPlatform
Function        Get-ISIQuotaReportSettings                         IsilonPlatform
Function        Get-ISIQuotaSummary                                IsilonPlatform
Function        Get-ISISMBOpenFiles                                IsilonPlatform
Function        Get-ISISMBSessions                                 IsilonPlatform
Function        Get-ISISMBSettings                                 IsilonPlatform
Function        Get-ISISMBShares                                   IsilonPlatform
Function        Get-ISISMBSharesSummary                            IsilonPlatform
Function        Get-ISISnapshot                                    IsilonPlatform
Function        Get-ISISnapshotLicense                             IsilonPlatform
Function        Get-ISISnapshotSummary                             IsilonPlatform
Function        Get-ISIUser                                        IsilonPlatform
Function        Get-ISIUserMappingRules                            IsilonPlatform
Function        Get-ISIUserMemberOf                                IsilonPlatform
Function        Get-ISIZone                                        IsilonPlatform
Function        Get-PasswordFromFile                               IsilonPlatform
Function        New-PasswordFile                                   IsilonPlatform
Function        Set-ISIAPIConnectionInfo                           IsilonPlatform

DM - Isilon Platform API Toolkit

CommandType Name ModuleName

----------- ---- ----------

Function Disable-CertificateValidation IsilonPlatform

Function Get-ISIADSProvider IsilonPlatform

Function Get-ISIADSProviderDomainControllers IsilonPlatform

Function Get-ISIADSTrustedDomains IsilonPlatform

Function Get-ISIFileProvider IsilonPlatform

Function Get-ISIGroup IsilonPlatform

Function Get-ISIGroupMembers IsilonPlatform

Function Get-ISINFSExports IsilonPlatform

Function Get-ISINFSExportsSummary IsilonPlatform

Function Get-ISINFSSettings IsilonPlatform

Function Get-ISIProviderSummary IsilonPlatform

Function Get-ISIQuota IsilonPlatform

Function Get-ISIQuotaLicense IsilonPlatform

Function Get-ISIQuotaNotification IsilonPlatform

Function Get-ISIQuotaReport IsilonPlatform

Function Get-ISIQuotaReportAbout IsilonPlatform

Function Get-ISIQuotaReportSettings IsilonPlatform

Function Get-ISIQuotaSummary IsilonPlatform

Function Get-ISISMBOpenFiles IsilonPlatform

Function Get-ISISMBSessions IsilonPlatform

Function Get-ISISMBSettings IsilonPlatform

Function Get-ISISMBShares IsilonPlatform

Function Get-ISISMBSharesSummary IsilonPlatform

Function Get-ISISnapshot IsilonPlatform

Function Get-ISISnapshotLicense IsilonPlatform

Function Get-ISISnapshotSummary IsilonPlatform

Function Get-ISIUser IsilonPlatform

Function Get-ISIUserMappingRules IsilonPlatform

Function Get-ISIUserMemberOf IsilonPlatform

Function Get-ISIZone IsilonPlatform

Function Get-PasswordFromFile IsilonPlatform

Function New-PasswordFile IsilonPlatform

Function Set-ISIAPIConnectionInfo IsilonPlatform

Now that we have a console with the commands loaded we can use them to get information from the Isilon system. Here is an example of a command to return all Isilon groups.

PS C:Windowssystem32> get-isigroup
 
 
dn               :
dns_domain       :
domain           : UNIX_GROUPS
generated_gid    : False
gid              : @{id=GID:0}
id               : wheel
member_of        :
name             : wheel
provider         : lsa-file-provider:System
sam_account_name : wheel
sid              : @{id=SID:S-1-22-2-0}
type             : group
 
dn               :
dns_domain       :
domain           : UNIX_GROUPS
generated_gid    : False
gid              : @{id=GID:14}
id               : ftp
member_of        :
name             : ftp
provider         : lsa-file-provider:System
sam_account_name : ftp
sid              : @{id=SID:S-1-22-2-14}
type             : group
 
dn               :
dns_domain       :
domain           : UNIX_GROUPS
generated_gid    : False
gid              : @{id=GID:31}
id               : guest
member_of        :
name             : guest
provider         : lsa-file-provider:System
sam_account_name : guest
sid              : @{id=SID:S-1-22-2-31}
type             : group
 
.
.
.

PS C:Windowssystem32> get-isigroup

dn :

dns_domain :

domain : UNIX_GROUPS

generated_gid : False

gid : @{id=GID:0}

id : wheel

member_of :

name : wheel

provider : lsa-file-provider:System

sam_account_name : wheel

sid : @{id=SID:S-1-22-2-0}

type : group

dn :

dns_domain :

domain : UNIX_GROUPS

generated_gid : False

gid : @{id=GID:14}

id : ftp

member_of :

name : ftp

provider : lsa-file-provider:System

sam_account_name : ftp

sid : @{id=SID:S-1-22-2-14}

type : group

dn :

dns_domain :

domain : UNIX_GROUPS

generated_gid : False

gid : @{id=GID:31}

id : guest

member_of :

name : guest

provider : lsa-file-provider:System

sam_account_name : guest

sid : @{id=SID:S-1-22-2-31}

type : group

Here we can see all groups are returned with all properties. We also see the results are returned as PowerShell objects. This gives us all of the PowerShell goodness when working with the Isilon platform API. The next example uses PowerShell to display just the information we want in an easier to read format.

PS C:Windowssystem32> get-isigroup | select id,provider,domain
 
id                                             provider                                       domain
--                                             --------                                       ------
wheel                                          lsa-file-provider:System                       UNIX_GROUPS
ftp                                            lsa-file-provider:System                       UNIX_GROUPS
guest                                          lsa-file-provider:System                       UNIX_GROUPS
ifs                                            lsa-file-provider:System                       UNIX_GROUPS
admin                                          lsa-file-provider:System                       UNIX_GROUPS
Administrators                                 lsa-local-provider:System                      BUILTIN
Users                                          lsa-local-provider:System                      BUILTIN
Guests                                         lsa-local-provider:System                      BUILTIN
Backup Operators                               lsa-local-provider:System                      BUILTIN
Isilon Users                                   lsa-local-provider:System                      IS7TEST01

PS C:Windowssystem32> get-isigroup | select id,provider,domain

id provider domain

-- -------- ------

wheel lsa-file-provider:System UNIX_GROUPS

ftp lsa-file-provider:System UNIX_GROUPS

guest lsa-file-provider:System UNIX_GROUPS

ifs lsa-file-provider:System UNIX_GROUPS

admin lsa-file-provider:System UNIX_GROUPS

Administrators lsa-local-provider:System BUILTIN

Users lsa-local-provider:System BUILTIN

Guests lsa-local-provider:System BUILTIN

Backup Operators lsa-local-provider:System BUILTIN

Isilon Users lsa-local-provider:System IS7TEST01

The following example shows how we can query the groups, filter by type and control the display format. This example retrieves all users with the domain type of BUILTIN and displays the name and provider in a results table.

PS C:Windowssystem32> Get-ISIGroup | where domain -eq BUILTIN | select name,provider
 
name                                                                  provider
----                                                                  --------
Administrators                                                        lsa-local-provider:System
Users                                                                 lsa-local-provider:System
Guests                                                                lsa-local-provider:System
Backup Operators                                                      lsa-local-provider:System

PS C:Windowssystem32> Get-ISIGroup | where domain -eq BUILTIN | select name,provider

name provider

---- --------

Administrators lsa-local-provider:System

Users lsa-local-provider:System

Guests lsa-local-provider:System

Backup Operators lsa-local-provider:System

The cmdlets will also allow using the pipeline. Although, I do not have pipline functionality in all cmdlets yet. The user, group and provider cmdlets do. The following example shows a listing of all Isilon ADS BUILTIN groups and the members of each group.

PS C:Windowssystem32> Get-ISIGroup | where domain -eq BUILTIN | foreach {Write-Host $_.id; $_ | Get-ISIGroupMembers | select name,type | out-string}
 
Administrators
 
name                                                                  type
----                                                                  ----
LABdomain admins                                                     group
 
 
Users
 
name                                                                  type
----                                                                  ----
Authenticated Users                                                   wellknown
LABdomain users                                                      group
 
 
Guests
 
name                                                                  type
----                                                                  ----
Guest                                                                 user
 
 
Backup Operators

PS C:Windowssystem32> Get-ISIGroup | where domain -eq BUILTIN | foreach {Write-Host $_.id; $_ | Get-ISIGroupMembers | select name,type | out-string}

Administrators

name type

---- ----

LABdomain admins group

Users

name type

---- ----

Authenticated Users wellknown

LABdomain users group

Guests

name type

---- ----

Guest user

Backup Operators

Hopefully someone finds this interesting and I will try to provide more useful examples as I add functionality to the module. Here are the download links for the module and the console launch script. You will need to modify the console launch script(Start-IsilonPlatform.ps1) for your environment.

IsilonPlatform
Start-IsilonPlatform

This module is a work in progress so use at your own risk. I hope to provide more functionality soon and I hope to see EMC add more coverage to the API. I would really like to see more around cluster configuration and performance statistics. I think this is great functionality provided by EMC, more please!

Feedback on the module is welcome.

Regards,

Dave

EMC VNXe Performance Analysis with PowerShell

Posted on October 25, 2012
by dmuegge

Hello again, It has been a long time, but the day job takes precedence. Recently, I have been working on a little pet project I thought I would share. The EMC VNXe is a great entry level storage array which has been popular with SMB and remote office branch office deployments in the enterprise. I was recently asked to look at performance on one of these arrays. While investigating this request I discovered the VNXe has reduced functionality around monitoring compared to big brother, VNX. Performance information is collected differently and the VNXe does not currently have the ability to utilize Unisphere Analyzer.

While searching on the web I found the following article http://henriwithani.wordpress.com/2011/12/01/hidden-vnxe-performance-statistics/ . Henri shows where to find additional performance statistics on the VNXe. He also shows how to export the data to CSV for reporting. The VNXe performance data is stored in a public domain C library based SQL database called SQLite. While investigating the use of the SQLite database and tools I discovered there is an ADO.Net provider assembly available for SQLite. Since I am somewhat partial to PowerShell and any .NET object is accessable from PowerShell. I thought I would take a shot at building a PowerShell Module for looking at VNXe performance data.

Here is what I came up with, the VNXePerformance.psm1 PowerShell module. The module currently consists of 21 CmdLets. One CmdLet is to set the location of the SQLite database tables and the remaining 20 are to return data stored in those tables. The CmdLets currently work with the following three SQLite tables:

capacity.db
stats_basic_summary.db
stats_basic_default.db

These three tables appear to hold most of the interesting information so I concentrated on them first. The primary purpose of the module is to query the database and return PowerShell objects from the data. A secondary purpose of the module is to add calculated properties to some of those returned objects. The calculated properties currently implemented provide bytes per second for each of the three IP storage protocols supported NFS, CIFS, and iSCSI and also read and write IO’s per second. This was the first info I needed and was pretty simple to figure out. I plan to add additional calculated fields in the future as I learn more about the data.

Using the module

Install the SQLite ADO.Net Provider Assembly

The VNXePerformance module requires the SQLite ADO.Net Provider to be installed, which can be downloaded from the following page http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

I recommend using the setup package for the framework and OS version you are using.

The next step is to download the module, unzip it to the modules folder. The proper modules folder can be identified with the $env:PSModulePath variable.

Download the module VnxePerformance.zip

Unzip the module to the PowerShell modules folder, open PowerShell and verify module is available. The VNXePerformance module should be listed when executing the get-module -ListAvailable command, if installed in the proper location.

PS C:&gt; get-module -ListAvailable

ModuleType Name                      ExportedCommands
---------- ----                      ----------------
Manifest   VNXePerformance           {Get-VNXeBasicSummaryGMSSPADetail, Get-VNXeSystemTotalsDetail, Get-VNXeB...

PS C:> get-module -ListAvailable

ModuleType Name ExportedCommands

---------- ---- ----------------

Manifest VNXePerformance {Get-VNXeBasicSummaryGMSSPADetail, Get-VNXeSystemTotalsDetail, Get-VNXeB...

Then set the following line to the location of the System.Data.SQLite.dll and save changes as necessary. The location defined in the module is the default installation path for the assembly if installed with the prepackaged installation.

$AssemblyPath="C:Program FilesSystem.Data.SQLite2010binSystem.Data.SQLite.dll"

1	$AssemblyPath="C:Program FilesSystem.Data.SQLite2010binSystem.Data.SQLite.dll"

We are now ready to use the module so open a PowerShell prompt and import the module.

PS C:&gt;Import-Module VNXePerformance

1	PS C:>Import-Module VNXePerformance

We can now use the get-command cmdlet to see the new functionality provided by our module.

PS C:&gt;Get-Command -Module vnxeperformance | select name
Name
----
Get-VNXeApplicationsDetail
Get-VNXeApplicationTotalsDetail
Get-VNXeBasicDefaultDart2Detail
Get-VNXeBasicDefaultDart3Detail
Get-VNXeBasicDefaultFlareSPADetail
Get-VNXeBasicDefaultFlareSPBDetail
Get-VNXeBasicDefaultOSSPADetail
Get-VNXeBasicDefaultOSSPBDetail
Get-VNXeBasicSummaryDart2Detail
Get-VNXeBasicSummaryDart3Detail
Get-VNXeBasicSummaryFlareSPADetail
Get-VNXeBasicSummaryFlareSPBDetail
Get-VNXeBasicSummaryGMSSPADetail
Get-VNXeBasicSummaryGMSSPBDetail
Get-VNXeBasicSummaryOSSPADetail
Get-VNXeBasicSummaryOSSPBDetail
Get-VNXeInitTimeDetail
Get-VNXePoolsDetail
Get-VNXePoolTotalsDetail
Get-VNXeSystemTotalsDetail
Set-VNXeSQLiteLocation

PS C:>Get-Command -Module vnxeperformance | select name

Name

----

Get-VNXeApplicationsDetail

Get-VNXeApplicationTotalsDetail

Get-VNXeBasicDefaultDart2Detail

Get-VNXeBasicDefaultDart3Detail

Get-VNXeBasicDefaultFlareSPADetail

Get-VNXeBasicDefaultFlareSPBDetail

Get-VNXeBasicDefaultOSSPADetail

Get-VNXeBasicDefaultOSSPBDetail

Get-VNXeBasicSummaryDart2Detail

Get-VNXeBasicSummaryDart3Detail

Get-VNXeBasicSummaryFlareSPADetail

Get-VNXeBasicSummaryFlareSPBDetail

Get-VNXeBasicSummaryGMSSPADetail

Get-VNXeBasicSummaryGMSSPBDetail

Get-VNXeBasicSummaryOSSPADetail

Get-VNXeBasicSummaryOSSPBDetail

Get-VNXeInitTimeDetail

Get-VNXePoolsDetail

Get-VNXePoolTotalsDetail

Get-VNXeSystemTotalsDetail

Set-VNXeSQLiteLocation

We can also use the get-help CmdLet to find out the functionality provided by each function. A particulary helpful piece of info in the function help is the example field list and data.

PS C:&gt; get-help Get-VNXeSystemTotalsDetail -full

NAME
    Get-VNXeSystemTotalsDetail

SYNOPSIS
    Returns a psobject for the SQLite capacity.db system_totals table

SYNTAX
    Get-VNXeSystemTotalsDetail [<commonparameters>]

DESCRIPTION
    Returns a psobject for the SQLite capacity.db system_totals table

PARAMETERS
    <commonparameters>
        This cmdlet supports the common parameters: Verbose, Debug,
        ErrorAction, ErrorVariable, WarningAction, WarningVariable,
        OutBuffer and OutVariable. For more information, see
        about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).

INPUTS
    None, Only requires set-SQLiteLocation to be called prior in session

OUTPUTS
    Array of PSObjects representing table records

    Record Example: system_totals
     --------------------------------------------------------
        timestamp            : 2011-05-03 23:59:15
        closing_time         : 2011-05-03 23:59:15
        allocated_space      : 0
        total_space          : 0
        free_space           : 0
        allocated_protection : 0
        total_protection     : 0
     --------------------------------------------------------


    -------------------------- EXAMPLE 1 --------------------------

    C:PS&gt;$detailrecords = Get-VNXeSystemTotalsDetail


RELATED LINKS

</commonparameters></commonparameters>

PS C:> get-help Get-VNXeSystemTotalsDetail -full

NAME

Get-VNXeSystemTotalsDetail

SYNOPSIS

Returns a psobject for the SQLite capacity.db system_totals table

SYNTAX

Get-VNXeSystemTotalsDetail [<commonparameters>]

DESCRIPTION

Returns a psobject for the SQLite capacity.db system_totals table

PARAMETERS

This cmdlet supports the common parameters: Verbose, Debug,

ErrorAction, ErrorVariable, WarningAction, WarningVariable,

OutBuffer and OutVariable. For more information, see

about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).

INPUTS

None, Only requires set-SQLiteLocation to be called prior in session

OUTPUTS

Array of PSObjects representing table records

Record Example: system_totals

--------------------------------------------------------

timestamp : 2011-05-03 23:59:15

closing_time : 2011-05-03 23:59:15

allocated_space : 0

total_space : 0

free_space : 0

allocated_protection : 0

total_protection : 0

--------------------------------------------------------

-------------------------- EXAMPLE 1 --------------------------

C:PS>$detailrecords = Get-VNXeSystemTotalsDetail

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this: