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 Friday, September 13, 2013 6:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:13 AM
Points: 47, Visits: 214
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
Post #1494563
Posted Friday, September 13, 2013 9:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 10:49 AM
Points: 18, Visits: 477
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

SQL South West UK User Grouphttp://sqlsouthwest.co.uk/
Post #1494617
Posted Friday, September 13, 2013 9:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:13 AM
Points: 47, Visits: 214
Thanks mate, but it still not returning not installed services.displaying as like previous. can you give some idea.

cheers
Post #1494639
Posted Friday, September 13, 2013 10:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:13 AM
Points: 47, Visits: 214
Hi rob,

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

Mnay thanks
Post #1494645
Posted Friday, September 13, 2013 10:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 10:49 AM
Points: 18, Visits: 477
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

SQL South West UK User Grouphttp://sqlsouthwest.co.uk/
Post #1494646
Posted Friday, September 13, 2013 10:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:13 AM
Points: 47, Visits: 214
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
Post #1494661
Posted Friday, September 13, 2013 11:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 10:49 AM
Points: 18, Visits: 477
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

SQL South West UK User Grouphttp://sqlsouthwest.co.uk/
Post #1494684
Posted Friday, September 13, 2013 1:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:13 AM
Points: 47, Visits: 214
Many thanks rob,

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

Cheers
Post #1494704
Posted Monday, September 16, 2013 4:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:13 AM
Points: 47, Visits: 214
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,
Post #1494991
Posted Monday, September 16, 2013 6:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 10:49 AM
Points: 18, Visits: 477
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

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

Add to briefcase 12»»

Permissions Expand / Collapse