September 13, 2013 at 6:44 am
Hi,
i am looking to find powershell expert, so that can help on my query.below script does return sql installed services on server but it will not return anything if service is not installed. my requirement to return wheather service is running then 'running' else 'not installed or not running'. i need to display all status.can anyone please help me.
# Configuration data.
# Add your machine names to check for to the list:
[Array] $servers = "server name";
# Defining output format for each column.
$fmtName =@{label="Service Name" ;alignment="left" ;width=30 ;Expression={$_.Name};};
$fmtMode =@{label="Start Mode" ;alignment="left" ;width=10 ;Expression={$_.StartMode};};
$fmtState =@{label="State" ;alignment="left" ;width=10 ;Expression={$_.State};};
$fmtStatus =@{label="Status" ;alignment="left" ;width=10 ;Expression={$_.Status};};
$fmtMsg =@{label="Message" ;alignment="left" ;width=50 ; `
Expression={ if (($_.StartMode -eq "Auto") -and ($_.State -ne "Running") ) {"Alarm: Stopped"} };};
foreach($server in $servers)
{
$srvc = Get-WmiObject `
-query "SELECT *
FROM win32_service
WHERE name LIKE '%SQL%'
OR name LIKE '%MSOLAP%'
OR name LIKE '%ReportServer%'
OR name LIKE '%MSDtsServer%'" `
-computername $server `
| Sort-Object -property name;
Write-Output ("Server: {0}" -f $server);
Write-Output $srvc | Format-Table $fmtName, $fmtMode, $fmtState, $fmtStatus, $fmtMsg;
}
Thanks
September 13, 2013 at 9:15 am
I think I would do it in a different way but using your code I would add this in
if($Srvc.count -eq 0)
{
Write-Host "SQL Not Installed on $Server "
}
else
{
Write-Output ("Server: {0}" -f $server);
Write-Output $srvc | Format-Table $fmtName, $fmtMode, $fmtState, $fmtStatus, $fmtMsg;
}
https://blog.robsewell.com Its where I blog
SQL Community Slack Channel https://sqlps.io/slack
The Best PowerShell Module for the Modern SQL DBA https://dbatools.io
Data South West User Group http://sqlsouthwest.co.uk/[/url]
September 13, 2013 at 9:56 am
Thanks mate, but it still not returning not installed services.displaying as like previous. can you give some idea.
cheers
September 13, 2013 at 10:18 am
Hi rob,
can you please send script in your way to find list of services installed on server or not.
Mnay thanks
September 13, 2013 at 10:18 am
Hiya,
What this code should do is look at the results of
"SELECT *
FROM win32_service
WHERE name LIKE '%SQL%'
OR name LIKE '%MSOLAP%'
OR name LIKE '%ReportServer%'
OR name LIKE '%MSDtsServer%'" `
and if there are no services with those names print SQL not installed
if($Srvc.count -eq 0)
{
Write-Host "SQL Not Installed on $Server "
}
and if there are then it will display as you had it previously using this bit
else
{
Write-Output ("Server: {0}" -f $server);
Write-Output $srvc | Format-Table $fmtName, $fmtMode, $fmtState, $fmtStatus, $fmtMsg;
}
Is that not the result are you looking for?
https://blog.robsewell.com Its where I blog
SQL Community Slack Channel https://sqlps.io/slack
The Best PowerShell Module for the Modern SQL DBA https://dbatools.io
Data South West User Group http://sqlsouthwest.co.uk/[/url]
September 13, 2013 at 10:31 am
Hi rob,
you are right but if i run on any server just returning only status "running" and "stopped", but i canot see not installed on any server. particularly on 2000 server it should return SSIS,SSRS,SSAS are not installed but not showing anything. i need wheather service not installed then it should return "not installed.
looking forward to hear from you.
cheers
September 13, 2013 at 11:34 am
Ah now I understand
This should do the trick
$SSRS = Get-Service -ComputerName $server -name *report*
$SSIS = Get-Service -ComputerName $server -name *Integra*
If ($SSRS -eq $null)
{
Write-Host SSRS Not Installed on $server
}
else
{
$SSRS
}
If ($SSIS -eq $null)
{
Write-Host SSIS Not Installed on $server
}
else
{
$SSIS
}
you can work out the others
https://blog.robsewell.com Its where I blog
SQL Community Slack Channel https://sqlps.io/slack
The Best PowerShell Module for the Modern SQL DBA https://dbatools.io
Data South West User Group http://sqlsouthwest.co.uk/[/url]
September 13, 2013 at 1:14 pm
Many thanks rob,
Ill work out and let you know if I get any trouble.
Cheers
September 16, 2013 at 4:43 am
Hi Rob,
I am sorry to asking you again, still i am getting some errors , can you please send me the well executed script, that which i send to you and you send to me. i don't have that much grip on powershell. many thanks
cheers,
September 16, 2013 at 6:04 am
HI
As you don't explain the errors or the code you are running you are getting I can't really be of much use.............
You need to find the name of the service that you which to know about using Get-service and set them to a variable
ie
$SSRS = Get-Service -ComputerName $server -name *report*
then check if that $variable is $Null and if it is write Service Not Installed on $server and if it isn't give the details
ie
If ($SSRS -eq $null)
{
Write-Host "SSRS Not Installed on $server"
}
else
{
$SSRS
}
Then repeat that for each of the services you want to know about
Once you have that for each of the services you are interested in put the whole script in a loop and call each of your servers
ie
$servers = @("NAME1","NAME2")
or $servers = Get-Content "pathtoservers.txt"
foreach($server in $servers)
{
Put all the code you have written above in here
}
Then it will work. (Providing your account has permissions to do everything it needs to do)
https://blog.robsewell.com Its where I blog
SQL Community Slack Channel https://sqlps.io/slack
The Best PowerShell Module for the Modern SQL DBA https://dbatools.io
Data South West User Group http://sqlsouthwest.co.uk/[/url]
September 16, 2013 at 6:57 am
Thanks Rob,
working fine and if i need any thing i'll back to you.
Many Thanks
September 16, 2013 at 8:38 am
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
September 16, 2013 at 9:17 am
$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
https://blog.robsewell.com Its where I blog
SQL Community Slack Channel https://sqlps.io/slack
The Best PowerShell Module for the Modern SQL DBA https://dbatools.io
Data South West User Group http://sqlsouthwest.co.uk/[/url]
September 16, 2013 at 9:29 am
Hi Rob,
I did not get conclusion what you said for cluster nodes, can you please explain me howto do.??
Thanks
September 16, 2013 at 9:37 am
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
https://blog.robsewell.com Its where I blog
SQL Community Slack Channel https://sqlps.io/slack
The Best PowerShell Module for the Modern SQL DBA https://dbatools.io
Data South West User Group http://sqlsouthwest.co.uk/[/url]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply