create event in powershell

Try
{
#Define the event log and your custom event source
$evtlog = “Application”
$source = “MyEventSource”
$evtID = 911
$myMessage = “Test hari powershell”

#Load the event source to the log if not already loaded. This will fail if the event source is already assigned to a different log.
if ([System.Diagnostics.EventLog]::SourceExists($source) -eq $false)
{
[System.Diagnostics.EventLog]::CreateEventSource($source, $evtlog)
}

$evtID = New-Object System.Diagnostics.EventInstance($evtID,1);
$evtObject = New-Object System.Diagnostics.EventLog;
$evtObject.Log = $evtlog;
$evtObject.Source = $source;
$evtObject.WriteEvent($evtID, $myMessage)
}

Catch
{
$ErrorMessage = $_.Exception.Message
$FailedItem = $_.Exception.ItemName
Write-Host $ErrorMessage
#Send-MailMessage -From hari@Mygeek.Com -To geekhari@MyCompany.Com -Subject “Geek Hari is caught in exception!” -SmtpServer EXCH01.AD.here.Com -Body “We failed to read file $FailedItem. The error message was $ErrorMessage”
#Break
}

DBA Audits with powershell

## open database connection
## Get Input from DBA, Reason for Login
<#
HealthCheck = 1
Maintenace = 2
Change Management = 3

#>
try
{

#Get Current user
$Currentuser = [Security.Principal.WindowsIdentity]::GetCurrent().Name
Write-Host $Currentuser

$DbaInput = “”
$Reason =””
$DbaInput = Read-Host -Prompt ‘Input your Reason for Login HealthCheck=1 Maintenace = 2 server Change Management = 3’
Write-Host “You input ‘$DbaInput’ ”

If($DbaInput -eq “1”) {$Reason = “HC”}
ElseIf($DbaInput -eq “2”){$Reason = “MT”}
ElseIf($DbaInput -eq “3”){$Reason = “CM”}
Else {“No Reason Found!”}
Write-Host “You input ‘$Reason’ ”

$conn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=HARIERE-PC\SQLSERVER2014; Initial Catalog=BackupTest; Integrated Security=SSPI”)
$conn.Open()

$cmd = $conn.CreateCommand()
$cmd.CommandText =”INSERT INTO [dbo].[Reason_Login]
([Reason]
,[Name])
VALUES ( ‘ “+$Reason + ” ‘ “+”,'”+$Currentuser +” ‘)”
$cmd.ExecuteNonQuery()
$conn.Close()
}
catch
{
$ErrorMessage = $_.Exception.Message
$FailedItem = $_.Exception.ItemName
Send-MailMessage -From hari@geek.Com -To hari@Geek.Com -Subject “DBA Loginreason Failed” -SmtpServer hari.geek.Com -Body ” failed $FailedItem. The error message was $ErrorMessage”
Break
}

Powershell with Windows Task Scheduler

In this article i have provided few commands how to work with task scheduler with power-shell. for detailed information on Scheduled Tasks Cmdlets, please visit.

https://technet.microsoft.com/en-us/library/jj649816(v=wps.630).aspx

 

 

PS C:\Users\Administrator> Disable-ScheduledTask -TaskName “HariTask”

TaskPath TaskName State
——– ——– —–
\ HariTask Disabled
PS C:\Users\Administrator> Enable-ScheduledTask -TaskName “HariTask”

TaskPath TaskName State
——– ——– —–
\ HariTask Ready
PS C:\Users\Administrator> Get-ScheduledTask -TaskName “HariTask”

TaskPath TaskName State
——– ——– —–
\ HariTask Ready
PS C:\Users\Administrator> Start-ScheduledTask -TaskName “HariTask”
PS C:\Users\Administrator> Stop-ScheduledTask -TaskName “HariTask”
PS C:\Users\Administrator>

Check Powersshell Version and Change Versions

To check powershell edition, Type $PSVersionTable at prompt.

I ran the command my windows 2012 VM, Please see find the results bellow

Windows PowerShell
Copyright (C) 2013 Microsoft Corporation. All rights reserved.

PS C:\Users\Administrator> $PSVersionTable

Name Value
—- —–
PSVersion 4.0
WSManStackVersion 3.0
SerializationVersion 1.1.0.1
CLRVersion 4.0.30319.34014
BuildVersion 6.3.9600.16394
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0}
PSRemotingProtocolVersion 2.2

 

How to Switch to Power shell versions?

Type powerhsell -version <n> where n is version number (2.0,3.0,4.0). in this example i am switching my version from 4.0 to 2.0.

 

PS C:\Users\Administrator> powershell -version 2.0
Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\Users\Administrator> $PSVersionTable

Name Value
—- —–
CLRVersion 2.0.50727.8000
BuildVersion 6.1.7600.16385
PSVersion 2.0
WSManStackVersion 2.0
PSCompatibleVersions {1.0, 2.0}
SerializationVersion 1.1.0.1
PSRemotingProtocolVersion 2.1

 

 

 

 

Find Sql Servers Service pack information across your Enterprise using power shell in one click

Imagine if your shop has more than 100+ SQL servers and you want to create a report to show the patching level of each SQL Server, How did you do that in fastest way.

Login into each server and run query will consume time and leads some mistakes.

 

Power-shell make easy for you.

  • Create a text file .
  • Add SQL Server instance names to the text file and save.
  • Change the path in the following script  C:\hari1\mysqlservers.txt to your text file name and execute the power Shell script.
    Output will be saved in CSV and you can share this report to your management team.
    output
"SqlServerName","Version","SP"
"HARIERE-PC\SQLSERVER2014","12.0.2254.0","RTM"
"HARIERE-PC\SQLSRV2012","11.0.2218.0","RTM"
# Author Hari Ere
# SqlServerversionspcheck.ps1
#create a text file and add sqlserver instanace names in the file
# usage: ./SqlServerversionspcheck.ps1
# Check Sqlserver versions and Service packs across the network
#define data table as public
 $dt = new-object System.Data.DataTable 
foreach ($svr in get-content "C:\hari1\mysqlservers.txt")
{
 #Establish trusted Sql Connection
 $con = "server=$svr;database=master;Integrated Security=sspi"
 #query to get sqlservername,version and sp info
 $cmd = "SELECT @@servername as SqlServerName,SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('ProductLevel') as SP"
 #build data adapter
 $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
 #fill the data set
 $da.fill($dt) | out-null
 
 #for debug purpose uncomment the follwoing two statements.
 ##$svr
 ##$dt | Format-Table -autosize
}
$dt | export-csv "C:\hari1\mysqlserveroutput.csv" -noType

Power Shell Basic Commands 1

#Get-command - Retrieves a list of PowerShell command lets
Get-command

#search Command lets with verb and noun
Get-Command -verb "get"
Get-Command -noun "service"

#Find syntax of cmdlets
Get-Help Get-Command
#to clear window

#to find parameters
Get-Command -?
Get-childItem -?

#To list the directory
Get-ChildItem

#find dos alias in PowerShell
Get-Alias

#set location path
set-Location "c:\"

#pipelining command lets by adding in another cmdlet to another
#in the following example, I am asking to list the files in C directory which is >1000kb
Get-ChildItem |where-object{$_.Length -gt 1000kb}
#list files with lenght less than 100 kb
Get-ChildItem |Where-object{$_.Length -lt 100kb}
#List files where letter starts H
Get-ChildItem  |Where-object{$_.Name -eq "hari"}
#sort files by length
Get-ChildItem |where-object{$_.Length -gt 100kb} | Sort-Object Length|Format-Table -property Name,path,Length -AutoSize
#use select object to get properties of an object
Get-ChildItem|Select-object Name,Length,path