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
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
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
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;
}

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
Thanks mate, but it still not returning not installed services.displaying as like previous. can you 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,

can you please send script in your way to find list of services installed on server or not.

Mnay 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
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?

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,

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
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
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

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
Many thanks rob,

Ill work out and let you know if I get any trouble.

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, 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,
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

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)

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