SQL services and instance name

PowerShell Help
1

I have following script:

$namespaces = @(gwmi -ns ‘root\Microsoft\SqlServer’ __NAMESPACE | ? {$_.name -match ‘ComputerManagement’ } | select name).Name

 

$namespaces | ForEach {
$namespaces = $_
$instance = Get-WmiObject -Namespace ("root\Microsoft\SqlServer" + $namespaces) -Class ServerSettings

ForEach-Object { Get-WmiObject -Namespace ("root\Microsoft\SqlServer" + $namespaces) -Class SqlService | Select-Object Hostname, DisplayName, ServiceName, StartName

}

} | Format-Table -Property Hostname, DisplayName, ServiceName, StartName, @{name=“InstanceName”; exp={$instance.InstanceName}} -AutoSize | Out-String -Width 4096 | Out-File -FilePath “\servername\share\filename.txt” -Append

 

After running on a single server, the result is correct (table format):

Hostname DisplayName ServiceName StartName InstanceName

When I distribute a script using SCCM “Software Library -> Scripts” for a collection with 16 clients, I only get a result for one of the clients (randomly selected) or two clients.
I need to create a script so that the result obtained includes all clients in a given collection and is saved to the csv file.

Please help. -Append switch doesn’t help

2

Format-Table is used for formatting in the console. Replace that with Select-Object and remove the autosize switch. This is returning an object, so you should use an Export option, most likely Export-Csv to save the results.

The next issues are permissions first. SCCM is running as SYSTEM and you are trying to write to a network share. You would need to have the computer accounts in the collection added to the share\ntfs permissions for the SYSTEM account to access that share. After that, you trying to write to the same file with 16 computers, which is going to have errors because the file can only be opened by one computer for edit at a time. You should be creating unique files (e.g. Servername.csv) for each computer and then joining with another process\script.

3

Correct.

When Im using the $env:COMPUTERNAME in the path then works correctly \servername\share\ $env:COMPUTERNAME.txt

4

If I use $env: COMPUTERNAME then many files are created and that’s fine. If I use a common name (eg. report.csv) then the file contents are overwritten.

Permissions to the resource/share are granted. I think this is a bug in the script.

5

Have you tried…

| Select-Object -Property Hostname, DisplayName, ServiceName, StartName, @{name=”InstanceName”; exp={$instance.InstanceName}} | 
    Export-Csv -Path "\\servername\share\$env:COMPUTERNAME.txt"
6
Select-Object -Property Hostname, DisplayName, ServiceName, StartName, @{name=”InstanceName”; exp={$instance.InstanceName}} |
Export-Csv -Path "\\servername\share\$env:COMPUTERNAME.csv"
Creates several files with valid results (the server name in filename).
=======================
Select-Object -Property Hostname, DisplayName, ServiceName, StartName, @{name=”InstanceName”; exp={$instance.InstanceName}} |
Export-Csv -Path "\\servername\share\reports.csv"
Creates one result in file for only one server (not for example 9 servers in collection).
7

Ok. Is there a question? My assumption is that you want them in a single file? Something like this:

Get-ChildItem C:\MyFiles\*.csv | 
    foreach{Import-Csv -Path $_.FullName} | 
        Export-Csv -Path C:\MyFiles\AllResults.csv -NoTypeInformation
8

Works well. The curiosity is why it doesn’t work when saving to one file.

9

The answer to that is the Windows file system. When a file is opened for editing, a file is locked. Even if you are “appending” to a file, you are still going to run into multiple systems fighting to write to a single file. If you create unique files for each, then one system is writing to one file, so there is no conflict.

Databases are designed to have multiple connections creating rows, but even a database can lock a record if multiple systems are trying to update the same row. Writing to the databases just collect data is typically a pain and requires extra code. It’s been a bit, but if this something you want to inventory you can add it to the MOF file and SCCM will add it to the database: