PowerShell – OS Inventory and Disk Info – Consolidated Excel File – EMAIL

Posted on May 21, 2014 by Prashanth Jayaram

This script is used to discover all the server information and captures disk drive utilization details. The drive details are aggregated based on the status of free space. The aggregated data is pictorially represented using pie chart. The excel sheet is then send to intended users.

The script requires input file where all the servers are listed. You can also populate the server list by quering AD. The script will try to communicate all the servers which are listed in the input file to get the required information

Script	Server Inventory on sheet1 and Disk Information on sheet2
Directory	It checks for the directory C:\Scripts\ and create a one if it doesn’t exists.
Source File	GetAllOsDrSrvInfo.PS1
Input File	E:\Scripts\InputServer.txt
Output	Excel sheet as an attachment
Email Notification	Yes
Recipients	More than one recipients can be separated by comma
SMTPMail	SMTP address
Functionality	Discovery all the server information and consolidate the data in a excel sheet

Change the first few lines of colored code as per your requirement and change the alert threshold in the bottom of the code

#### Spreadsheet Location and email sending details

$DirectoryToSaveTo = “c:\”

$date=Get-Date -format “yyyy-MM-d”

$Filename=”serverinfo-$($date)”

$FromEmail=”<ToEmail>”

$ToEmail=”<FromEmail>”

$SMTPMail=”<SMTP MAIL>”

###InputLocation

$Computers = Get-Content “c:\server.txt”

###################

The code is tested on Powershell 2.0 onwards

Download the code : – OS and DiskInfo

Code:-

  
#### Spreadsheet Location 
 $DirectoryToSaveTo = "c:\" 
 $date=Get-Date -format "yyyy-MM-d" 
 $Filename="serverinfo-$($date)" 
 $FromEmail="<ToEmail>" 
 $ToEmail="<FromEmail>" 
 $SMTPMail="<SMTP MAIL>" 
  
 ###InputLocation 
 $Computers = Get-Content "c:\server.txt" 
  
  
# before we do anything else, are we likely to be able to save the file? 
# if the directory doesn't exist, then create it 
if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing 
  { 
  New-Item "$DirectoryToSaveTo" -type directory | out-null 
  } 
   
 
 
#Create a new Excel object using COM  
$Excel = New-Object -ComObject Excel.Application 
$Excel.visible = $True 
$Excel = $Excel.Workbooks.Add() 
$Sheet = $Excel.Worksheets.Item(1) 
 
$sheet.Name = 'Server Inventory' 
#Create a Title for the first worksheet 
$row = 1 
$Column = 1 
$Sheet.Cells.Item($row,$column)= 'Server Inventory' 
 
$range = $Sheet.Range("a1","s2") 
$range.Merge() | Out-Null 
$range.VerticalAlignment = -4160 
 
#Give it a nice Style so it stands out 
$range.Style = 'Title' 
 
#Increment row for next set of data 
$row++;$row++ 
 
#Save the initial row so it can be used later to create a border 
#Counter variable for rows 
$intRow = $row 
$xlOpenXMLWorkbook=[int]51 
 
#Read thru the contents of the SQL_Servers.txt file 
 
$Sheet.Cells.Item($intRow,1)  ="Name" 
$Sheet.Cells.Item($intRow,2)  ="status" 
$Sheet.Cells.Item($intRow,3)  ="OS" 
$Sheet.Cells.Item($intRow,4)  ="Domain Role" 
$Sheet.Cells.Item($intRow,5)  ="ProcessorName" 
$Sheet.Cells.Item($intRow,6)  ="Manufacturer" 
$Sheet.Cells.Item($intRow,7)  ="Model" 
$Sheet.Cells.Item($intRow,8)  ="SystemType" 
$Sheet.Cells.Item($intRow,9)  ="Last Boot Time" 
$Sheet.Cells.Item($intRow,10) ="Bios Version" 
$Sheet.Cells.Item($intRow,11) ="CPU Info" 
$Sheet.Cells.Item($intRow,12) ="NoOfProcessors" 
$Sheet.Cells.Item($intRow,13) ="Total Physical Memory" 
$Sheet.Cells.Item($intRow,14) ="Total Free Physical Memory" 
$Sheet.Cells.Item($intRow,15) ="Total Virtual Memory" 
$Sheet.Cells.Item($intRow,16) ="Total Free Virtual Memory" 
$Sheet.Cells.Item($intRow,17) ="Disk Info" 
$Sheet.Cells.Item($intRow,18) ="FQDN" 
$Sheet.Cells.Item($intRow,19) ="IPAddress" 
 
for ($col = 1; $col –le 19; $col++) 
     { 
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True 
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48 
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34 
     } 
 
$intRow++ 
 
 
Function GetStatusCode 
{  
    Param([int] $StatusCode)   
    switch($StatusCode) 
    { 
        0         {"Success"} 
        11001   {"Buffer Too Small"} 
        11002   {"Destination Net Unreachable"} 
        11003   {"Destination Host Unreachable"} 
        11004   {"Destination Protocol Unreachable"} 
        11005   {"Destination Port Unreachable"} 
        11006   {"No Resources"} 
        11007   {"Bad Option"} 
        11008   {"Hardware Error"} 
        11009   {"Packet Too Big"} 
        11010   {"Request Timed Out"} 
        11011   {"Bad Request"} 
        11012   {"Bad Route"} 
        11013   {"TimeToLive Expired Transit"} 
        11014   {"TimeToLive Expired Reassembly"} 
        11015   {"Parameter Problem"} 
        11016   {"Source Quench"} 
        11017   {"Option Too Big"} 
        11018   {"Bad Destination"} 
        11032   {"Negotiating IPSEC"} 
        11050   {"General Failure"} 
        default {"Failed"} 
    } 
} 
 
 
Function GetUpTime 
{ 
    param([string] $LastBootTime) 
    $Uptime = (Get-Date) - [System.Management.ManagementDateTimeconverter]::ToDateTime($LastBootTime) 
    "Days: $($Uptime.Days); Hours: $($Uptime.Hours); Minutes: $($Uptime.Minutes); Seconds: $($Uptime.Seconds)"  
} 
 
     
     
 
 
 
foreach ($Computer in $Computers) 
 { 
 
 TRY { 
 $OS = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $Computer 
 $Bios = Get-WmiObject -Class Win32_BIOS -ComputerName $Computer 
 $sheetS = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $Computer 
 $sheetPU = Get-WmiObject -Class Win32_Processor -ComputerName $Computer 
 $drives = Get-WmiObject -ComputerName $Computer Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3} 
 $pingStatus = Get-WmiObject -Query "Select * from win32_PingStatus where Address='$Computer'" 
 $IPAddress=(Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $Computer | ? {$_.IPEnabled}).ipaddress 
 $FQDN=[System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().Name 
 $OSRunning = $OS.caption + " " + $OS.OSArchitecture + " SP " + $OS.ServicePackMajorVersion 
 $NoOfProcessors=$sheetS.numberofProcessors 
 $name=$SheetPU|select name -First 1 
 $Manufacturer=$sheetS.Manufacturer 
 $Model=$sheetS.Model 
 $systemType=$sheetS.SystemType 
 $ProcessorName=$SheetPU|select name -First 1 
 $DomainRole = $sheetS.DomainRole 
 $TotalAvailMemory = $OS.totalvisiblememorysize/1kb 
 $TotalVirtualMemory = $OS.totalvirtualmemorysize/1kb 
 $TotalFreeMemory = $OS.FreePhysicalMemory/1kb 
 $TotalFreeVirtualMemory = $OS.FreeVirtualMemory/1kb 
 $TotalMem = "{0:N2}" -f $TotalAvailMemory 
 $TotalVirt = "{0:N2}" -f $TotalVirtualMemory 
 $FreeMem = "{0:N2}" -f $TotalFreeMemory 
 $FreeVirtMem = "{0:N2}" -f $TotalFreeVirtualMemory 
 $date = Get-Date 
 $uptime = $OS.ConvertToDateTime($OS.lastbootuptime) 
 $BiosVersion = $Bios.Manufacturer + " " + $Bios.SMBIOSBIOSVERSION + " " + $Bios.ConvertToDateTime($Bios.Releasedate) 
 $sheetPUInfo = $name.Name + " & has " + $sheetPU.NumberOfCores + " Cores & the FSB is " + $sheetPU.ExtClock + " Mhz" 
 $sheetPULOAD = $sheetPU.LoadPercentage 
  
 if($pingStatus.StatusCode -eq 0) 
    { 
        $Status = GetStatusCode( $pingStatus.StatusCode ) 
    } 
else 
    { 
    $Status = GetStatusCode( $pingStatus.StatusCode ) 
       } 
     
     
 if (($DomainRole -eq "0") -or ($DomainRole -eq "1")) 
 { 
 $Role = "Work Station" 
 } 
 elseif (($DomainRole -eq "2") -or ($DomainRole -eq "3")) 
 { 
 $Role = "Member Server" 
 } 
 elseif (($DomainRole -eq "4") -or ($DomainRole -eq "5")) 
 { 
 $Role = "Domain Controller" 
 } 
 else 
 { 
 $Role = "Unknown" 
 } 
 } 
 CATCH 
 { 
 $pcnotfound = "true" 
 } 
 #### Pump Data to Excel 
 if ($pcnotfound -eq "true") 
 { 
 $sheet.Cells.Item($intRow, 1) = "$($computer) Not Found " 
 } 
 else 
 { 
 $sheet.Cells.Item($intRow, 1) = $computer 
 $sheet.Cells.Item($intRow, 2) = $status 
 $sheet.Cells.Item($intRow, 3) = $OSRunning 
 $sheet.Cells.Item($intRow, 4) = $Role 
 $sheet.Cells.Item($intRow, 5) = $name.name 
 $Sheet.Cells.Item($intRow, 6) = $Manufacturer 
 $Sheet.Cells.Item($intRow, 7) = $Model 
 $Sheet.Cells.Item($intRow, 8) = $SystemType 
 $sheet.Cells.Item($intRow, 9) = $uptime 
 $sheet.Cells.Item($intRow, 10)= $BiosVersion 
 $sheet.Cells.Item($intRow, 11)= $sheetPUInfo 
 $sheet.Cells.Item($intRow, 12)=$NoOfProcessors 
 $sheet.Cells.Item($intRow, 13)= "$TotalMem MB" 
 $sheet.Cells.Item($intRow, 14)= "$FreeMem MB" 
 $sheet.Cells.Item($intRow, 15)= "$TotalVirt MB" 
 $sheet.Cells.Item($intRow, 16)= "$FreeVirtMem MB" 
 $sheet.Cells.Item($intRow, 19)=$IPAddress 
 $sheet.Cells.Item($intRow, 18)=$FQDN 
 
  
$driveStr = "" 
 foreach($drive in $drives) 
 { 
 $size1 = $drive.size / 1GB 
 $size = "{0:N2}" -f $size1 
 $free1 = $drive.freespace / 1GB 
 $free = "{0:N2}" -f $free1 
 $freea = $free1 / $size1 * 100 
 $freeb = "{0:N2}" -f $freea 
 $ID = $drive.DeviceID 
 $driveStr += "$ID = Total Space: $size GB / Free Space: $free GB / Free (Percent): $freeb % ` " 
 } 
 $sheet.Cells.Item($intRow, 17) = $driveStr 
 } 
 
  
$intRow = $intRow + 1 
 $pcnotfound = "false" 
 } 
 
$erroractionpreference = “SilentlyContinue”  
 
$Sheet.UsedRange.EntireColumn.AutoFit() 
########################################333 
 
$Sheet = $Excel.Worksheets.Item(2) 
$sheet.Name = 'DiskSpace' 
$Sheet.Activate() | Out-Null 
 
#Create a Title for the first worksheet 
$row = 1 
$Column = 1 
$Sheet.Cells.Item($row,$column)= 'Disk Space Information' 
 
$range = $Sheet.Range("a1","h2") 
$range.Merge() | Out-Null 
$range.VerticalAlignment = -4160 
 
#Give it a nice Style so it stands out 
$range.Style = 'Title' 
 
#Increment row for next set of data 
$row++;$row++ 
 
#Save the initial row so it can be used later to create a border 
$initalRow = $row 
 
#Create a header for Disk Space Report; set each cell to Bold and add a background color 
$Sheet.Cells.Item($row,$column)= 'Computername' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column).Font.Bold=$True 
$Column++ 
$Sheet.Cells.Item($row,$column)= 'DeviceID' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column).Font.Bold=$True 
$Column++ 
$Sheet.Cells.Item($row,$column)= 'VolumeName' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column).Font.Bold=$True 
$Column++ 
$Sheet.Cells.Item($row,$column)= 'TotalSizeGB' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column).Font.Bold=$True 
$Column++ 
$Sheet.Cells.Item($row,$column)= 'UsedSpaceGB' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column).Font.Bold=$True 
$Column++ 
$Sheet.Cells.Item($row,$column)= 'FreeSpaceGB' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column).Font.Bold=$True 
$Column++ 
$Sheet.Cells.Item($row,$column)= '%Free' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column).Font.Bold=$True 
$Column++ 
$Sheet.Cells.Item($row,$column)= 'State' 
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48 
$Sheet.Cells.Item($row,$column).Font.Bold=$True 
 
#Set up a header filter 
$headerRange = $Sheet.Range("a3","h3") 
$headerRange.AutoFilter() | Out-Null 
 
#Increment Row and reset Column back to first column 
$row++ 
$Column = 1 
$critical=0 
$warning=0 
$good=0 
 
#Get the drives and filter out CD/DVD drives 
foreach ($computer in $Computers) 
 { 
$diskDrives = Get-WmiObject win32_LogicalDisk -Filter "DriveType='3'" -ComputerName $computer 
 
#Process each disk in the collection and write to spreadsheet 
ForEach ($disk in $diskDrives) { 
    $Sheet.Cells.Item($row,1)= $disk.__Server 
    $Sheet.Cells.Item($row,2)= $disk.DeviceID 
    $Sheet.Cells.Item($row,3)= $disk.VolumeName 
    $Sheet.Cells.Item($row,4)= [math]::Round(($disk.Size /1GB),2) 
    $Sheet.Cells.Item($row,5)= [math]::Round((($disk.Size - $disk.FreeSpace)/1GB),2) 
    $Sheet.Cells.Item($row,6)= [math]::Round(($disk.FreeSpace / 1GB),2) 
    $Sheet.Cells.Item($row,7)= ("{0:P}" -f ($disk.FreeSpace / $disk.Size))   
    
    #Determine if disk needs to be flagged for warning or critical alert 
    If ($disk.FreeSpace -lt 5GB -AND ("{0:P}" -f ($disk.FreeSpace / $disk.Size))  -lt 40) { 
        $Sheet.Cells.Item($row,8) = "Critical" 
        $critical++ 
        #Check to see if space is near empty and use appropriate background colors 
        $range = $Sheet.Range(("A{0}"  -f $row),("H{0}"  -f $row)) 
        $range.Select() | Out-Null     
        #Critical threshold          
        $range.Interior.ColorIndex = 3 
    } ElseIf ($disk.FreeSpace -lt 10GB -AND ("{0:P}" -f ($disk.FreeSpace / $disk.Size)) -lt 60) { 
        $Sheet.Cells.Item($row,8) = "Warning" 
        $range = $Sheet.Range(("A{0}"  -f $row),("H{0}"  -f $row)) 
        $range.Select() | Out-Null         
        $warning++ 
        $range.Interior.ColorIndex = 6 
    } Else { 
        $Sheet.Cells.Item($row,8) = "Good" 
        $good++ 
    } 
 
     $row++ 
} 
} 
 
#Add a border for data cells 
$row-- 
$dataRange = $Sheet.Range(("A{0}"  -f $initalRow),("H{0}"  -f $row)) 
7..12 | ForEach { 
    $dataRange.Borders.Item($_).LineStyle = 1 
    $dataRange.Borders.Item($_).Weight = 2 
} 
 
#Auto fit everything so it looks better 
 
$usedRange = $Sheet.UsedRange                                                             
$usedRange.EntireColumn.AutoFit() | Out-Null 
 
$critical 
$warning 
$good 
 
$sheet = $excel.Worksheets.Item(2)  
  
$row++;$row++ 
 
$beginChartRow = $Row 
 
$Sheet.Cells.Item($row,$Column) = 'Critical' 
$Column++ 
$Sheet.Cells.Item($row,$Column) = 'Warning' 
$Column++ 
$Sheet.Cells.Item($row,$Column) = 'Good' 
$Column = 1 
$row++ 
#Critical formula 
$Sheet.Cells.Item($row,$Column)=$critical 
$Column++ 
#Warning formula 
$Sheet.Cells.Item($row,$Column)=$warning 
$Column++ 
#Good formula 
$Sheet.Cells.Item($row,$Column)= $good 
 
$endChartRow = $row 
 
$chartRange = $Sheet.Range(("A{0}" -f $beginChartRow),("C{0}" -f $endChartRow)) 
 
##Add a chart to the workbook 
#Open a sheet for charts 
$temp = $sheet.Charts.Add() 
$temp.Delete() 
$chart = $sheet.Shapes.AddChart().Chart 
$sheet.Activate() 
 
#Configure the chart 
##Use a 3D Pie Chart 
$chart.ChartType = 70 
$chart.Elevation = 40 
#Give it some color 
$sheet.Shapes.Item("Chart 1").Fill.ForeColor.TintAndShade = .34 
$sheet.Shapes.Item("Chart 1").Fill.ForeColor.ObjectThemeColor = 5 
$sheet.Shapes.Item("Chart 1").Fill.BackColor.TintAndShade = .765 
$sheet.Shapes.Item("Chart 1").Fill.ForeColor.ObjectThemeColor = 5 
 
$sheet.Shapes.Item("Chart 1").Fill.TwoColorGradient(1,1) 
 
#Set the location of the chart 
$sheet.Shapes.Item("Chart 1").Placement = 3 
$sheet.Shapes.Item("Chart 1").Top = 30 
$sheet.Shapes.Item("Chart 1").Left = 600 
 
$chart.SetSourceData($chartRange) 
$chart.HasTitle = $True 
 
$chart.ApplyLayout(6,69) 
$chart.ChartTitle.Text = "Disk Space Report" 
$chart.ChartStyle = 26 
$chart.PlotVisibleOnly = $False 
$chart.SeriesCollection(1).DataLabels().ShowValue = $True 
$chart.SeriesCollection(1).DataLabels().Separator = ("{0}" -f [char]10) 
 
$chart.SeriesCollection(1).DataLabels().Position = 2 
#Critical 
$chart.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = 255 
#Warning 
$chart.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = 65535 
#Good 
$chart.SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = 5287936 
 
#Hide the data 
$chartRange.EntireRow.Hidden = $True 
 
$sheet.Name = 'DiskInformation' 
 
 
 
 
$filename = "$DirectoryToSaveTo$filename.xlsx" 
if (test-path $filename ) { rm $filename } #delete the file if it already exists 
$Sheet.UsedRange.EntireColumn.AutoFit() 
$Excel.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx) 
$Excel.Saved = $True 
$Excel.Close() 
$Excel.DisplayAlerts = $False 
$Excel.quit() 
 
 
Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath) 
{ 
#initate message 
$email = New-Object System.Net.Mail.MailMessage  
$email.From = $emailFrom 
$email.To.Add($emailTo) 
$email.Subject = $subject 
$email.Body = $body 
# initiate email attachment  
$emailAttach = New-Object System.Net.Mail.Attachment $filePath 
$email.Attachments.Add($emailAttach)  
#initiate sending email  
$smtp = new-object Net.Mail.SmtpClient($smtpServer) 
$smtp.Send($email) 
} 
 
#Call Function  
 
$message = @"  
Hi Team, 
 
The Discovery of Windows Server and Disk Space information for all the listed instances. 
 
Autogenerated Email!!! Please do not reply. 
 
Thank you,  
XYZ.com 
 
"@         
$date=get-date 
 
sendEmail -emailFrom $fromEmail -emailTo $ToEmail -subject "Windows Server Inventory & Disk Details -$($date)" -body $message -smtpServer $SMTPMail -filePath $filename

Sheet1 – Server Information

Sheet 2 – Disk Information – No of critical, warning and good state representation of drives

Email-Output-

Reference

PowerShell and Excel: Adding a Chart and Header Filter to a Report

Posted in Uncategorized | Tagged Drive Information, Email Attachment, Graph Disk representation Pie chart, OS Inventory, Powershell Script, Server consolidation | 4 Comments

SQL – List All Tables, Space Usage Info & Other Details

Posted on May 1, 2014 by Prashanth Jayaram

One of the OP requested in the forum to find all table details of any database. The details which includes Number of Rows,table size, index size etc:-. Even I frequently writing queries to list all such details.

I’ve used SQLCMD mode to connect to the remote server and database to fetch all the required details.

SQLCMD can be enabled in Query Editor.

Go to SSMS Menu -> Query ->Select SQLCMD.

Download the script :- SQL-List-All-Tables-Information

Chane the Servername and Databasename in the below script

:CONNECT <SERVERNAME> 
:SETVAR DB <DATABASENAME> 
 
USE $(DB) 
 
create table #TableSize ( 
    Name varchar(255), 
    [rows] int, 
    reserved varchar(255), 
    data varchar(255), 
    index_size varchar(255), 
    unused varchar(255)) 
create table #ConvertedSizes ( 
    Name varchar(255), 
    [rows] int, 
    reservedKb int, 
    dataKb int, 
    reservedIndexSize int, 
    reservedUnused int) 
 
EXEC sp_MSforeachtable @command1="insert into #TableSize 
EXEC sp_spaceused '?'" 
insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused) 
select name, [rows],  
SUBSTRING(reserved, 0, LEN(reserved)-2),  
SUBSTRING(data, 0, LEN(data)-2),  
SUBSTRING(index_size, 0, LEN(index_size)-2),  
SUBSTRING(unused, 0, LEN(unused)-2) 
from #TableSize 
 
select * from #ConvertedSizes 
order by reservedKb desc 
 
drop table #TableSize 
drop table #ConvertedSizes

Output:-

Posted in Uncategorized | Tagged Index Size, No Of Rows, sp_msforeachtable, sp_spaceUsed, SQLCMD, Table Size | Leave a comment

PowerShell-Input-Validation Scripts

Posted on April 28, 2014 by Prashanth Jayaram

Input values are validated using various mechanisms. let’s start by examining how you would validate input parameters in Windows PowerShell 2.0

The Inputs are validated for the below mentioned functionality

PostalCode
PhoneNumber
EmailID
IP
FileName

Download the scripts from the below link

http://gallery.technet.microsoft.com/PowerShell-Input-Validation-30ebaa51

To validate PostalCode – 99999

Function Validate-PostalCode 
{ 
 Param([Parameter(Mandatory=$true,HelpMessage="Enter a valid Postal Code xxx-xxx-xxxx")][ValidatePattern("[0-9][0-9][0-9][0-9]")]$PostalCode) 
 Write-host "The Pin Code $PostalCode is valid" 
}

To Validate phone number – 999-999-9999

Function validate-PhoneNumber 
{ 
 Param([ValidatePattern("\d{3}-\d{3}-\d{4}")]$phoneNumber) 
  Write-host "The phone number $phoneNumber is valid" 
} 
 
validate-PhoneNumber -Phonenumber 999-999-9999

To Validate email address

function Validate-Email ([string]$Email) 
{ 
  return $Email -match "^(?("")("".+?""@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$" 
}

To Validate IP Range – 255.255.255.255

function Validate-IPAddress ([string]$IP) 
 { 
 if($IP -match "(\d{1,3}).(\d{1,3}).(\d{1,3}).(\d{1,3})" -and -not ([int[]]$matches[1..4] -gt 255)) 
 { 
   Write-host "The $IP IP is valid" 
 } 
 }

To Validate the filename for ########_???_?.jpg

function Validate-Filename 
{ 
Param([ValidatePattern("^\d{8}_[a-zA-Z]{3,4}_[a-zA-Z]{1}\.jpg"]$filename) 
Write-host "The filename $filename is valid" 
}

You can also refer the below link for more information

http://henrik.familiendamgaard.dk/blog/2012/08/14/easy-validating-parameter-input-using-validatecount-validatelength-validatepattern-validaterange-validateset-and-validatescript-in-powershell/

Posted in Uncategorized | Tagged Email ID, Filename Validation, Input validation, IP, Phonenumber, PostalCode | Leave a comment

SQL – Find encrypted objects using sql_modules or Powershell

Posted on April 14, 2014 by Prashanth Jayaram

sys.sql_modules catalog view which got introduced from SQL 2005 onwards. The sql_modules which includes the objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module.

SQL

SELECT 
sm.object_id, 
OBJECT_NAME(sm.object_id) AS object_name, 
o.type, o.type_desc, 
sm.definition, 
CAST(CASE WHEN sm.definition IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted] 
FROM sys.sql_modules AS sm 
JOIN sys.objects AS o ON sm.object_id = o.object_id 
ORDER BY o.type;

sql_modules to list only stored procedures then you can join with sys.procedures

SQL

SELECT SP.*, 
CASE  
WHEN SM.definition IS NULL THEN 'Encyrpted' END AS IsEncrypted 
FROM sys.procedures AS SP     
LEFT JOIN sys.sql_modules AS SM    ON SP.object_id = SM.object_id

PowerShell :-

Change the Servername and DBName in the below code. Execute the below code from PowerShell-ISE

$server = "<ServerName>" 
$database = "<DBName>" 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null 
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server 
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database") 
 
# Get the database and Its objects 
$db = $srv.Databases[$database] 
$storedProcs = $db.StoredProcedures | Where-object {$_.IsEncrypted} 
$storedProcs|select name

Download the code: –

http://gallery.technet.microsoft.com/SQL-Find-encrypted-objects-327ea596

Posted in Uncategorized | Tagged Encrypted Object list, sys.sql_modules | Leave a comment

First Birthday to My Blog

Posted on April 10, 2014 by Prashanth Jayaram

Today is the First year anniversary of my blog. After thinking a lot and going through many un-imaginable resource available over the internet and reading blogs of many wonderful brains made me think about creating a blog.

When I wrote my first post a year ago I wondered and asked myself many times what I’m going to tell you all. I refused myself many times. At lost I made it and completed first year. Later I started enjoying in what I’m doing. It’s great to share what I do, but mostly I enjoy the feedback I get from you all. This is really invaluable. It tells me what works and what doesn’t and many times inputs and advice’s is truly priceless.

Thanks everyone for contributing to the world of knowledge. I truly admire everyone.

Happy Learning!!!!!!!!!!!

Thanks!!

Posted in Uncategorized | Tagged First Year celebration | Leave a comment

PowerShell GUI Tool – Network Ping – Version 2

Posted on April 2, 2014 by Prashanth Jayaram

The first version of the tool was developed by Amandhally and it can be found in the below path. Thanks for sharing.

http://www.amandhally.net/2012/08/08/powershell-based-network-range-pinging-application-network-pinger-ajit-2/

In my version of the tool, I’ve re-used his template and modified in such a way that the output is HTML formatted with server Names displayed in the result area.

You can download the second version of the tool by clicking the below link

http://gallery.technet.microsoft.com/PowerShell-GUI-Tool-f03b7523

This script requires

Staring IP- Adddress
Ending IP-Address
Hit RUN button
The output in the result box

You can also send email from the below portion of window

Output-

PowerShell Code:-

<#=================================  
# Generated On: 04/02/2014   
# Generated By: Prashanth Jayaram   
# Version     : 1.1   
# Desc        : Network Ping  
#=================================  
#>  
 
Function Network_Pinger 
{ 
function OnApplicationLoad { 
    #Note: This function is not called in Projects 
    #Note: This function runs before the form is created 
    #Note: To get the script directory in the Packager use: Split-Path $hostinvocation.MyCommand.path 
    #Note: To get the console output in the Packager (Windows Mode) use: $ConsoleOutput (Type: System.Collections.ArrayList) 
    #Important: Form controls cannot be accessed in this function 
    #TODO: Add snapins and custom code to validate the application load 
    $PingReport = "C:\pingstatus.htm" 
    New-Item -ItemType file $PingReport -Force 
    return $true #return true for success or false for failure 
} 
 
function OnApplicationExit { 
    #Note: This function is not called in Projects 
    #Note: This function runs after the form is closed 
    #TODO: Add custom code to clean up and unload snapins when the application exits 
     
    $script:ExitCode = 0 #Set the exit code for the Packager 
     
} 
 
#endregion Application Functions 
 
#---------------------------------------------- 
# Generated Form Function 
#---------------------------------------------- 
function Call-PIng_Network_Community_v2_pff { 
 
    #---------------------------------------------- 
    #region Import the Assemblies 
    #---------------------------------------------- 
    [void][reflection.assembly]::Load("mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089") 
    [void][reflection.assembly]::Load("System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089") 
    [void][reflection.assembly]::Load("System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089") 
    [void][reflection.assembly]::Load("System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089") 
    [void][reflection.assembly]::Load("System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a") 
    [void][reflection.assembly]::Load("System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089") 
    [void][reflection.assembly]::Load("System.DirectoryServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a") 
    [void][reflection.assembly]::Load("System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089") 
    #endregion Import Assemblies 
 
    #---------------------------------------------- 
    #region Generated Form Objects 
    #---------------------------------------------- 
    [System.Windows.Forms.Application]::EnableVisualStyles() 
    $formPowershellNetworkPin = New-Object 'System.Windows.Forms.Form' 
    $labelOutput = New-Object 'System.Windows.Forms.Label' 
    $labelCreatedByAmanDhallyW = New-Object 'System.Windows.Forms.Label' 
    $labelSubject = New-Object 'System.Windows.Forms.Label' 
    $labelFrom = New-Object 'System.Windows.Forms.Label' 
    $label1 = New-Object 'System.Windows.Forms.Label' 
    $labelSMTPServer = New-Object 'System.Windows.Forms.Label' 
    $textbox9 = New-Object 'System.Windows.Forms.TextBox' 
    $textbox8 = New-Object 'System.Windows.Forms.TextBox' 
    $textbox7 = New-Object 'System.Windows.Forms.TextBox' 
    $textbox6 = New-Object 'System.Windows.Forms.TextBox' 
    $buttonEMailOutput = New-Object 'System.Windows.Forms.Button' 
    $richtextbox1 = New-Object 'System.Windows.Forms.RichTextBox' 
    $buttonRun = New-Object 'System.Windows.Forms.Button' 
    $textbox5 = New-Object 'System.Windows.Forms.TextBox' 
    $labelTo = New-Object 'System.Windows.Forms.Label' 
    $textbox4 = New-Object 'System.Windows.Forms.TextBox' 
    $textbox3 = New-Object 'System.Windows.Forms.TextBox' 
    $textbox2 = New-Object 'System.Windows.Forms.TextBox' 
    $textbox1 = New-Object 'System.Windows.Forms.TextBox' 
    $labelIPAddressRange = New-Object 'System.Windows.Forms.Label' 
    $InitialFormWindowState = New-Object 'System.Windows.Forms.FormWindowState' 
    #endregion Generated Form Objects 
 
         
     
     
     
     
    $formPowershellNetworkPin_Load={ 
        #TODO: Initialize Form Controls here 
         
    } 
     
    $textbox1.MaxLength=3 
    $textbox2.MaxLength=3 
    $textbox3.MaxLength=3 
    $textbox4.MaxLength=3 
    $textbox5.MaxLength=3 
     
function Get-ComputerNameByIP { 
    param( 
        $IPAddress = $null 
    ) 
    BEGIN { 
    } 
    PROCESS { 
        if ($IPAddress -and $_) { 
            throw 'Please use either pipeline or input parameter' 
            break 
        } elseif ($IPAddress) { 
            ([System.Net.Dns]::GetHostbyAddress($IPAddress)).HostName 
        } elseif ($_) { 
            [System.Net.Dns]::GetHostbyAddress($_).HostName 
        } else { 
            $IPAddress = Read-Host "Please supply the IP Address" 
            [System.Net.Dns]::GetHostbyAddress($IPAddress).HostName 
        } 
    } 
    END { 
    } 
} 
 
 
     
    $buttonRun_Click={ 
         
        #TODO: Place custom script here 
        $richtextbox1.Clear() 
$PingReport = "C:\pingstatus.htm" 
#$script:list = "c:\computer.txt" 
 
 
New-Item -ItemType file $PingReport -Force 
 
# Getting the freespace info using WMI 
#Get-WmiObject win32_logicaldisk  | Where-Object {$_.drivetype -eq 3 -OR $_.drivetype -eq 2 } | format-table DeviceID, VolumeName,status,Size,FreeSpace | Out-File FreeSpace.txt 
# Function to write the HTML Header to the file 
Function writeHtmlHeader 
{ 
param($fileName) 
$date = ( get-date ).ToString('yyyy/MM/dd') 
Add-Content $fileName "<html>" 
Add-Content $fileName "<head>" 
Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>" 
Add-Content $fileName '<title>IP Scanner Report</title>' 
add-content $fileName '<STYLE TYPE="text/css">' 
add-content $fileName  "<!--" 
add-content $fileName  "td {" 
add-content $fileName  "font-family: Tahoma;" 
add-content $fileName  "font-size: 11px;" 
add-content $fileName  "border-top: 1px solid #999999;" 
add-content $fileName  "border-right: 1px solid #999999;" 
add-content $fileName  "border-bottom: 1px solid #999999;" 
add-content $fileName  "border-left: 1px solid #999999;" 
add-content $fileName  "padding-top: 0px;" 
add-content $fileName  "padding-right: 0px;" 
add-content $fileName  "padding-bottom: 0px;" 
add-content $fileName  "padding-left: 0px;" 
add-content $fileName  "}" 
add-content $fileName  "body {" 
add-content $fileName  "margin-left: 5px;" 
add-content $fileName  "margin-top: 5px;" 
add-content $fileName  "margin-right: 0px;" 
add-content $fileName  "margin-bottom: 10px;" 
add-content $fileName  "" 
add-content $fileName  "table {" 
add-content $fileName  "border: thin solid #000000;" 
add-content $fileName  "}" 
add-content $fileName  "-->" 
add-content $fileName  "</style>" 
Add-Content $fileName "</head>" 
Add-Content $fileName "<body>" 
 
add-content $fileName  "<table width='100%'>" 
add-content $fileName  "<tr bgcolor='#CCCCCC'>" 
add-content $fileName  "<td colspan='2' height='25' align='center'>" 
add-content $fileName  "<font face='tahoma' color='#003399' size='4'><strong>IP Scanner Report - $date</strong></font>" 
add-content $fileName  "</td>" 
add-content $fileName  "</tr>" 
add-content $fileName  "</table>" 
 
} 
 
# Function to write the HTML Header to the file 
Function writeTableHeader 
{ 
param($fileName) 
 
Add-Content $fileName "<tr bgcolor=#CCCCCC>" 
Add-Content $fileName "<td width='10%' align='center'>Name</td>" 
Add-Content $fileName "<td width='50%' align='center'>Status Label</td>" 
Add-Content $fileName "</tr>" 
} 
 
Function writeHtmlFooter 
{ 
param($fileName) 
 
Add-Content $fileName "</body>" 
Add-Content $fileName "</html>" 
} 
 
Function writeDiskInfo 
{ 
param($fileName,$name,$status) 
if ($status -eq $TRUE) 
{ 
Add-Content $fileName "<tr>" 
Add-Content $fileName "<td>$Name</td>" 
Add-Content $fileName "<td>Is alive and Pinging</td>" 
} 
else 
{ 
Add-Content $fileName "<tr>" 
Add-Content $fileName "<td>$Name</td>" 
Add-Content $fileName "<td bgcolor='#FF0000'>Seems dead and not Pinging</td>" 
Add-Content $fileName "</tr>" 
} 
 
} 
 
 
writeHtmlHeader $PingReport 
 
Add-Content $PingReport "<table width='100%'><tbody>" 
Add-Content $PingReport "<tr bgcolor='#CCCCCC'>" 
Add-Content $PingReport "<td width='100%' align='center' colSpan=2><font face='tahoma' color='#003399' size='2'><strong> IP and Host Status </strong></font></td>" 
Add-Content $PingReport "</tr>" 
 
        $result = @() 
        $startip = $textbox1.Text + "." + $textbox2.Text +  "." + $textbox3.Text + "." + $textbox4.Text 
        $ip = $textbox5.Text  
         
        foreach ($i in $startip) {  
        $last = $i.Split(".") 
        [int]$lastip = $last[3] 
        while ( $lastip -le $ip ) {  
        $result +=  $textbox1.Text + "." + $textbox2.Text +  "." + $textbox3.Text + "." +  $lastip++ } }     
         
        foreach ( $computer in $result ) { 
        if (test-Connection -ComputerName $computer -Count 1 -Quiet ) { 
            $Name= Get-ComputerNameByIP -IPAddress $computer 
               $richtextbox1.Text += "$computer -> $Name is alive and Pinging."  
            $richtextbox1.Text += "`n" 
            writeDiskInfo $PingReport "$computer -> $Name" $TRUE 
            $formPowershellNetworkPin.Refresh()                 
            } else     {  
             
            $richtextbox1.Text +=  " $computer -> seems dead and not Pinging."  
            $richtextbox1.Text += "`n"     
            writeDiskInfo $PingReport $computer $FALSE 
            $formPowershellNetworkPin.Refresh()     
             
            } 
         
 
     } 
      Add-Content $PingReport "</table>"  
     
    } 
     
   ##writeHtmlFooter $PingReport  
     
     
    $buttonClear_Click={ 
        #TODO: Place custom script here 
         
    } 
     
Function sendEmail   
{  
param($from,$to,$subject,$smtphost,$htmlFileName)   
[string]$receipients="$to" 
$body = Get-Content $htmlFileName  
$body = New-Object System.Net.Mail.MailMessage $from, $receipients, $subject, $body  
$body.isBodyhtml = $true 
$smtpServer = $MailServer 
$smtp = new-object Net.Mail.SmtpClient($smtphost) 
$smtp.Send($body) 
} 
 
#Call Function  
#sendEmail -from $From -to $to -subject "System Report : $computers" -smtphost $SMTPMail -htmlfilename c:\report.html 
 
 
     
    $buttonEMailOutput_Click={ 
        #TODO: Place custom script here 
        $smtp = $textbox6.Text 
        $to =   $textbox7.Text 
        $from = $textbox8.Text 
        $sub =  $textbox9.Text 
        $body = $richtextbox1.Text 
        sendEmail -from $From -to $to -subject $sub -smtphost $smtp -htmlfilename c:\pingstatus.htm 
        if($?) 
        { 
        [void][System.Windows.Forms.MessageBox]::Show("Email Sent") 
        } 
         
 
    } 
     
    $labelSMTPServer_Click={ 
        #TODO: Place custom script here 
         
    } 
     
    # --End User Generated Script-- 
    #---------------------------------------------- 
    #region Generated Events 
    #---------------------------------------------- 
     
    $Form_StateCorrection_Load= 
    { 
        #Correct the initial state of the form to prevent the .Net maximized form issue 
        $formPowershellNetworkPin.WindowState = $InitialFormWindowState 
    } 
     
    $Form_Cleanup_FormClosed= 
    { 
        #Remove all event handlers from the controls 
        try 
        { 
            $labelSMTPServer.remove_Click($labelSMTPServer_Click) 
            $buttonEMailOutput.remove_Click($buttonEMailOutput_Click) 
            $buttonRun.remove_Click($buttonRun_Click) 
            $formPowershellNetworkPin.remove_Load($formPowershellNetworkPin_Load) 
            $formPowershellNetworkPin.remove_Load($Form_StateCorrection_Load) 
            $formPowershellNetworkPin.remove_FormClosed($Form_Cleanup_FormClosed) 
        } 
        catch [Exception] 
        { } 
    } 
    #endregion Generated Events 
 
    #---------------------------------------------- 
    #region Generated Form Code 
    #---------------------------------------------- 
    # 
    # formPowershellNetworkPin 
    # 
    $formPowershellNetworkPin.Controls.Add($labelOutput) 
    $formPowershellNetworkPin.Controls.Add($labelCreatedByAmanDhallyW) 
    $formPowershellNetworkPin.Controls.Add($labelSubject) 
    $formPowershellNetworkPin.Controls.Add($labelFrom) 
    $formPowershellNetworkPin.Controls.Add($label1) 
    $formPowershellNetworkPin.Controls.Add($labelSMTPServer) 
    $formPowershellNetworkPin.Controls.Add($textbox9) 
    $formPowershellNetworkPin.Controls.Add($textbox8) 
    $formPowershellNetworkPin.Controls.Add($textbox7) 
    $formPowershellNetworkPin.Controls.Add($textbox6) 
    $formPowershellNetworkPin.Controls.Add($buttonEMailOutput) 
    $formPowershellNetworkPin.Controls.Add($richtextbox1) 
    $formPowershellNetworkPin.Controls.Add($buttonRun) 
    $formPowershellNetworkPin.Controls.Add($textbox5) 
    $formPowershellNetworkPin.Controls.Add($labelTo) 
    $formPowershellNetworkPin.Controls.Add($textbox4) 
    $formPowershellNetworkPin.Controls.Add($textbox3) 
    $formPowershellNetworkPin.Controls.Add($textbox2) 
    $formPowershellNetworkPin.Controls.Add($textbox1) 
    $formPowershellNetworkPin.Controls.Add($labelIPAddressRange) 
    $formPowershellNetworkPin.ClientSize = '463, 326' 
    $formPowershellNetworkPin.FormBorderStyle = 'Fixed3D' 
    $formPowershellNetworkPin.Name = "formPowershellNetworkPin" 
    $formPowershellNetworkPin.Opacity = 0.95 
    $formPowershellNetworkPin.StartPosition = 'CenterScreen' 
    $formPowershellNetworkPin.Text = "Powershell IP Scanner" 
    $formPowershellNetworkPin.add_Load($formPowershellNetworkPin_Load) 
    # 
    # labelOutput 
    # 
    $labelOutput.Location = '12, 44' 
    $labelOutput.Name = "labelOutput" 
    $labelOutput.Size = '65, 16' 
    $labelOutput.TabIndex = 21 
    $labelOutput.Text = "Output |" 
    # 
 
    # 
    # labelSubject 
    # 
    $labelSubject.Location = '163, 237' 
    $labelSubject.Name = "labelSubject" 
    $labelSubject.Size = '100, 20' 
    $labelSubject.TabIndex = 18 
    $labelSubject.Text = "Subject" 
    # 
    # labelFrom 
    # 
    $labelFrom.Location = '17, 238' 
    $labelFrom.Name = "labelFrom" 
    $labelFrom.Size = '100, 19' 
    $labelFrom.TabIndex = 17 
    $labelFrom.Text = "From" 
    # 
    # label1 
    # 
    $label1.Location = '163, 188' 
    $label1.Name = "label1" 
    $label1.Size = '92, 23' 
    $label1.TabIndex = 16 
    $label1.Text = "To" 
    # 
    # labelSMTPServer 
    # 
    $labelSMTPServer.Location = '17, 188' 
    $labelSMTPServer.Name = "labelSMTPServer" 
    $labelSMTPServer.Size = '150, 23' 
    $labelSMTPServer.TabIndex = 15 
    $labelSMTPServer.Text = "SMTP Server" 
    $labelSMTPServer.add_Click($labelSMTPServer_Click) 
    # 
    # textbox9 
    # 
    $textbox9.Location = '163, 260' 
    $textbox9.Name = "textbox9" 
    $textbox9.Size = '150, 20' 
    $textbox9.TabIndex = 14 
    # 
    # textbox8 
    # 
    $textbox8.Location = '12, 260' 
    $textbox8.Name = "textbox8" 
    $textbox8.Size = '150, 20' 
    $textbox8.TabIndex = 13 
    # 
    # textbox7 
    # 
    $textbox7.Location = '163, 214' 
    $textbox7.Name = "textbox7" 
    $textbox7.Size = '150, 20' 
    $textbox7.TabIndex = 12 
    # 
    # textbox6 
    # 
    $textbox6.Location = '12, 214' 
    $textbox6.Name = "textbox6" 
    $textbox6.Size = '150, 20' 
    $textbox6.TabIndex = 11 
    # 
    # buttonEMailOutput 
    # 
    $buttonEMailOutput.Location = '337, 214' 
    $buttonEMailOutput.Name = "buttonEMailOutput" 
    $buttonEMailOutput.Size = '65, 65' 
    $buttonEMailOutput.TabIndex = 16 
    $buttonEMailOutput.Text = "E-Mail Output" 
    $buttonEMailOutput.UseVisualStyleBackColor = $True 
    $buttonEMailOutput.add_Click($buttonEMailOutput_Click) 
    # 
    # richtextbox1 
    # 
    $richtextbox1.Location = '12, 63' 
    $richtextbox1.Name = "richtextbox1" 
    $richtextbox1.Size = '427, 101' 
    $richtextbox1.TabIndex = 8 
    $richtextbox1.Text = "" 
    # 
    # buttonRun 
    # 
    $buttonRun.Location = '350, 20' 
    $buttonRun.Name = "buttonRun" 
    $buttonRun.Size = '55, 23' 
    $buttonRun.TabIndex = 7 
    $buttonRun.Text = "Run" 
    $buttonRun.UseVisualStyleBackColor = $True 
    $buttonRun.add_Click($buttonRun_Click) 
    # 
    # textbox5 
    # 
    $textbox5.Location = '303, 21' 
    $textbox5.Name = "textbox5" 
    $textbox5.Size = '28, 20' 
    $textbox5.TabIndex = 6 
    # 
    # labelTo 
    # 
    $labelTo.Location = '263, 23' 
    $labelTo.Name = "labelTo" 
    $labelTo.Size = '100, 23' 
    $labelTo.TabIndex = 5 
    $labelTo.Text = "-- to --" 
    # 
    # textbox4 
    # 
    $textbox4.Location = '228, 21' 
    $textbox4.Name = "textbox4" 
    $textbox4.Size = '27, 20' 
    $textbox4.TabIndex = 4 
    # 
    # textbox3 
    # 
    $textbox3.Location = '192, 21' 
    $textbox3.Name = "textbox3" 
    $textbox3.Size = '27, 20' 
    $textbox3.TabIndex = 3 
    # 
    # textbox2 
    # 
    $textbox2.Location = '157, 21' 
    $textbox2.Name = "textbox2" 
    $textbox2.Size = '27, 20' 
    $textbox2.TabIndex = 2 
    # 
    # textbox1 
    # 
    $textbox1.Location = '123, 21' 
    $textbox1.Name = "textbox1" 
    $textbox1.Size = '27, 20' 
    $textbox1.TabIndex = 1 
    # 
    # labelIPAddressRange 
    # 
    $labelIPAddressRange.Location = '15, 21' 
    $labelIPAddressRange.Name = "labelIPAddressRange" 
    $labelIPAddressRange.Size = '112, 23' 
    $labelIPAddressRange.TabIndex = 0 
    $labelIPAddressRange.Text = "IP-Address Range :" 
    #endregion Generated Form Code 
 
    #---------------------------------------------- 
 
    #Save the initial state of the form 
    $InitialFormWindowState = $formPowershellNetworkPin.WindowState 
    #Init the OnLoad event to correct the initial state of the form 
    $formPowershellNetworkPin.add_Load($Form_StateCorrection_Load) 
    #Clean up the control events 
    $formPowershellNetworkPin.add_FormClosed($Form_Cleanup_FormClosed) 
    #Show the Form 
    return $formPowershellNetworkPin.ShowDialog() 
 
} #End Function 
 
#Call OnApplicationLoad to initialize 
if((OnApplicationLoad) -eq $true) 
{ 
    #Call the form 
    Call-PIng_Network_Community_v2_pff | Out-Null 
    #Perform cleanup 
    OnApplicationExit 
} 
}  
     
Network_Pinger

Posted in Uncategorized | Tagged HTML Format, IP scanner, Network Ping | Leave a comment

PowerShell – System Information

Posted on March 17, 2014 by Prashanth Jayaram

Different ways to find system information are given below:-

Method 1:Querying WMI objects

Download the code SystemInfo
Create the function with Powershell console and call the function with ComputerName as its argument

.SYNOPSIS 
Get Complete details of any server Local or remote 
.DESCRIPTION 
This function uses WMI class to connect to remote machine and get all related details 
.PARAMETER COMPUTERNAMES 
Just Pass computer name as Its parameter 
.EXAMPLE  
Get-SystemInfo 
.EXAMPLE  
Get-SystemInfo -ComputerName HQSPDBSP01 
.NOTES 
To get help: 
Get-Help Get-SystemInfo 
.LINK 
https://sqlpowershell.wordpress.com

Function Call: 
PS:\>Get-SystemInfo -ComputerName <computername>

Ouptut:-

Method 2:- Reference from Powershell Tip

Use of systeminfo.exe with Powershell. Copy and Paste the below code and call the function

function Get-SystemInfo 
{ 
  param($ComputerName = $env:ComputerName) 

      $header = 'Hostname','OSName','OSVersion','OSManufacturer','OSConfig','Buildtype', 'RegisteredOwner','RegisteredOrganization','ProductID','InstallDate', 'StartTime','Manufacturer','Model','Type','Processor','BIOSVersion', 'WindowsFolder' ,'SystemFolder','StartDevice','Culture', 'UICulture', 'TimeZone','PhysicalMemory', 'AvailablePhysicalMemory' , 'MaxVirtualMemory', 'AvailableVirtualMemory','UsedVirtualMemory','PagingFile','Domain' ,'LogonServer','Hotfix','NetworkAdapter' 
      systeminfo.exe /FO CSV /S $ComputerName |  
            Select-Object -Skip 1 |  
            ConvertFrom-CSV -Header $header 
}

Output:-

Posted in Uncategorized | Tagged SystemInfo Gridview, systeminfor.exe, WMI object | Leave a comment

SQL Jobs – Complete Information – SQL 2000/2005/2008/R2

Posted on March 11, 2014 by Prashanth Jayaram

SQL Job details – The script can be executed across all SQL version from SQL 2000 onwards.

The below are the columns listed in the output

ServerName
ScheduleID
ScheduleName
IsEnabled
ScheduleType
Occurrence
Recurrence
Frequency
ScheduleUsage
StartDate
ScheduleUsage
EndDate
ScheduleCreatedOn
ScheduleLastModifiedOn

For more information about the columns refer the below the link

http://technet.microsoft.com/en-us/library/ms178644.aspx

Download :- SQL Jobs Details

Code:-

IF  (left(cast(SERVERPROPERTY('ProductVersion')as varchar),5)='10.00' and SERVERPROPERTY('EngineEdition')=3) OR (left(cast(SERVERPROPERTY('ProductVersion')as varchar),5)='10.50' and SERVERPROPERTY('EngineEdition')in(2,3)) OR (left(cast(SERVERPROPERTY('ProductVersion')as varchar),5)='9.00.' and SERVERPROPERTY('EngineEdition')in(2,3)) 
BEGIN 
DECLARE @DML1 VARCHAR(8000) 
DECLARE @DML2 VARCHAR(8000) 
DECLARE @DML3 VARCHAR(8000) 

SET @DML1='SELECT '''+ @@Servername +''' ServerName, 
    [Schedule_UID] AS [ScheduleID] 
    , [name] AS [ScheduleName] 
    , CASE [enabled] 
        WHEN 1 THEN ''Yes'' 
        WHEN 0 THEN ''No'' 
      END AS [IsEnabled] 
    , CASE  
        WHEN [freq_type] = 64 THEN ''Start automatically when SQL Server Agent starts'' 
        WHEN [freq_type] = 128 THEN ''Start whenever the CPUs become idle'' 
        WHEN [freq_type] IN (4,8,16,32) THEN ''Recurring'' 
        WHEN [freq_type] = 1 THEN ''One Time'' 
      END [ScheduleType] 
    , CASE [freq_type] 
        WHEN 1 THEN ''One Time'' 
        WHEN 4 THEN ''Daily'' 
        WHEN 8 THEN ''Weekly'' 
        WHEN 16 THEN ''Monthly'' 
        WHEN 32 THEN ''Monthly - Relative to Frequency Interval'' 
        WHEN 64 THEN ''Start automatically when SQL Server Agent starts'' 
        WHEN 128 THEN ''Start whenever the CPUs become idle'' 
      END [Occurrence] 
    , 
     CASE [freq_type] 
        WHEN 4 THEN ''Occurs every '' + CAST([freq_interval] AS VARCHAR(3)) + '' day(s)'' 
        WHEN 8 THEN ''Occurs every '' + CAST([freq_recurrence_factor] AS VARCHAR(3))  
                    + '' week(s) on '' 
                    + CASE WHEN [freq_interval] & 1 = 1 THEN ''Sunday'' ELSE '''' END 
                    + CASE WHEN [freq_interval] & 2 = 2 THEN '', Monday'' ELSE '''' END 
                    + CASE WHEN [freq_interval] & 4 = 4 THEN '', Tuesday'' ELSE '''' END 
                    + CASE WHEN [freq_interval] & 8 = 8 THEN '', Wednesday'' ELSE '''' END 
                    + CASE WHEN [freq_interval] & 16 = 16 THEN '', Thursday'' ELSE '''' END 
                    + CASE WHEN [freq_interval] & 32 = 32 THEN '', Friday'' ELSE '''' END 
                    + CASE WHEN [freq_interval] & 64 = 64 THEN '', Saturday'' ELSE '''' END 
        WHEN 16 THEN ''Occurs on Day '' + CAST([freq_interval] AS VARCHAR(3))  
                     + '' of every '' 
                     + CAST([freq_recurrence_factor] AS VARCHAR(3)) + '' month(s)'' 
        WHEN 32 THEN ''Occurs on '' 
                     + CASE [freq_relative_interval] 
                        WHEN 1 THEN ''First'' 
                        WHEN 2 THEN ''Second'' 
                        WHEN 4 THEN ''Third'' 
                        WHEN 8 THEN ''Fourth'' 
                        WHEN 16 THEN ''Last'' 
                       END 
                     + '' ''  
                     + CASE [freq_interval] 
                        WHEN 1 THEN ''Sunday'' 
                        WHEN 2 THEN ''Monday'' 
                        WHEN 3 THEN ''Tuesday'' 
                        WHEN 4 THEN ''Wednesday'' 
                        WHEN 5 THEN ''Thursday'' 
                        WHEN 6 THEN ''Friday'' 
                        WHEN 7 THEN ''Saturday'' 
                        WHEN 8 THEN ''Day'' 
                        WHEN 9 THEN ''Weekday'' 
                        WHEN 10 THEN ''Weekend day'' 
                       END 
                     + '' of every '' + CAST([freq_recurrence_factor] AS VARCHAR(3))  
                     + '' month(s)'' 
      END AS [Recurrence] 
    , 

   CASE [freq_subday_type] 
        WHEN 1 THEN ''Occurs once at ''  
                    + STUFF( 
                 STUFF(RIGHT(''000000'' + CAST([active_start_time] AS VARCHAR(6)), 6) 
                                , 3, 0, '':'') 
                            , 6, 0, '':'') 
        WHEN 2 THEN ''Occurs every ''  
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + '' Second(s) between ''  
                    + STUFF( 
                   STUFF(RIGHT(''000000'' + CAST([active_start_time] AS VARCHAR(6)), 6) 
                                , 3, 0, '':'') 
                           , 6, 0, '':'') 
                    + '' & ''  
                    + STUFF( 
                    STUFF(RIGHT(''000000'' + CAST([active_end_time] AS VARCHAR(6)), 6) 
                                , 3, 0, '':'') 
                            , 6, 0, '':'')' 
        SET @DML2='WHEN 4 THEN ''Occurs every ''  
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + '' Minute(s) between ''  
                    + STUFF( 
                   STUFF(RIGHT(''000000'' + CAST([active_start_time] AS VARCHAR(6)), 6) 
                                , 3, 0, '':'') 
                            , 6, 0, '':'') 
                    + '' & ''  
                    + STUFF( 
                    STUFF(RIGHT(''000000'' + CAST([active_end_time] AS VARCHAR(6)), 6) 
                                , 3, 0, '':'') 
                            , 6, 0, '':'') 
        WHEN 8 THEN ''Occurs every ''  
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + '' Hour(s) between ''  
                    + STUFF( 
                    STUFF(RIGHT(''000000'' + CAST([active_start_time] AS VARCHAR(6)), 6) 
                                , 3, 0, '':'') 
                            , 6, 0, '':'') 
                    + '' & ''  
                    + STUFF( 
                    STUFF(RIGHT(''000000'' + CAST([active_end_time] AS VARCHAR(6)), 6) 
                                , 3, 0, '':'') 
                            , 6, 0, '':'') 
      END [Frequency] 
    , STUFF( 
            STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, ''-'') 
                , 8, 0, ''-'') AS [ScheduleUsageStartDate] 
    , STUFF( 
            STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, ''-'') 
                , 8, 0, ''-'') AS [ScheduleUsageEndDate] 
    , [date_created] AS [ScheduleCreatedOn] 
    , [date_modified] AS [ScheduleLastModifiedOn] 
FROM [msdb].[dbo].[sysschedules] 
ORDER BY [ScheduleName] 
' 

SET @DML3=@DML1+@DML2 
exec (@DML3) 

END 
IF  (left(cast(SERVERPROPERTY('ProductVersion')as varchar),5)='8.00.' and SERVERPROPERTY('EngineEdition')in(2,3)) 
BEGIN 

SET @DML1='SELECT '''+ @@Servername +''' ServerName, 
    so.[job_id] AS [ScheduleID] 
    , so.[name] AS [ScheduleName] 
    , CASE so.[enabled] 
        WHEN 1 THEN ''Yes'' 
        WHEN 0 THEN ''No'' 
      END AS [IsEnabled] 
    , CASE  
        WHEN [freq_type] = 64 THEN ''Start automatically when SQL Server Agent starts'' 
        WHEN [freq_type] = 128 THEN ''Start whenever the CPUs become idle'' 
        WHEN [freq_type] IN (4,8,16,32) THEN ''Recurring'' 
        WHEN [freq_type] = 1 THEN ''One Time'' 
      END [ScheduleType] 
    , CASE [freq_type] 
        WHEN 1 THEN ''One Time'' 
        WHEN 4 THEN ''Daily'' 
        WHEN 8 THEN ''Weekly'' 
        WHEN 16 THEN ''Monthly'' 
        WHEN 32 THEN ''Monthly - Relative to Frequency Interval'' 
        WHEN 64 THEN ''Start automatically when SQL Server Agent starts'' 
        WHEN 128 THEN ''Start whenever the CPUs become idle'' 
      END [Occurrence] 
    , 
     CASE [freq_type] 
        WHEN 4 THEN ''Occurs every '' + CAST([freq_interval] AS VARCHAR(3)) + '' day(s)'' 
        WHEN 8 THEN ''Occurs every '' + CAST([freq_recurrence_factor] AS VARCHAR(3))  
                    + '' week(s) on '' 
                    + CASE WHEN [freq_interval] & 1 = 1 THEN ''Sunday'' ELSE '''' END 
                    + CASE WHEN [freq_interval] & 2 = 2 THEN '', Monday'' ELSE '''' END 
                    + CASE WHEN [freq_interval] & 4 = 4 THEN '', Tuesday'' ELSE '''' END 
                    + CASE WHEN [freq_interval] & 8 = 8 THEN '', Wednesday'' ELSE '''' END 
                    + CASE WHEN [freq_interval] & 16 = 16 THEN '', Thursday'' ELSE '''' END 
                    + CASE WHEN [freq_interval] & 32 = 32 THEN '', Friday'' ELSE '''' END 
                    + CASE WHEN [freq_interval] & 64 = 64 THEN '', Saturday'' ELSE '''' END 
        WHEN 16 THEN ''Occurs on Day '' + CAST([freq_interval] AS VARCHAR(3))  
                     + '' of every '' 
                     + CAST([freq_recurrence_factor] AS VARCHAR(3)) + '' month(s)'' 
        WHEN 32 THEN ''Occurs on '' 
                     + CASE [freq_relative_interval] 
                        WHEN 1 THEN ''First'' 
                        WHEN 2 THEN ''Second'' 
                        WHEN 4 THEN ''Third'' 
                        WHEN 8 THEN ''Fourth'' 
                        WHEN 16 THEN ''Last'' 
                       END 
                     + '' ''  
                     + CASE [freq_interval] 
                        WHEN 1 THEN ''Sunday'' 
                        WHEN 2 THEN ''Monday'' 
                        WHEN 3 THEN ''Tuesday'' 
                        WHEN 4 THEN ''Wednesday'' 
                        WHEN 5 THEN ''Thursday'' 
                        WHEN 6 THEN ''Friday'' 
                        WHEN 7 THEN ''Saturday'' 
                        WHEN 8 THEN ''Day'' 
                        WHEN 9 THEN ''Weekday'' 
                        WHEN 10 THEN ''Weekend day'' 
                       END 
                     + '' of every '' + CAST([freq_recurrence_factor] AS VARCHAR(3))  
                     + '' month(s)'' 
      END AS [Recurrence] 
    , 

   CASE [freq_subday_type] 
        WHEN 1 THEN ''Occurs once at ''  
                    + STUFF( 
                 STUFF(RIGHT(''000000'' + CAST([active_start_time] AS VARCHAR(6)), 6) 
                                , 3, 0, '':'') 
                            , 6, 0, '':'') 
        WHEN 2 THEN ''Occurs every ''  
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + '' Second(s) between ''  
                    + STUFF( 
                   STUFF(RIGHT(''000000'' + CAST([active_start_time] AS VARCHAR(6)), 6) 
                                , 3, 0, '':'') 
                           , 6, 0, '':'') 
                    + '' & ''  
                    + STUFF( 
                    STUFF(RIGHT(''000000'' + CAST([active_end_time] AS VARCHAR(6)), 6) 
                                , 3, 0, '':'') 
                            , 6, 0, '':'')' 
        SET @DML2='WHEN 4 THEN ''Occurs every ''  
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + '' Minute(s) between ''  
                    + STUFF( 
                   STUFF(RIGHT(''000000'' + CAST([active_start_time] AS VARCHAR(6)), 6) 
                                , 3, 0, '':'') 
                            , 6, 0, '':'') 
                    + '' & ''  
                    + STUFF( 
                    STUFF(RIGHT(''000000'' + CAST([active_end_time] AS VARCHAR(6)), 6) 
                                , 3, 0, '':'') 
                            , 6, 0, '':'') 
        WHEN 8 THEN ''Occurs every ''  
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + '' Hour(s) between ''  
                    + STUFF( 
                    STUFF(RIGHT(''000000'' + CAST([active_start_time] AS VARCHAR(6)), 6) 
                                , 3, 0, '':'') 
                            , 6, 0, '':'') 
                    + '' & ''  
                    + STUFF( 
                    STUFF(RIGHT(''000000'' + CAST([active_end_time] AS VARCHAR(6)), 6) 
                                , 3, 0, '':'') 
                            , 6, 0, '':'') 
      END [Frequency] 
    , STUFF( 
            STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, ''-'') 
                , 8, 0, ''-'') AS [ScheduleUsageStartDate] 
    , STUFF( 
            STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, ''-'') 
                , 8, 0, ''-'') AS [ScheduleUsageEndDate] 
    , so.[date_created] AS [ScheduleCreatedOn] 
    , [date_modified] AS [ScheduleLastModifiedOn] 
FROM [msdb].[dbo].[sysjobschedules] sj 
inner join msdb.dbo.sysjobs so on so.job_id=sj.job_id' 

SET @DML3=@DML1+@DML2 
exec (@DML3) 
END

Posted in SQL | Tagged Job Frequency, job schedule, SQL Jobs, sysschedules | Leave a comment

SQL – Backup Report

Posted on March 5, 2014 by Prashanth Jayaram

The simple query to find the database backup status

The select statment consists of

ServerName
dbname
BackupStartDate
BackupFinishDate
BackupAge
Size
status
Type

The query will only run from sql 2005 and later version.

Select  
 SERVERPROPERTY('ServerName'), 
 db.name, 
 CONVERT(VARCHAR(10), b.backup_start_date, 103) +   + convert(VARCHAR(8), b.backup_start_date, 14) backup_start_date, 
 CONVERT(VARCHAR(10), b.backup_finish_date, 103) +   + convert(VARCHAR(8), b.backup_finish_date, 14) backup_finish_date, 
  case 
        when (DATEDIFF(hour, b.backup_start_date, getdate())<24)then 'Success' 
        when (DATEDIFF(hour, b.backup_start_date, getdate())>=24)then 'Failed' 
      end Status, 
DATEDIFF(hh, b.backup_finish_date, GETDATE())BackupAgeInHours, 
(b.backup_size/1024/1024/1024 )BackupSize, 
case b.[type] 
WHEN 'D' THEN 'Full' 
WHEN 'I' THEN 'Differential' 
WHEN 'L' THEN 'Transaction Log' 
END Type, 
ISNULL(STR(ABS(DATEDIFF(day, GetDate(),(Backup_finish_date)))), 'NEVER')DaysSinceLastBackup 
FROM sys.sysdatabases db  
Left OUTER JOIN (SELECT * , ROW_NUMBER() OVER(PARTITION BY database_name ORDER BY backup_finish_date DESC) AS RNUM 
FROM msdb.dbo.backupset) b ON b.database_name = db.name  AND RNUM = 1 
where dbid<>2

The Second part is for sending an HTML Email. This requires a dbmail configured on the server.

Download:- SQLBackupReportWithHTMLMail

DECLARE @tableHTML  NVARCHAR(MAX) ; 

 SET @tableHTML = 
  N'<H1>Databases Backup Report</H1>' + 
  N'<table border="1">' + 
  N'<tr><th>Server Name</th><th>DatabaseName</th> 
  <th>[BackupStartDate]</th> 
  <th>[BackupFinishDate]</th> 
  <th>[Status24hrs]</th> 
  <th>[BackupAge (Hours)]</th> 
  <th>BackupSizeGB</th> 
  <th>Type</th> 
  <th>DaysSinceLastBackup</th> 
  </tr>' + 
  CAST ( (    
  Select  
  td=SERVERPROPERTY('ServerName'),' ', 
  td=db.name,' ', 
  td =CONVERT(VARCHAR(10), b.backup_start_date, 103) +   + convert(VARCHAR(8), b.backup_start_date, 14),' ', 
  td=CONVERT(VARCHAR(10), b.backup_finish_date, 103) +   + convert(VARCHAR(8), b.backup_finish_date, 14),' ', 
  td= case 
        when (DATEDIFF(hour, b.backup_start_date, getdate())<24)then 'Success' 
        when (DATEDIFF(hour, b.backup_start_date, getdate())>=24)then 'Failed' 
      end,' ', 
td= DATEDIFF(hh, b.backup_finish_date, GETDATE()),' ', 
td=(b.backup_size/1024/1024/1024 ),' ', 
td=case b.[type] 
WHEN 'D' THEN 'Full' 
WHEN 'I' THEN 'Differential' 
WHEN 'L' THEN 'Transaction Log' 
END ,' ', 
td=ISNULL(STR(ABS(DATEDIFF(day, GetDate(),(Backup_finish_date)))), 'NEVER'),' ' 
FROM sys.sysdatabases db  
Left OUTER JOIN (SELECT * , ROW_NUMBER() OVER(PARTITION BY database_name ORDER BY backup_finish_date DESC) AS RNUM 
FROM msdb.dbo.backupset) b ON b.database_name = db.name  AND RNUM = 1 
where dbid<>2 
      FOR XML PATH('tr'), TYPE  
  ) AS NVARCHAR(MAX) ) + 
  N'</table>' ; 

 EXEC msdb.dbo.sp_send_dbmail @recipients='pjayaram@appvion.com', 
  @subject = 'Database Backup', 
  @body = @tableHTML, 
  @body_format = 'HTML' ;

Output:-

Posted in SQL, XML | Tagged SQL Backup Report | 4 Comments

INVENTORY – SQL- SSAS – SSRS – Excel Output – Email

Posted on March 3, 2014 by Prashanth Jayaram

Inventory

Inventory is a vital information,whether you’re a consultant or an in-house DBA, you need to have a reliable inventory of the servers you manage.This inventory can take any number of forms but, ideally, will allow some aggregation of information. When server hardware or software changes are taking place, it’s easy to forget that the inventory has to be updated as well. For this reason, I like to have a script that creates the inventory, rather than have to keep a manual log of changes as they occur.

It has five mandatory parameters

InputFileName – Text File contains a list of SQL/SSAS/SSRS Servers -C:\Server.txt(Example)
DirectoryToSave – Folder where you want to store the file
ToID – To email Address
FromID – From Email Address
SMTP – SMTP Adress

Pre-requisites are –

Windows PowerShell 2.0 must be installed
Permission to access all SQL instances
Permission to create a outputFile

Windows PowerShell 2.0 is installed by default on newer versions of the Windows operating systems.You must install a SQL Server feature that includes the SQL Server PowerShell (SQLPS) module and client libraries. The easiest way to do this is by installing SQL Server Management Studio, which includes the PowerShell feature and client libraries automatically. The SQL Server PowerShell (SQLPS) module contains the PowerShell providers and cmdlets for all SQL Server features.

The all three PowerShell scripts requires an input file, Directory to Save and Email parameters
For Example :-The C:\SQLList.txt file should list of SQL instances

SQLList.txt

apdbsp15
apdbsp17\CRM2011

SQL Server Inventory

SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server
The output is comprises of following columns

InstanceName
ComputerNamePhysicalNetBIOS
NetName
OS
OSVersion
Platform
Product
edition
Version
VersionString
ProductLevel
DatabaseCount
HasNullSaPassword
IsCaseSensitive
IsFullTextInstalled
Language
LoginMode
Processors
PhysicalMemory
MaxMemory
MinMemory
IsSingleUser
IsClustered
Collation
MasterDBLogPath
MasterDBPath
ErrorLogPath
BackupDirectory
DefaultLog
ResourceLastUpdatetime
AuditLevel
DefaultFile
xp_cmdshell
Domain
IPAddress

http://gallery.technet.microsoft.com/PowerShell-SQL-Inventory-e9b92dac

PS C:\> .\SQLServerInventory.ps1 -InputFileName C:\SQLserver.txt -DirectoryToSaveTo C:\ -To pram@abc.com -From pram@abc.com -SMTP mail.abc.com

SSAS Inventory

PowerShell allows me to do just that. PowerShell makes it easy to issue WMI (Windows Management Instrumentation) and AMO’s to pull the required details.

Analysis Management Objects (AMO) is the complete library of programmatically accessed objects that enables an application to manage a running instance of Microsoft SQL Server Analysis Services. This section explains AMO concepts, focusing on major objects, how and when to use them, and the way they are interrelated.

The 24 columns output are listed are given below

Name
ConnectionString
Domain Name
OS
CPU
TotalRAM
FreeRAM
UsedRAM
Version
Edition
EditionID
ProductLEvel
ProductName
Isloaded
DataDir
Tempdir
LogDir
BackupDir
CubeDatabaseCount
CubeDatabaseList
CreatedTimestmp
LastSchemaUpdate
Uptime
IPAddress

http://gallery.technet.microsoft.com/Powershell-Automatic-SSAS-db510158

PSC:\>.\SSASInventoryExcel.ps1 -InputFileName C:\SSASList.txt -DirectoryToSaveTo c:\ -OutputFileName SSASInventoryExcel

SSRS Inventory:-

The MSReportServer_ConfigurationSetting class represents the installation and runtime parameters of a report server instance. These parameters are stored in the configuration file for the report server

The 33 columns output are listed are given below

Name
Domain Name
OS
DatabaseServerName
CPU
TotalRAM
FreeRAM
UsedRAM
Version
DatabaseName
ExtendedProtectionLevel
ExtendedProtectionScenario
InstanceName
SenderEmailAddress
SendUsingSMTPServer
ServiceName
SMTPServer
VirtualDirectoryReportManager
VirtualDirectoryReportServer
IsInitialized
IsReportManagerEnabled
IsSharePointIntegrated
IsWebServiceEnabled
IsWindowsServiceEnabled
MachineAccountIdentity
PathName
SecureConnectionLevel
WindowsServiceIdentityActual
WindowsServiceIdentityConfigured
ConnectionPoolSize
DatabaseQueryTimeout
Uptime
IPAddress

http://gallery.technet.microsoft.com/PowerShell-SSRS-Inventory-de5d2682

PS C:\> .\SSRSInventory.ps1 -InputFileName C:\SSRSList.txt -DirectoryToSaveTo C:\ -To pram@abc.com -From pram@abc.com -SMTP mail.abc.com

Reference:-

http://technet.microsoft.com/en-us/library/ms162169.aspx

http://technet.microsoft.com/en-us/library/ms124924.aspx

http://technet.microsoft.com/en-us/library/ms154648.aspx

http://www.mssqltips.com/sqlservertip/1826/getting-started-with-sql-server-management-objects-smo/

Posted in PowerShell, SQL, SSAS, SSRS, T-SQL | Tagged AMO, Email Attachment, Excel Output, Inventory, MSReportServer_ConfigurationSetting, SMO, SQL, ssrs | 2 Comments

Prashanth Jayaram

PowerShell – OS Inventory and Disk Info – Consolidated Excel File – EMAIL

SQL – List All Tables, Space Usage Info & Other Details

PowerShell-Input-Validation Scripts

SQL – Find encrypted objects using sql_modules or Powershell

First Birthday to My Blog

PowerShell GUI Tool – Network Ping – Version 2

PowerShell – System Information

SQL Jobs – Complete Information – SQL 2000/2005/2008/R2

SQL – Backup Report

INVENTORY – SQL- SSAS – SSRS – Excel Output – Email

Inventory

Pre-requisites are –

SQLList.txt

SSAS Inventory

SSRS Inventory:-

Blog Stats

Prashanth Jayaram

Verified Services

Top 30 PowerShell bloggers of 2018

My Blog is ranked amongst the Top 50 SQL blogs worldwide

DZone MVB

Microsoft

Subscribe to Blog via Email

Archives

Top Posts & Pages

Categories

Recent Posts

PowershellSQL

Blog Stats

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Inventory

Pre-requisites are –

SQLList.txt

SSAS Inventory

SSRS Inventory:-

Share this:

Blog Stats

Verified Services

Top 30 PowerShell bloggers of 2018

My Blog is ranked amongst the Top 50 SQL blogs worldwide

DZone MVB

Microsoft

Subscribe to Blog via Email

Archives

Top Posts & Pages

Categories

Recent Posts

PowershellSQL