Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Looking for powershell script Expand / Collapse
Author
Message
Posted Monday, September 16, 2013 6:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 47, Visits: 175
Thanks Rob,

working fine and if i need any thing i'll back to you.

Many Thanks
Post #1495054
Posted Monday, September 16, 2013 8:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 47, Visits: 175
Hi Rob,

sorry to disturb you again,
$SQL = Get-Service -ComputerName $server -name *sql* if i run this not returning anything, can you suggest me what i would need to change.

and i got another issue that we have many clusters and we don't know which cluster is running and what nodes that cluster, also want to find services. is there any chance to find with powershell??.

if you can help me will be great.

cheers
Post #1495123
Posted Monday, September 16, 2013 9:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:52 AM
Points: 16, Visits: 443
$SQL = Get-Service -ComputerName $server -name *sql* if i run this not returning anything, can you suggest me what i would need to change.


That is setting the services whose name contains SQL to a variable

You would then call $SQL and it would show

Status Name DisplayName
------ ---- -----------
Running msftesql SQL Server FullText Search (MSSQLSE...
Running MSSQLSERVER SQL Server (MSSQLSERVER)
Stopped MSSQLServerADHe... SQL Server Active Directory Helper
Stopped SQLBrowser SQL Server Browser
Running SQLSERVERAGENT SQL Server Agent (MSSQLSERVER)
Running SQLWriter SQL Server VSS Writer

(on a 2005 box)

If you just wanted the SQL Server Database Engine Service then run

Get-Service -ComputerName $Server -name "MSSQLSERVER"

The better bet for you right now would be to run

Get-Service -ComputerName $Server

on the server and look at the list of services. You can then work out the correct name to choose for your query.

Your other question probably should be asked in a separate thread but if you put the cluster nodes into the sqlserverstxt file and run your script from this thread it will show you which cluster is running SQL and which services


http://sqldbawithabeard.com Its where I blog

SQL South West UK User Grouphttp://sqlsouthwest.co.uk/
Post #1495147
Posted Monday, September 16, 2013 9:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 47, Visits: 175
Hi Rob,

I did not get conclusion what you said for cluster nodes, can you please explain me howto do.??

Thanks
Post #1495153
Posted Monday, September 16, 2013 9:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:52 AM
Points: 16, Visits: 443
It's probably best for a new question, So create a new thread

If

$Server = CLusterNode

that will show you if SQL is running on that node but there will be a better way to fully answer your question


http://sqldbawithabeard.com Its where I blog

SQL South West UK User Grouphttp://sqlsouthwest.co.uk/
Post #1495157
Posted Monday, September 16, 2013 10:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 47, Visits: 175
Cheers Rob,

Ill do as you suggested.

Thanks once again.
Post #1495187
Posted Tuesday, September 17, 2013 4:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 47, Visits: 175
Hi Rob,

I created another post in adminstration name (finding cluster nodes), i tried $server = ClusterNodes, but it is not working. can give some idea.

Cheers,
Post #1495427
Posted Tuesday, September 17, 2013 4:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 47, Visits: 175
Hi Rob,

I created another post in adminstration name (finding cluster nodes), i tried $server = ClusterNodes, but it is not working. can give some idea.

Cheers,
Post #1495428
Posted Thursday, September 19, 2013 8:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 47, Visits: 175
Hi Rob,

I am sorry to asking you again,
I need your help now, below code executing fine now on any server, but the issue is i need to store those values in sql table for monitoring. we have inhouse monitoring tool, so we are updating these changes to monitoring tool, but the issue is need to save these executed powershell values to sql table. i need to insert into table and update everytime. i need to run this on main server to get rest of servers information. it is kind of loop, every time audit updates it needs to update. can you please arrange my code into that way, because i have no idea. i'll be very greatfull if you can help.

param(
[string]$servers = $( Read-Host "Input server, please" )
)
foreach($server in $servers)
{
$SSRS = Get-Service -ComputerName $server -name *report*
$SSIS = Get-Service -ComputerName $server -name *MSDts*
$SSAS = Get-Service -ComputerName $server -name *Olap*
$cluster = Get-Service -ComputerName $server -name *ClusSvc*
$SQLbrowser = Get-Service -ComputerName $server -name *sqlbrowser*
$SQLagent = Get-Service -ComputerName $server -name *serveragent*


If ($SSRS -eq $null)
{
Write-Host "SSRS Service Not Running on $server"

}
else
{
$SSRS
}
If ($SSIS -eq $null)
{
Write-Host "SSIS Service Not Running on $server"
}
else
{
$SSIS
}
If ($SSAS -eq $null)
{
Write-Host "SSAS Service Not Running on $server"

}
else
{
$SSAS
}

If ($SQLbrowser -eq $null)
{
Write-Host "SQLbrowser Service Not Running on $server"
}
else
{
$SQLbrowser
}
If ($SQLagent -eq $null)
{
Write-Host "SQLagent Service Stopped on $server"
}
else
{
$SQLagent
}
If ($cluster -eq $null)
{
Write-Host "cluster Service Not running on $server"
}
else
{
$cluster
}

# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null;
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $server;
# Get server properties
$properties = $srv.Properties
$owner_node = $properties.Item("ComputerNamePhysicalNetBIOS").Value;
$is_clustered = $properties.Item("IsClustered").Value
if($is_clustered)
{
Write-Host "The current active node of $server is $owner_node.";
}
else
{
Write-Host "$server is not a clustered instance of SQL Server.";
}
}

Many Thanks
Post #1496422
Posted Sunday, September 22, 2013 1:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:52 AM
Points: 16, Visits: 443
Hi

You are probably best of using the Out-Datatable and Write-DataTable functions to do this.

Google will give you plenty of examples


http://sqldbawithabeard.com Its where I blog

SQL South West UK User Grouphttp://sqlsouthwest.co.uk/
Post #1497191
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse