Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Looking for powershell script


Looking for powershell script

Author
Message
mac_live
mac_live
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 219
Thanks Rob,

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

Many Thanks
mac_live
mac_live
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 219
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
mrrobsewell
mrrobsewell
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 657
$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
http://sqlps.io PASS PowerShell Virtual Chapter
http://sqlps.io/slack SQL Community Slack Channel
http://sqlps.io/vote Help Microsoft prioritise improvements to sqlserver PowerShell module
http://dbatools.io The Best PowerShell Module for the Modern SQL DBA
http://dbareports.io PowerShell, SQL, SSRS, PowerBi and Cortana to report on your estate
http://sqlps.io/video PASS PowerShell Virtual Chapter YouTube Channel - Subscribe to get notified of new videos



SQL South West UK User Grouphttp://sqlsouthwest.co.uk/
mac_live
mac_live
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 219
Hi Rob,

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

Thanks
mrrobsewell
mrrobsewell
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 657
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
http://sqlps.io PASS PowerShell Virtual Chapter
http://sqlps.io/slack SQL Community Slack Channel
http://sqlps.io/vote Help Microsoft prioritise improvements to sqlserver PowerShell module
http://dbatools.io The Best PowerShell Module for the Modern SQL DBA
http://dbareports.io PowerShell, SQL, SSRS, PowerBi and Cortana to report on your estate
http://sqlps.io/video PASS PowerShell Virtual Chapter YouTube Channel - Subscribe to get notified of new videos



SQL South West UK User Grouphttp://sqlsouthwest.co.uk/
mac_live
mac_live
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 219
Cheers Rob,

Ill do as you suggested.

Thanks once again.
mac_live
mac_live
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 219
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,
mac_live
mac_live
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 219
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,
mac_live
mac_live
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 219
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
mrrobsewell
mrrobsewell
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 657
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
http://sqlps.io PASS PowerShell Virtual Chapter
http://sqlps.io/slack SQL Community Slack Channel
http://sqlps.io/vote Help Microsoft prioritise improvements to sqlserver PowerShell module
http://dbatools.io The Best PowerShell Module for the Modern SQL DBA
http://dbareports.io PowerShell, SQL, SSRS, PowerBi and Cortana to report on your estate
http://sqlps.io/video PASS PowerShell Virtual Chapter YouTube Channel - Subscribe to get notified of new videos



SQL South West UK User Grouphttp://sqlsouthwest.co.uk/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search