Saturday, 21 March 2015

How to connect SQL database using PowerShell

Importing Machine details:  (In SCCM Database Perspective)

Let's suppose we have an SCCM SQL Server database named CM_VRR (Database of SCCM) and that database has a table named collection, which holds collection resources (System or user). If you are not an SCCM admin, Just imagine like we have an SQL server, database and a table which holds Computer Information.

Normally we will use query to get the data from a table. For that we need to login SQL server and have to type the query, after that we have to find some way for saving data as per you requirement.This will be big task to do manually especially if we have a huge tables and data inside the database. That's where powershell comes into the picture.

Here I would like to show you a scenario based example. I have SCCM database (CM_VRR) and a table which holds device collection details (Computer Information). Below is the over all graphical scenario 

SQL Server 2012 :
Server Name : ADDC-2012
Database : CM_VRR




















SCCM 2012 Console (Front End)

















SQL Query - Used to get the Computer Information (Device Collection Details)



















Now let's start this using powershell !!!

Note the below classes:

Reference : https://msdn.microsoft.com/en-us/library/System.Data.SqlClient


System.Data.SQLClient.SQLConnection : This Represents an open connection to a SQL Server database
System.Data.SqlClient.SqlCommand : Represent a Transact-SQL statement or stored procedure to execute against a SQL Server database
System.Data.SqlClient.SqlDataAdapter : Represents a set of data commands and a database connection that are used to fill the data-set and update a SQL Server database
System.Data.DataSet : Represents an in-memory cache of data.

1 - Opening SQL Connection using System.Data.SQLClient.SQLConnection
        You should have the proper permission to the database to access the data.

001
002
003
004
005
006
007
008
009
010
011
012
$Global:SCCMSQLSERVER = "ADDC-2012"
$Global:DBNAME = "CM_VRR"
Try
{
$SQLConnection = New-Object System.Data.SQLClient.SQLConnection
$SQLConnection.ConnectionString ="server=$SCCMSQLSERVER;database=$DBNAME;Integrated Security=True;"
$SQLConnection.Open()
}
catch
{
    [System.Windows.Forms.MessageBox]::Show("Failed to connect SQL Server:")
}

2 - Crating stored procedure and adding SQL query using System.Data.SqlClient.SqlCommand. In the below SQL Query, I have hard-coded the collection Name. If any portion of your sql query is dynamic, you just create a variable in the initial stage and include in your query.

001
002
003
004
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = "select QueryName,CollectionName from [dbo].[Collections_G] as G inner join [dbo].[Collection_Rules] as R
 on G.CollectionID = R.CollectionID where G.CollectionName = 'softwareinstallation'"

$SQLCommand.Connection = $SQLConnection

3 - Creating SQL Dataadapter and Dataset. Using System.Data.SqlClient.SqlDataAdapter and System.Data.DataSet


001
002
003
004
005
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SQLCommand
            
$SQLDataset = New-Object System.Data.DataSet
$SqlAdapter.fill($SQLDataset| out-null

4 - Now your query result is available in $SQLDataset 

001
$SQLDataset.tables[0]














As per the above result, we got two column with System Name and Collection Name based on our query. You may get many columns based on what query you are going to use. So we have to filter the data based on our required column.

Filtering the table data based on column. Below for-each loop will filter the first column ( $data[0] ) of our dataset.
Increase the array value from [0] to get the next column values $data[1


001
002
003
004
005
006
007
008
$tablevalue = @()

foreach ($data in $SQLDataset.tables[0])
{
$tablevalue = $data[0]
$tablevalue
}
$SQLConnection.close()

Putting all together : You have to modify the server details.


001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
$Global:SCCMSQLSERVER = "ADDC-2012"
$Global:DBNAME = "CM_VRR"
Try
{
$SQLConnection = New-Object System.Data.SQLClient.SQLConnection
$SQLConnection.ConnectionString ="server=$SCCMSQLSERVER;database=$DBNAME;Integrated Security=True;"
$SQLConnection.Open()
}
catch
{
    [System.Windows.Forms.MessageBox]::Show("Failed to connect SQL Server:")
}

$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = "select QueryName,CollectionName from [dbo].[Collections_G] as G inner join [dbo].[Collection_Rules] as R
 on G.CollectionID = R.CollectionID where G.CollectionName = 'softwareinstallation'"

$SQLCommand.Connection = $SQLConnection

$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SQLCommand        
$SQLDataset = New-Object System.Data.DataSet
$SqlAdapter.fill($SQLDataset| out-null

$tablevalue = @()
foreach ($data in $SQLDataset.tables[0])
{
$tablevalue = $data[0]
$tablevalue
}
$SQLConnection.close()


Download Script



Monday, 16 March 2015

Powershell output handling using CSV

How to export powershell output to CSV - Using hash table ?


           We have many ways to export a PowerShell output to CSV format, and the simple one is just using Expoer-CSV which is familiar to all... Below is the method exporting a powershell output to CSV format by using hash table. Here we are updating (Appending) hash table from a For-each loop and exporting the result to CSV format. At the end using Expoer-CSV to export the hash table values.Benefit is to make out result as per our customized way.

That's all I am trying here...

Scenario : Just pulling few details of a grop of machines

Technet - Using export-csv

001
Get-Process | export-csv C:\vivek\process.csv

Passing the result to an hash table and exporting to CSV

If we want to export a customized powershell output to CSV file, we can use a hash table to do that. Here I am giving a scenario based example for creating a csv output using hash table.

Scenario : Pinging a group of system and exporting the result to csv file.And also checking the Operating system name and RAM size of the system,

1. Creating an empty hash table to store the result and Getting Machine Name from a .txt file located on our desktop.
You can use your own way to input the system details.

001
002
003
004
$details = @()
$pc = Get-Content $env:USERPROFILE\desktop\pc.txt
foreach ($sys in $pc)
{

2. Ping checking (Offline) -  This script portion will start based on ping result.If the ping result is false, it just create an hash table with predefined content.And if the result is true, there are some wmi query to check the system details.

Forming hash table and adding the values to PSObject based on ping result
Using [ordered]@ table to keep the order as we given

001
002
003
004
005
006
007
008
009
010
if(!(Test-Connection -Cn $sys -BufferSize 16 -Count 1 -ea 0 -quiet))
{
$Result = [ordered]@{
MACHINE_NAME     = "$sys"
PING_STATUS      = "MACHINE OFFLINE"
OS_NAME = "N/A"      
TOTAL_PHYSICALMEMORY = "N/A"
                          }
    $Details += New-Object PSObject -Property $Result
}

We have to create a PSObject to store and append the hash table values. After the for-each loop, we will be exporting this psobject to comma separate value (csv) 

001
$Details += New-Object PSObject -Property $Result

3. Ping Checking (Online) -  As I mentioned previously, this is the true portion of ping result. If the ping result is true, we are checking the OS_NAME and TOTAL PHYSICALMEMORY of the system. 

001
002
003
004
005
006
$osname = "N/A"
$physicalmemory = "N/A"
$sysdetails = "N/A"
$sysdetails = Get-WmiObject -Class Win32_computersystem -ComputerName $sys | select -ExpandProperty TotalPhysicalMemory
$osname = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $sys | select -ExpandProperty caption
$physicalmemory = ($sysdetails)/1mb -as [int]

Forming hash table and adding the values to PSObject based on ping result

001
002
003
004
005
006
007
$Result = [ordered]@{  
MACHINE_NAME     = "$sys"
PING_STATUS      = "MACHINE ONLINE"
OS_NAME = "$OSNAME"
TOTAL_PHYSICALMEMORY = "$physicalmemory"
              }
$Details += New-Object PSObject -Property $Result

At the end exporting all the result to .CSV file. I just created a location path with a customized name. You can give anything which you like

001
002
003
$date = Get-Date -UFormat "%m-%d-%y"
$pathofcsv = "$env:userprofile\desktop\" + "Ping_Result_" + "$date" + ".csv"
$Details | export-csv -Path $pathofcsv -NoTypeInformation

Output will looks like below  :)












Now putting all the script together :

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
#Machine availability check
#Handling Output using CSV

$details = @()
$pc = Get-Content $env:USERPROFILE\desktop\pc.txt
foreach ($sys in $pc)
{
if(!(Test-Connection -Cn $sys -BufferSize 16 -Count 1 -ea 0 -quiet))
{
    $Result = [ordered]@{
    MACHINE_NAME     = "$sys"
    PING_STATUS      = "MACHINE OFFLINE"
    OS_NAME = "N/A"      
    TOTAL_PHYSICALMEMORY = "N/A"
                          }
    $Details += New-Object PSObject -Property $Result
}
else
{
$osname = $N
$physicalmemory = "N/A"
$sysdetails = ""
$sysdetails = Get-WmiObject -Class Win32_computersystem -ComputerName $sys | select -ExpandProperty TotalPhysicalMemory
$osname = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $sys | select -ExpandProperty caption
$physicalmemory = ($sysdetails)/1mb -as [int]

    $Result = [ordered]@{  
    MACHINE_NAME     = "$sys"
    PING_STATUS      = "MACHINE ONLINE"
    OS_NAME = "$OSNAME"
    TOTAL_PHYSICALMEMORY = "$physicalmemory"
              }
    $Details += New-Object PSObject -Property $Result
}
}
$date = Get-Date -UFormat "%m-%d-%y"
$pathofcsv = "$env:userprofile\desktop\" + "Ping_Result_" + "$date" + ".csv"
$Details | export-csv -Path $pathofcsv -NoTypeInformation


Download Script

Thursday, 5 March 2015

Powershell output handling using HTML

HTML Report in PowerShell -  Using HTML Tables


Here I am going to cover how to handle PowerShell output using html table.

Benefits of html output : We will be able to do the different types of formatting using html like table creation, coloring,font style etc, but we need to know some basic html language to do a better formatting. Below url help you to learn the basics of html.So if you are a beginner in html language, please go through the below url and understand the basics of html.


Scenario : Fetching System Properties :

Through this example, I am using get-wmiobject to get some system details and passing the output to html tables.Here we have to decide the output design before creating script... Like output heading , table's rows,columns, heading, color, graphs design and location etc: 

Please note the below html tags for basic understandings:

001
002
003
004
005
006
<Table>     : Table Craetion
<tr>        : Table row
<td>        : Table Data
<h1>        : Heading size
<bgcolor>   : Color of the table. You can change as per your wish
<align>     : Alignment of table and table data

 1 - Creating heading for table and output:

001
002
003
004
005
006
007
008
009
$Table =
{
'<table width=40% align=center>'
'<th align="center"> <h3><font color=red>SYSTEM PROPERTIES<h3></th>'
'</table>'
'<table border="1" width=40% align=center>'
'<tr bgcolor=skyblue>'
'<th>HOST NAME</th><th>OPERATING SYSTEM NAME</th><th>SERIAL NO</th>'
}

This will be the head part of your output. Table width,height and alignment can be customize as per your requirement. In this example, the output heading will be SYSTEM PROPERTIES and I am fetching the below three details of a computer
HOST NAME
OPERATING SYSTEM NAME
SERIAL NO

The format will looks like below:








2 - Process Creation : Using get-wmiobject to retrive the system details and passing the result through html.


001
$Sysdetails = Get-WmiObject -Class win32_operatingsystem -ComputerName $_

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
$process=
{
    Get-Content $env:USerProfile\desktop\pc.txt | foreach {
    '<tr>'
    '<td align="center" >{0}</td>' -f $_
    if(!(Test-Connection -Cn $_ -BufferSize 16 -Count 1 -ea 0 -quiet))
    {
        $failed = "Failed to Ping"
        '<td bgcolor = orange align = center>{0}</td><td bgcolor = orange align = center>{1}</td>' -f $failed,$failed
    }
    else
    {
        $Error.Clear()
        $Sysdetails = Get-WmiObject -Class win32_operatingsystem -ComputerName $_
        if($Error[0])
            {
            $unknown = "Unknown"
            '<td bgcolor = yellow align = center>{0}</td> <td bgcolor = yellow align = center>{1}</td>' -f $unknown,$unknown
            }
        else
            {
            '<td bgcolor=lightgreen align=center>{0}</td> <td bgcolor=lightgreen align=center>{1}</td>' -f $Sysdetails.Caption,$Sysdetails.SerialNumber
            }
        }'<tr>'
       $Sysdetails = $null
       }
       # Foreach End

}
#Process End
$Tableend 

3 - Report Generation Part : Path Creation, report generation and opening the html file


001
002
003
004
$Path = "$env:USerProfile\desktop\SysDetails.html"
ForEach-Object -Begin $table -Process $process -End $tableend |
Set-Content -Path $Path -Encoding ascii
Invoke-Expression $Path

Finally the Output of the script will appears as below: