PowerSehll : How to suppress the table heading and remove the blank space from a text file

There are a multiple ways to suppress and remove the first line from text file. The first scenario would be writing a suppressed heading to an output text file Or after writing, we can remove the line using trim function Or using the trick shared in PowerShell magazine.

Just give you a background, we wanted to create a input a file based on some criteria and use the output  text file as an input for further processing.

For example,

I came across an requirement to uninstall the list of patches that were installed yesterday. Have to get a list of patches and store it in a file and used PowerShell script to uninstall the patches.

Using Looping

PS:\>$Computername='ABCD'
PS:\>$outFile='HotFix.txt'
PS:\>Get-HotFix -ComputerName $computerName|
where {$_.installedOn -gt "4/01/2013 12:00:00 AM "}|
ForEach-Object { $_.hotfixID >>$outFile}

Using Trim and set-content cmdlet to update the file by trimming the blank space

 

PS:\>$Computername='ABCD'
PS:\>$outFile='HotFix.txt'
PS:\>Get-HotFix -ComputerName $computername|where {$_.installedOn -gt "4/01/2013 12:00:00 AM "}|select hotFixID |out-file $outFile
PS:\>(gc $outFile) | ? {$_.trim() -ne "" } | set-content $OutFile

Using assignment and and redirecting the operator

PS:\>$Computername='ABCD'
PS:\>$outFile='HotFix.txt'
PS:\>$a,$b=get-content $OutFile 
PS:\>$b>$OutFile

removeBlank

Reference :-

remove-the-first-line-from-a-text-file/

 

Posted in PowerShell | Tagged , , | Leave a comment

PowerShell – SQL Databases Backup Status Report of Multiple Servers

 

This post is a requirement from one of my blog reader.

There are plenty of scripts available to get the backup status of the databases across multiple server. The  requirement is to read a server name and application names from an input file. The server name is used for building a connection and to get the required backup details but where as an app name is used for making an heading in a HTML body.

The Input CSV file consists of Servername, ApplicationName. In this case the App names are separated ‘/’.

InputFile

You need to change the below colored input values as per your environment setup

  • ServerList – Input file where DB Server and Application Names are Listed
  • OutputFile – Used for HTML Email body
  • emlist – You can send it more than one intended receipients. The receipients list are separated by comma
  • MailServer – Valid SMTP servername

CODE:

#Change value of following variables as needed
$ServerList = "f:\Powersql\ServerList.csv"
$OutputFile = "f:\Powersql\Output.htm"
$emlist="pjayaram@appvion.com,prashanth@abc.com"
$MailServer="maa.stmp.com"
 
$HTML = '<style type="text/css">
#Header{font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;width:100%;border-collapse:collapse;}
#Header td, #Header th {font-size:14px;border:1px solid #98bf21;padding:3px 7px 2px 7px;}
#Header th {font-size:14px;text-align:left;padding-top:5px;padding-bottom:4px;background-color:#A7C942;color:#fff;}
#Header tr.alt td {color:#000;background-color:#EAF2D3;}
</Style>'
$HTML += "<HTML><BODY><Table border=1 cellpadding=0 cellspacing=0 width=100% id=Header>
  <TR>
   <TH><B>Database Name</B></TH>
   <TH><B>RecoveryModel</B></TD>
   <TH><B>Last Full Backup Date</B></TH>
   <TH><B>Last Differential Backup Date</B></TH>
   <TH><B>Last Log Backup Date</B></TH>
   </TR>"
  
  
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
Import-Csv $ServerList |ForEach-Object {
$ServerName=$_.ServerName
$AppName=$_.ApplicationName
$HTML += "<TR bgColor='#ccff66'><TD colspan=8 align=center>$ServerName - $AppName</TD></TR>"
$SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName 
 Foreach($Database in $SQLServer.Databases)
{
$DaysSince = ((Get-Date) - $Database.LastBackupDate).Days
$DaysSinceDiff = ((Get-Date) - $Database.LastDifferentialBackupDate).Days
$DaysSinceLog = ((Get-Date) - $Database.LastLogBackupDate).Days
IF(($Database.Name) -ne 'tempdb' -and ($Database.Name) -ne 'model')
{
if ($Database.RecoveryModel -like "simple" )
{
if ($DaysSince -gt 1){
  $HTML += "<TR >
     <TD>$($Database.Name)</TD>
     <TD>$($Database.RecoveryModel)</TD>
     <TD bgcolor='RED'>$($Database.LastBackupDate)</TD>
     <TD>$($Database.LastDifferentialBackupDate)</TD>
     <TD>NA</TD>
     </TR>"
}
}
  if ($Database.RecoveryModel -like "full" )
{
if ($DaysSince -gt 1){
  $HTML += "<TR >
     <TD>$($Database.Name)</TD>
     <TD>$($Database.RecoveryModel)</TD>
     <TD bgcolor='RED'>$($Database.LastBackupDate)</TD>
     <TD>$($Database.LastDifferentialBackupDate)</TD>
     <TD>$($Database.LastLogBackupDate)</TD>
     </TR>"
}
}
if ($DaysSince -lt 1)
{
$HTML += "<TR >
     <TD>$($Database.Name)</TD>
     <TD>$($Database.RecoveryModel)</TD>
     <TD bgcolor='GREEN'>$($Database.LastBackupDate)</TD>
     <TD>$($Database.LastDifferentialBackupDate)</TD>
     <TD>$($Database.LastLogBackupDate)</TD>
     </TR>"
}
 }
}
}
 
$HTML += "</Table></BODY></HTML>"
$HTML | Out-File $OutputFile
 
Function sendEmail  
 
{ 
param($from,$to,$subject,$smtphost,$htmlFileName)  
 
$body = Get-Content $htmlFileName 
$body = New-Object System.Net.Mail.MailMessage $from, "$to", $subject, $body 
$body.isBodyhtml = $true
$smtpServer = $MailServer
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($body)
 
}  
$date = ( get-date ).ToString('yyyy/MM/dd')
$emlist
sendEmail pjayaram@appletonideas.com $emlist "Backup Report - $Date" $MailServer $OutputFile

 

Output

BackupStatusReport

Posted in Backup and Restore, PowerShell, SQL | Tagged , | Leave a comment

MongoDB : How to get Top, Bottom,Middle and Range of records

In MongoDB,the limit() method is used to limit the result set to be returned. You can also use this with various methods such as sort() and skip() for various combination of requirement.

The MS SQL equivalent is TOP function

>SELECT TOP 10 * FROM <TABLENAME>

Some examples are

Top ‘N’Record

db.categories.find().sort({$natural:1}).limit(10 )

Bottom ‘N’ Record

db.categories.find().sort({$natural:-1}).limit(10 )

Middle Record

db.categories.find().skip(db.categories.count()/2).limit(1)

Range of records from 3 and then select 4 records

>var startRange=3
> var EndRange=4
> db.categories.find().skip(startRange).limit(EndRange)

Output:

TopMiddle

 

Posted in MongoDB | Tagged , , | Leave a comment

MongoDB – Insert,Update,Upsert and Delete Examples – CRUD

In MongoDB we have to use either insert() or save() method to add the document to a collection

Insert Single document

> db.employee.insert( 
    { 
    "employee_id":1101, 
    "name":"Prashanth", 
    "sal":90000, 
    "dob"new Date(1983,2,3,5,20), 
    "department": 
        [ 
            'DB Amdin','DB Developer' 
        ], 
    "Location":"New York" 
    });

Insert Multiple document –

We have to use an array to pass multiple documents and its enclosed in a square brackets,separated by comma.

>db.employee.save( 
[ 
{ 
"employee_id":1102"name":"Jayaram""sal":95000"dob"new Date(1983,18,4,12,05), 
"department":"Web Admin""Location":"New York" 
}{ 
"employee_id":1103"name":"Pravitha""sal":195000"dob"new Date(2015,07,06,11,22), 
"department":['Health Science','Scientist'], 
"Location":"NJ" 
}{ 
"employee_id":1104"name":"Prarthana""sal":295000"dob"new Date(2015,07,06,11,23), 
"department":['Engineer','Pilot'], 
"Location":"NJ" 
}, 
{
"employee_id":1105"name":"Ambika""sal":80000"dob"new Date(1983,2,3,5,20), 
"department":['DB Amdin','DB Developer'], 
"Location":"Dallas" 
}, 
] 
);

Mongo DB’s Update

Mongo DB’s update() method used to update values of an existing document

  • Update the document Prashan and set the sal to 100K
>db.employee.update({name: 'Prashan'},{$set: {sal: 100000}})
  •   Update sal to 10000 for all. To update multiple document you need to set a parameter multi to true.

>db.employee.update({},{$set: {sal:10000}},{multi:true})

Use of Multiply operator in Update

  • multiply the sal by 10 where sal is > 10000 for all
>db.employee.update({sal:{$gt:10000}},{$mul: {sal:10}},{multi:true})

EmployeesalaryUpdate

 

The Users document

The users collection has four documents and its details are as follows

 

>db.users.insert(

[{
"name" : "Prayer",
"age" : 1,
"status" : "B"
},
{
"name" : "Pravitha",
"age" : 10,
"status" : "C"
},
{
"name" : "Prashanth",
"age" : 33,
"status" : "A"
},
{
"name" : "Ambika",
"age" : 32,
"status" : "E"
}])
 In the below example, the status is update to “N” where age>18 of the users collection
>db.users.update({age:{$gt:18}},{$set: {status:"N"}},{multi:true})

userUpdateUse Multiple criteria’s to update the document

  • In the below example, the status is updated to “New” where age<15 and Name is Pravitha of the users collection
>db.users.update( {"age": {$lt :15}, "name" : "Pravitha" }, { $set : { status: "New" } } )

Use Regular Expression

  • In the below example, the search parameter ‘Pra’ is searched and updated its status to “Yes”
>var search='Pra'
>db.users.find({name : new RegExp(search)}).forEach(function(doc) { db.users.update({_id:doc._id},{$set:{"status":"Yes"}})})

 UsersUpdate.jpg

MongoDB’s Upserts

• Upserts are a special type of inserts that allows a document to be inserted if it is not found by the update criteria
• We specify upsert: true inside the db.collection.update function

>db.employee.update({name: 'Prayer'},{$set: {sal: 100000}},{upsert:true})

MongoDB’s Remove

MongoDB’s remove() method is used to remove document from the collection

• To delete a document from the database, we can use db.collection.remove() function.
• To remove all documents, we can use – db.collection.remove({})

Remove a document

>db.employee.remove({"name":"Prashanth"})

Remove a document based on condition

>db.employee.remove({"sal":{$lt:85000}})

Remove All documents

>db.employee.remove({})
Posted in MongoDB, Uncategorized | Tagged , , , , , , | Leave a comment

MongoDB – How to Copy Database

Using copydb

Run the copydb under admin database with from,to, host parameters

>use admin
switched to db admin
> db.runCommand({ copydb: 1, fromdb: “test”, todb: “new_test”, fromhost: “localhost” })

Using copyDatabase method

The db.copyDatabase() method is used to copy a database.

>db.copyDatabase(“source_test”,”TargetTest”,”LocalHost”)

CopyDatabase

 

Posted in MongoDB | Tagged , , | Leave a comment

MongoDB – List All the databases

The function getDBs() function returns all the database list

db.getMongo().getDBs()

The runCommand is a special type of query called a database command and its is used database administration. The listDatabases parameter provides a list of all existing databases.

You can see all commands by running the db.listCommands() command.

db.runCommand({ “listDatabases”: 1 }).databases;

ListDatabases

Posted in MongoDB | Tagged , , , | Leave a comment

SQL DB Details Of Multiple Servers Using PowerShell

 

One of my blog reader requested me to get the DB details(Server,DBName,Size,SpaceAvailable,LogSize,PercentUsed Log Space,compatiblilty mode, recovery model etc:-) using PowerShell hence this post is written.

The power of PowerShell is to capture an output of DBCC commands and store it an variable for later processing of the desired result

I’m storing the log usage details from  DBCC SQLPERF SQL command. Executing the below command gives us the required details and then join with DB to fetch other requested details

$db.ExecuteWithResults("DBCC SQLPERF(LOGSPACE)").Tables[0]

CODE: Change the below marked fields to get an output as per your environment


[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

#let's get our list of servers. For this, create a .txt files with all the server names you want to check.
$sqlservers = Get-Content "f:\powersql\server.txt";
$FileOutput= "f:\PowerSQL\FileOutput.htm" 
New-Item -ItemType file $FileOutput -Force

#we'll get the long date and toss that in a variable
$datefull = Get-Date
#and shorten it
$today = $datefull.ToShortDateString()

#let's set up the email stuff
$from="pjayaram@appvion.com"
$to="pjayaram@appvion.com"
$smtp_address = "Valid SMTP Address"

#Formatting a message body

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>SQL Job Failure 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='10' height='25' align='center'>"
add-content $fileName "<font face='tahoma' color='#003399' size='4'><strong>FileOutput - $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='LEFT'>ServerName</td>"
Add-Content $fileName "<td width='50%' align='left'>DBName</td>"
Add-Content $fileName "<td width='10%' align='left'>DBSizeGB</td>"
Add-Content $fileName "<td width='10%' align='left'>DBSpaceAvailableGB</td>"
Add-Content $fileName "<td width='10%' align='left'>LogSizeMB</td>"
Add-Content $fileName "<td width='10%' align='left'>LogSpaceUsedPer</td>"
Add-Content $fileName "<td width='10%' align='left'>LogFreeSpaceMB</td>"
Add-Content $fileName "<td width='10%' align='left'>Collation</td>"
Add-Content $fileName "<td width='10%' align='left'>RecoveryModel</td>"
Add-Content $fileName "<td width='10%' align='left'>AutoShrink</td>"
Add-Content $fileName "</tr>"
}

Function writeHtmlFooter
{
param($fileName)

Add-Content $fileName "</body>"
Add-Content $fileName "</html>"
}

Function writeInfo
{
param($filename,$sqlserver,$Name,$DBsize,$dbSpaceAvailable,$logSizeMB,$SpaceUsedPercent,$collation,$recoveryModel,$autoShrink)
Add-Content $fileName "<tr>"
Add-Content $fileName "<td align=left ><b>$instanceName</td>"
Add-Content $fileName "<td align=left ><b>$name</td>"
Add-Content $fileName "<td align=left ><b>$DBsize</td>"
Add-Content $fileName "<td align=left ><b>$dbSpaceAvailable</td>"
Add-Content $fileName "<td align=left ><b>$logSizeMB</td>"
Add-Content $fileName "<td align=left ><b>$logFreeSpaceMB</td>"
Add-Content $fileName "<td align=left ><b>$SpaceUsedPercent</td>"
Add-Content $fileName "<td align=left ><b>$collation</td>"
Add-Content $fileName "<td align=left ><b>$recoveryModel</td>"
Add-Content $fileName "<td align=left ><b>$autoShrink</td>"
Add-Content $fileName "</tr>"

}

writeHtmlHeader $FileOutput
Add-Content $FileOutput "<table width='100%'><tbody>"
Add-Content $FileOutput "<tr bgcolor='#CCCCCC'>"
Add-Content $FileOutput "<td width='100%' align='center' colSpan=10><font face='tahoma' color='#003399' size='2'><strong> DB Details</strong></font></td>"
Add-Content $FileOutput "</tr>"

writeTableHeader $FileOutput

foreach($sqlserver in $sqlservers) 
{ 
 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "$sqlserver"
foreach($db in $serverInstance.Databases)
{
$db1=$db.ExecuteWithResults("DBCC SQLPERF(LOGSPACE)").Tables[0]
$dbSpaceAvailable = $db.SpaceAvailable/1KB
$collation=$db.Collation
$compatiblity=$db.CompatibilityLevel
$size=$db.Size
$db.RecoveryModel
if ($db.AutoShrink -eq "True")
{
$autoshrink="Yes"
}
else
{
$autoshrink="No"
}


if($db.RecoveryModel -eq 1)
{
 $RecoveryModel='Full'
}
elseif($db.RecoveryModel -eq 3)
{
 $RecoveryModel='Simple'
}
elseif($db.RecoveryModel -eq 2 )
{
 $RecoveryModel='Bulk Logged'
}
foreach($db2 in $db1)
 {
 
 if($db.name -eq $db2."database Name")
 {
 $Name=$db.name
 $DBSize= $size/1KB
 $DBSize = “{0:N3}” -f $DBSize
 $dbSpaceAvailable="{0:N3}" -f ($dbSpaceAvailable/1KB)
 $LogSizeMB = "{0:N3}" -f ($db2."Log Size (MB)")
 $SpaceUsedPercent = $db2."Log Space Used (%)"
 $logFreeSpaceMB="{0:N3}" -f ($db2."Log Size (MB)" -($db2."Log Size (MB)"*$db2."Log Space Used (%)")/100)
 $collation=$collation
 $recoveryModel=$recoveryModel
 $autoShrink=$autoShrink
 }
 
 
 
 
 }
 write-host $FileOutput, $sqlserver,$Name,$DBsize,$dbSpaceAvailable,$logSizeMB,$logFreeSpaceMB,$SpaceUsedPercent,$collation,$recoveryModel,$autoShrink
 writeInfo $FileOutput $serverInstance $Name $DBsize $dbSpaceAvailable $logSizeMB $logFreeSpaceMB $SpaceUsedPercent $collation $recoveryModel $autoShrink
}
}
 
Add-Content $FileOutput "</table>"

writeHtmlFooter $FileOutput 

$date = ( get-date ).ToString('yyyy/MM/dd')

$body = Get-Content $FileOutput
$body = New-Object System.Net.Mail.MailMessage $from,$to, "Job Status - $Date", $body
$body.isBodyhtml = $true
$smtp = new-object Net.Mail.SmtpClient($smtp_address)
$smtp.Send($body) 
 

Output:-

SQLDBSize

 

Posted in PowerShell, SQL | Tagged , | 4 Comments

MongoDB -Get Total datasize of all DB’s

var sum = 0; db.getMongo().getDBs()[“databases”].forEach(function(x) { sum += db.getMongo().getDB(x.name).stats().dataSize }); print(sum );

Total_DBSize

OR
db = db.getSiblingDB(“admin”);
dbs = db.runCommand({ “listDatabases”: 1 }).databases;
var sum1=0;dbs.forEach(function(database) { sum1+=db.getMongo().getDB(database.name).stats().dataSize }); print(sum1);

Total_DBSize-2

Posted in MongoDB | Tagged , , | Leave a comment

MongoDB – Drop database

The dropDatabase command drops the current database, deleting the associated data files.

Different methods to drop database

Method 1:

1. select the database which you want to delete
>use < database name >
2. Then issue the below command
>db.dropDatabase()

Method 2:

1. Define the name of the database
> var dbname=”users”
2. Issue the below command
> Mongo().getDB(dbname).dropDatabase()
{ “dropped” : “users”, “ok” : 1 }

DropDatabase-2

Dropping all the databases

*****Please make a copy before deleting anything****

The first command gives you a list of dbs ‘AA’ and ‘PP’ and the second one using cursor loop through all the database and drop the databases

>db.getMongo().getDBs()[“databases”].forEach(function(dbname){ var db1=db.getMongo().getDB(dbname.name); print (db1);});
AA
PP
> db.getMongo().getDBs()[“databases”].forEach(function(dbname){ db.getMongo().getDB(dbname.name).dropDatabase();});
> show dbs

DropDatabase-1

Posted in MongoDB | Tagged , , | Leave a comment

MongoDB -Get Size, RowCount, CollectionSize

There are two different ways of getting the desired result

The first method is iterating through each collection using for loop and displaying the result

cols = db.getCollectionNames();
for (index = 0; index < cols.length; index++) {
var coll = db.getCollection(cols[index]);
var stats = coll.stats();
print(stats.ns, stats.count, stats.size, stats.totalIndexSize); }

OR

The second method is iterating through each collection using cursor which in turns call function

var stats;db.getCollectionNames().forEach(function(x) { stats=db.getCollection(x).stats(); print(stats.ns, stats.count, stats.size, stats.totalIndexSize); });

getSizeCountCollection

 

Posted in MongoDB | Tagged , , | Leave a comment