SQL – Find encrypted objects using sql_modules or Powershell

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: -

Posted in Uncategorized | Tagged , | Leave a comment

First Birthday to My Blog

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 | Leave a comment

PowerShell GUI Tool – Network Ping – Version 2

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,$statusif ($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 , , | Leave a comment

PowerShell – System Information

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 , , | Leave a comment

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

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

  1. ServerName
  2. ScheduleID
  3. ScheduleName
  4. IsEnabled
  5. ScheduleType
  6. Occurrence
  7. Recurrence
  8. Frequency
  9. ScheduleUsage
  10. StartDate
  11. ScheduleUsage
  12. EndDate
  13. ScheduleCreatedOn
  14. 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 , , , | Leave a comment

SQL – Backup Report

The simple query to find the database backup status

The select statment consists of

  1. ServerName
  2. dbname
  3. BackupStartDate
  4. BackupFinishDate
  5. BackupAge
  6. Size
  7. status
  8. 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.
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 | 2 Comments

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

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

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

Pre-requisites are -

  1. Windows PowerShell 2.0 must be installed
  2. Permission to access all SQL instances
  3. 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

  1. InstanceName
  2. ComputerNamePhysicalNetBIOS
  3. NetName
  4. OS
  5. OSVersion
  6. Platform
  7. Product
  8. edition
  9. Version
  10. VersionString
  11. ProductLevel
  12. DatabaseCount
  13. HasNullSaPassword
  14. IsCaseSensitive
  15. IsFullTextInstalled
  16. Language
  17. LoginMode
  18. Processors
  19. PhysicalMemory
  20. MaxMemory
  21. MinMemory
  22. IsSingleUser
  23. IsClustered
  24. Collation
  25. MasterDBLogPath
  26. MasterDBPath
  27. ErrorLogPath
  28. BackupDirectory
  29. DefaultLog
  30. ResourceLastUpdatetime
  31. AuditLevel
  32. DefaultFile
  33. xp_cmdshell
  34. Domain
  35. 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

  1. Name
  2. ConnectionString
  3. Domain Name
  4. OS
  5. CPU
  6. TotalRAM
  7. FreeRAM
  8. UsedRAM
  9. Version
  10. Edition
  11. EditionID
  12. ProductLEvel
  13. ProductName
  14. Isloaded
  15. DataDir
  16. Tempdir
  17. LogDir
  18. BackupDir
  19. CubeDatabaseCount
  20. CubeDatabaseList
  21. CreatedTimestmp
  22. LastSchemaUpdate
  23. Uptime
  24. 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

  1. Name
  2. Domain Name
  3. OS
  4. DatabaseServerName
  5. CPU
  6. TotalRAM
  7. FreeRAM
  8. UsedRAM
  9. Version
  10. DatabaseName
  11. ExtendedProtectionLevel
  12. ExtendedProtectionScenario
  13. InstanceName
  14. SenderEmailAddress
  15. SendUsingSMTPServer
  16. ServiceName
  17. SMTPServer
  18. VirtualDirectoryReportManager
  19. VirtualDirectoryReportServer
  20. IsInitialized
  21. IsReportManagerEnabled
  22. IsSharePointIntegrated
  23. IsWebServiceEnabled
  24. IsWindowsServiceEnabled
  25. MachineAccountIdentity
  26. PathName
  27. SecureConnectionLevel
  28. WindowsServiceIdentityActual
  29. WindowsServiceIdentityConfigured
  30. ConnectionPoolSize
  31. DatabaseQueryTimeout
  32. Uptime
  33. 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 , , , , , , , | 1 Comment