No XML file produced after running Get-SqlServerInventoryToClixml.ps1

Oct 21, 2013 at 8:30 PM
Get-WindowsInventoryToClixml.ps1 will write an XML file but Get-SqlServerInventoryToClixml.ps1 fails to produce one. The log file is written in -DirectoryPath that I choose but no XML file. Any suggestions?
Oct 21, 2013 at 9:01 PM
Actually I just did another scan against two systems and the XML file was produced.
Coordinator
Oct 21, 2013 at 9:01 PM
Hi Bill,
Does the log file from Get-SqlServerInventoryToClixml.ps1 indicate that any SQL Server services were found and successfully scanned?

--Kendal
Oct 21, 2013 at 9:22 PM
The log file found the SQL services originally. The XML files are being created now. The current issue now is converting to Excel. I run this command against the .gz file --> .\Convert-WindowsInventoryClixmlToExcel.ps1 -FromPath "C:\Users\ad-johnstw1\Documents\WindowsPowerShell\logs\SQL Server Inventory - 2013-10-21-14-16.xml.gz" -ColorTheme Metro -ColorScheme Dark

This is the error I'm getting now:

Import-Clixml : Data at the root level is invalid. Line 1, position 1.
At C:\Users\ad-johnstw1\Documents\WindowsPowerShell\Convert-WindowsInventoryClixmlToExcel.ps1:170 char:1
  • Import-Clixml -Path $FromPath | ForEach-Object {
  • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : NotSpecified: (:) [Import-Clixml], XmlException
    • FullyQualifiedErrorId : System.Xml.XmlException,Microsoft.PowerShell.Commands.ImportClixmlCommand
Oct 21, 2013 at 9:22 PM
oh and thanks for the quick response. This looks like an awesome tool when I get it working properly in our environment (600 servers)
Coordinator
Oct 21, 2013 at 9:26 PM
Hi Bill,
That error looks like the .GZ file may not have been completely written. Did the log file show "End Script:" as the very last line? This was just for 2 servers, correct?

--Kendal
Oct 21, 2013 at 9:33 PM
I am currently just leveraging it against one server because the second test server wouldn't work for some reason even though it does have MSSQLSERVER service running. Yes, End Script is the last line. Here is the end of log:

2013-10-21 14:30:23.8530 ? Scanned PAOMDB at IP address 10.11.86.205 with 0 errors
2013-10-21 14:30:24.1338 ? Machine scan complete (Success: 1; Failure: 0)
2013-10-21 14:30:24.1650 ? Writing Inventory to disk
2013-10-21 14:30:25.1322 ? End Script: C:\Users\ad-johnstw1\Documents\WindowsPowerShell\Get-SqlServerInventoryToClixml.ps1
Coordinator
Oct 21, 2013 at 9:37 PM
Hmm, if you hit the "End Script" line then the GZ file should have been completely written. Any chance the GZ file is blocked? (Right Click -> Properties, on the general tab you'd see an "Unblock" button if the file is blocked)

How big is the GZ file?

--Kendal
Oct 21, 2013 at 9:40 PM
There is no option for Block/Unblock like the modules. The file is 187KB. I can see the XML when I open the archive then the XML file. Again thanks for the rapid response.
Coordinator
Oct 21, 2013 at 9:55 PM
What OS are you running where you're trying to convert the GZ file to Excel Workbooks?

--Kendal
Oct 21, 2013 at 9:58 PM
Win7 Pro x64 with Office 2007. The Convert-WindowsInventoryClixmlToExcel.ps1 works flawlessly.
Oct 21, 2013 at 10:02 PM
Sorry Kendal, was using the wrong command. Everything appears to be working now. Thanks for your assistance!!!
Oct 21, 2013 at 10:05 PM
Edited Oct 21, 2013 at 11:54 PM
.
Oct 21, 2013 at 10:05 PM
Edited Oct 22, 2013 at 4:27 PM
Kendal, Ignore my last. I got it working. Is there any way to not have the Windows inventory scan when doing the SQL inventory? Thanks. -Bill
Oct 22, 2013 at 4:30 PM

Kendal,

Is there any way to exclude the Windows scanning portion of Get-SqlServerInventoryToClixml.ps1? I am scanning a /24 subnet and the SQL inventory completes but when the Windows inventory initiates it crashes. Thanks for all of your help.

-Bill

Coordinator
Oct 22, 2013 at 7:00 PM
Bill,
Glad that you got the syntax figured out. In troubleshooting I found another problem introduced with PowerShell 4.0 so it's a win either way. :-)

I should be up front in saying that I've never run SQL Power Doc against hundreds of instances, though I have run the Windows inventory piece alone against several hundred machines and it worked fine. If you don't mind being patient, I'd suggest scaling up the number of instances you scan until you find a breaking point...and then I can help figure out how to get around that if you do.

As for disabling the Windows Inventory scan, did you say that you could run a Windows Inventory only against the same machines and it worked? When it runs as part of the SQL Server inventory what specifically happens when it crashes? Is there an error written to the log?

There's nothing in SQL Power Doc to skip over performing a Windows Inventory right now, but if you want to manually circumvent it then delete or comment out lines 1382-1427 of SqlServerInventory.psm1.

--Kendal
Oct 22, 2013 at 8:20 PM
Thanks Kendal. I commented out the Windows Inventory code in SqlServerInventory.psm1 for now. Will test the SQL scan for now as this is my current priority. The log doesn't really say anything about the crash. The Windows inventory locks up about 51 servers into the scan (out of 170 servers on the subnet). I'll post more when I find out what might be happening.
Coordinator
Oct 22, 2013 at 8:25 PM
The appearance of locking up could be due to a hung WMI call against one of the machines that's being scanned - I've seen it happen before. To figure out which machines are currently being scanned I've used Resource Monitor to watch all the network connections in & out and, in parallel with looking at the log entries, I can see which machine things are hanging on.

When I've run into this in the past it's been something along the lines of a CPU pegged at 100% that made the WMI calls slow to a snail's pace.

--Kendal
Oct 22, 2013 at 11:42 PM
That appears to be the cause for some of the servers that were being scanned. It took about two hours to scan one /24 subnet for SQL. Windows appeared to be hanging but I just left it and the XML file finally appeared. It looked like most of the time was taken up writing the XML file. I had the Resource Monitor up the whole time and CPU was maxed with Memory swapping to disk after being maxed out. The XML file ended up being 53MB! I'm converting to Excel now. I love the "Go get a coffee, this can take a few minutes..." message you log to the console! In this case it will probably take an hour. :) I can't wait to see the first successful results with almost 200 servers in the Excel file. I'll keep you posted as this will be an on going project. Thanks for all of your help.
Coordinator
Oct 23, 2013 at 1:56 PM
Admittedly, PowerShell can be a bit heavy (especially on memory). I've noticed that writing the XML takes a while, too (due to the speed of serializing the PowerShell objects representing the servers to the GZ XML). I'm looking into ways to make that part faster...but for now I can add a message to the status window that indicates the XML is being written to disk and to go get (another) cup of coffee. :-)

--Kendal
Oct 23, 2013 at 3:48 PM
It created the XML file but converting to Excel gives an empty file. The XML file is probably too big. I'm breaking the servers up into smaller pieces by domains. We have 10 domains so this shouldn't be too bad. Is there a way to scan by Active Directory OU's? One of the domains we have has about 20 OU's and some of them we don't have permission to access and scan.

I just scanned 14 computers and it only took about 5 minutes. When I convert the GZ file to Excel the file shows up with the SQL inventory but some of the pieces are missing as the script throws errors. Is this because I commented out comment out lines 1382-1427 of SqlServerInventory.psm1? Again thanks for responding to fast. Here are the errors:

PS C:\Users\ad-johnstw1\Documents\WindowsPowerShell> C:\Users\ad-johnstw1\Documents\WindowsPowerShell\ConvertToExcel.ps1
Remove-Variable : Cannot find a variable with name 'HasDatabaseFile'.
At C:\Users\ad-johnstw1\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:4165 char:3
  • Remove-Variable -Name NullDatabaseName, HasDatabaseFile, CanGrowLargerThanDriv ...
  • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : ObjectNotFound: (HasDatabaseFile:String) [Remove-Variable], ItemNotFoundException
    • FullyQualifiedErrorId : VariableNotFound,Microsoft.PowerShell.Commands.RemoveVariableCommand
Remove-Variable : Cannot find a variable with name 'CanGrowLargerThanDriveFreeSpace'.
At C:\Users\ad-johnstw1\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:4165 char:3
  • Remove-Variable -Name NullDatabaseName, HasDatabaseFile, CanGrowLargerThanDriv ...
  • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : ObjectNotFound: (CanGrowLargerThanDriveFreeSpace:String) [Remove-Variable], ItemNotFoundException
    • FullyQualifiedErrorId : VariableNotFound,Microsoft.PowerShell.Commands.RemoveVariableCommand
Remove-Variable : Cannot find a variable with name 'AllFileGrowthPotentialKB'.
At C:\Users\ad-johnstw1\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:4165 char:3
  • Remove-Variable -Name NullDatabaseName, HasDatabaseFile, CanGrowLargerThanDriv ...
  • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : ObjectNotFound: (AllFileGrowthPotentialKB:String) [Remove-Variable], ItemNotFoundException
    • FullyQualifiedErrorId : VariableNotFound,Microsoft.PowerShell.Commands.RemoveVariableCommand
Remove-Variable : Cannot find a variable with name 'AssessmentPriority'.
At C:\Users\ad-johnstw1\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:4165 char:3
  • Remove-Variable -Name NullDatabaseName, HasDatabaseFile, CanGrowLargerThanDriv ...
  • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : ObjectNotFound: (AssessmentPriority:String) [Remove-Variable], ItemNotFoundException
    • FullyQualifiedErrorId : VariableNotFound,Microsoft.PowerShell.Commands.RemoveVariableCommand
Export-WindowsInventoryToExcel : Cannot bind argument to parameter 'WindowsInventory' because it is null.
At C:\Users\ad-johnstw1\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:4442 char:52
  • Export-WindowsInventoryToExcel -WindowsInventory $SqlServerInventory.WindowsIn ...
  • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : InvalidData: (:) [Export-WindowsInventoryToExcel], ParameterBindingValidationException
    • FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Export-WindowsInventoryToExcel
Oct 27, 2013 at 7:56 PM

Status report. Started the script on Thursday morning against 500 servers. It found 241 SQL instances. On Sunday afternoon it’s still running. I’m watching Resource Monitor. 94-100% Used Physical Memory. Commit=12GB, Working Set=5GB, Private=5GB. This is going to be interesting to see how long it will take to finish. It will be worth it though when the Excel files appear.

Coordinator
Oct 29, 2013 at 12:59 PM
Hi Bill,
Did the script ever finish? Curious how long it took...

Kendal
Oct 29, 2013 at 4:50 PM
yes, it finished yesterday at around 12PM. I started the script at 10AM on 10/24 and it finished at 12PM on 10/28. The convert to Excel crashed halfway through but did create the SQL Server Inventory-Database Engine Config.xslx file. The SQL Server Inventory-CM-Database Engine Assessment.xslx file was not created. All in all it's a nice script. I just need a more "powerful" computer. :)
Coordinator
Oct 30, 2013 at 9:59 PM
Bill....wow! Ideally you should be able to run this on something that doesn't require massive amounts of CPU or RAM.

I've had looking into alternate storage methods for the output on my to-do list for a while now. You've made the perfect case for revisiting that task sooner than later.

--Kendal
Nov 12, 2013 at 2:23 PM

Kendal,

Yes, it appears that most of the time is taken up with creating XML file. Thanks.

-Bill