June 6, 2012 at 2:32 pm
Win32_Volume is more appropriate in some server environments. I use Win32_LogicalDisk in this environment as Win32_Volume is not a class on all the servers that I support.
Here is how you would do the same in PowerShell using the Win32_Volume class. It only took a couple minor tweaks.
Get-WMIObject Win32_Volume -Computer COMPUTERNAME | Select-Object -Property Volume,Capacity,FreeSpace,Name,@{Name="FreeSpace(GB)";Expression={"{0:N1}" -f($_.FreeSpace/1gb)}},@{Name="Capacity(GB)";Expression={"{0:N1}" -f($_.Capacity/1gb)}},@{Name="freespace(%)";Expression={"{0:N1}" -f(($_.FreeSpace/$_.Capacity)*100)}}
Edit: use quote instead of code window
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2012 at 2:33 pm
Perry Whittle (6/6/2012)
best way, the way i use...
😛
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2012 at 2:48 pm
opc.three (6/6/2012)
Win32_Volume is not a class on all the servers that I support.
Which OS\service pack?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 6, 2012 at 2:56 pm
Perry Whittle (6/6/2012)
opc.three (6/6/2012)
Win32_Volume is not a class on all the servers that I support.Which OS\service pack?
They still have some 2000 boxes around.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2012 at 3:30 pm
Ah that's ok then, you're running SQL server 2000 on this box?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 6, 2012 at 3:35 pm
Perry Whittle (6/6/2012)
Ah that's ok then, you're running SQL server 2000 on this box?
One is running SQL 2000 Standard Edition x86...it's a memory bound old beast serving data for ColdFusion websites.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2012 at 3:52 pm
Yes, SQL 2000 doesn't support mount points anyway so that's fine. You'll need to use xp_fixeddrives to get the free space and disk name
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 6, 2012 at 4:18 pm
Perry Whittle (6/6/2012)
You'll need to use xp_fixeddrives to get the free space and disk name
Well, someone may. I use PowerShell + WMI for disk space monitoring and I check all the servers I support from one script running on an app server instead of a proc or job running on every instance 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2012 at 5:05 pm
Where as I just use wmi from a central instance
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 6, 2012 at 5:09 pm
Perry Whittle (6/6/2012)
Where as I just use wmi from a central instance
What do you use to check the results of the call to wmic you posted, and to send an alert? Windows Batch or VBS? Or...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2012 at 5:15 pm
opc.three (6/6/2012)
Perry Whittle (6/6/2012)
Where as I just use wmi from a central instanceWhat do you use to check the results of the call to wmic you posted, and to send an alert? Windows Batch or VBS? Or...
Never mind, I get what you're doing now with text files and T-SQL.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2012 at 7:41 pm
opc.three (6/6/2012)
Jeff Moden (6/5/2012)
opc.three (6/5/2012)
Please do not enable OLE Automation procs unless absolutely necessary...Please explain why or provide a link that does.
Issues I have with the OLE Automation procedures:
Issue #1
From BOL article OLE Automation Objects in Transact-SQL:
Unhandled errors in the Class_Initialize and Class_Terminate subroutines can cause unpredictable errors, such as an access violation in an instance of the Database Engine.
This means that a poorly written COM object used from one of the sp_OA procs can cause an access violation when running in the SQL Server process. That gives me pause.
CLR objects do not suffer from this issue.
Funny... you left out an important part of that. The sentence immediately before what you referenced says...
A custom in-process OLE server that is created by using Microsoft Visual Basic must have an error handler (specified with the On Error GoTo statement) for the Class_Initialize and Class_Terminate subroutines.
First, I see Geoff using the FileSystemObject but I don't see any VB in his code. I also see that he's got error checking galore in the code.
Also, CLR objects may not suffer from the specific issue, but they can and frequently do suffer from bad code in general. Although they may not suffer from memory leaks, they can just use too much memory because people may not have written them correctly and they can be incredibily slow and tough on all resources just like the bloody "generic" CLR Audit Triggers I'm currently in the process of replacing.
The real fact of the matter is the any code can be written improperly and telling someone to avoid a useful tool based on the possibility of writting some bad code doesn't hold any water because you can write bad code anywhere.
For example, I wouldn't say not to use CLRs just because there's a chance to write bad code that will use all of the memory resources in SQL Server or someone may write a "generic" trigger that takes 4 minutes (true story) to audit the updates of a 10,000 row, 5 column update.
Issue #2
A poorly written COM object can also have a memory leak which over time Windows cannot recover from without a reboot.
Managed CLR objects do not suffer from memory leaks unless there is a bug in the .NET Framework. Not choosing the SQLCLR for this reason would be like not choosing SQL Server in case there was a memory leak in SQLOS. It's possible, but I would venture a guess that it is somewhat rare, has limited exposure and is something Microsoft will react to fast.
A poorly written CLR can be just as bador worse in other areas. And, no... I'm not picking on CLR's. I'm just using your example. Any piece of poorly written code, CLR or not, can crash a server. It's not limited to memory leaks in a COM object.
Issue #3
From BOL article sp_OACreate (Transact-SQL):
[Use of sp_OACreate] Requires membership in the sysadmin fixed server role.
Are there ways to lock this down, sure, but its not a trivial affair, and not required meaning someone grabbing for a quick solution off the shelf is likely to bypass the effort. Does that make OLE Automation procs bad? No. sp_OA procs do not create security holes, database administrators do, but there are logical stopping points that provoke decisions when using other tools like SQLCLR to expose functionality not provided in T-SQL making it a better option IMHO.
Let me ask you a question. We're talking about finding disk space on 1 or more systems. Who do you actually want doing that? There should be only 2 answers. 1 is the DBA. The other is a scheduled job. Both have "SA" privs. Why are you worried about having to lock down sp_OA* usage? Is it because someone screwed up and gave Devs and/or apps "SA" privs??? If so and trivial or not, that means you have a major, extremely grave, extremely dangerous situation on your server and you better fix it RIGHT NOW!!! Disabling sp_OA* and similar "SA only" functionality won't help. Shoot... if you have anyone other than the DBA's or the system itself that has "SA" privs, I can show you how to get to the command line using T-SQL even if xp_CmdShell has been deleted from the system.
...
The first issue is cause enough for me to leave them disabled. The third is also a deal-breaker when looking for or recommending a solution. Since SQL 2005 the OLE Automation procs have been disabled by default forcing us to make a conscious decision to enable them. The same can be said of SQLCLR, and when searching for a solution and deciding which one to enable guess which way I would choose. SQLCLR is my preferred alternative.
If you have not taken the steps to properly lock down a server, then I don't blame you for fearing things like sp_OA*. Just don't think for a minute that you're not going to have some major problems with CLR because not everyone is going to pay attention to the same level of detail nor take time to truly understand CLR as you will.
Also, is there a way to format that one line of code into logical sections in a multiline format to enhance readability or would that break the code?
Here is another way to write the same code. The backtick escapes the line-break letting PowerShell know we're continuing our code on the next line. The pipe separates commands instructing PowerShell to feed the results of one command into the next creating a left-to-right pipeline. In this case PowerShell runs the Get-WMIObject CmdLet and pipes the results into the Select-Object CmdLet (can be aliased as "Select").
Get-WMIObject Win32_LogicalDisk -Filter "DriveType=3" -Computer "COMPUTERNAME" `| Select-Object SystemName,`
DeviceID,`
VolumeName,`
FileSystem,`
BlockSize,`
NumberOfBlocks,`
@{Name="size(GB)";Expression={"{0:N1}" -f($_.size/1gb)}},`
@{Name="freespace(GB)";Expression={"{0:N1}" -f($_.freespace/1gb)}},`
@{Name="freespace(%)";Expression={"{0:N1}" -f(($_.freespace/$_.size)*100)}}
You can also break up the pipeline and do the same this way in case you want to inspect the variable values along the way or use them for multiple purposes:
$wmiInfo = (Get-WMIObject Win32_LogicalDisk -Filter "DriveType=3" -Computer "COMPUTERNAME")Select-Object `
-InputObject $wmiInfo `
-Property SystemName,`
DeviceID,`
VolumeName,`
FileSystem,`
BlockSize,`
NumberOfBlocks,`
@{Name="size(GB)";Expression={"{0:N1}" -f($_.size/1gb)}},`
@{Name="freespace(GB)";Expression={"{0:N1}" -f($_.freespace/1gb)}},`
@{Name="freespace(%)";Expression={"{0:N1}" -f(($_.freespace/$_.size)*100)}}
Get PowerShell 2.0 and use the PowerShell ISE (Integrated Scripting Environment, powershell_ise.exe) to try out this code.
Edit: remove actual computer name from sample code
Thank you so much for the backtick hint. While I was waiting for your response, I found that lovely trick on the internet. Thank you for confiming it and for your examples.
As a side bar that you might take as a total flip from the pro to the con side on my part, I would absolutely agree that people who post sp_OA* solutions and other solutions that require turning on any feature that requires "SA" privs, also be kind enough to warn of the ramifications in a system that isn't properly locked down. BUT it really doesn't take much to lock down a system for this kind of thing. Do an audit of the users and logins and see if anyone other than the DBAs or the system itself has "SA" privs. If there are any, then, Step 1) do not turn on such features and Step 2) stop production and FIX IT RIGHT NOW because your hiney is sticking way out.
For all of those that are going to warn that a properly locked down system may not be properly locked down in the future, I would suggest that it had better be or your company will lose ISO, SOX, Sec, PCI, and a dozen other certifications that are required for your company to do business. Even if your company doesn't need such certifications to be in business, your company's servers should be properly locked down or the company might not be in business for very much longer.
Ok... I've gotta go now because some idiots working for LinkedIn didn't know how to protect a couple of million passwords and I have to change mine because of it. I'm just betting is wasn't because they simply had something like sp_OA* in use.
{EDIT} Fixed a couple of spelling errors.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 6, 2012 at 7:57 pm
Jeff Moden (6/6/2012)
Ok... I've gotta go now because some idiots working for LinkedIn didn't know how to protect a couple of million passwords and I have to change mine because of it. I'm just betting is wasn't because they simply had something like sp_OA* in use.
ROFL Mine was safe 🙂
Jared
CE - Microsoft
June 6, 2012 at 9:31 pm
Jeff Moden (6/6/2012)
Funny... you left out an important part of that. The sentence immediately before what you referenced says...A custom in-process OLE server that is created by using Microsoft Visual Basic must have an error handler (specified with the On Error GoTo statement) for the Class_Initialize and Class_Terminate subroutines.
Sure, it is required per interface, but the fact that one exists says nothing about unhandled exceptions that may arise within those error handlers. I have written VB6 objects for use with SQL Server. I also left out this one:
If an in-process OLE server is allowed (by using a context value of 1 or 5 or by not specifying a context value), it has access to memory and other resources owned by SQL Server. An in-process OLE server may damage SQL Server memory or resources and cause unpredictable results, such as a SQL Server access violation.
The fact of the matter is that generally speaking, employing the OLE Automation procs is inherently more risky than using the SQLCLR. In a forum setting where people are seeking guidance or a quick solution I'll steer them away from OLE Automation and towards the SQLCLR 100 times out of 100.
Also, CLR objects may not suffer from the specific issue, but they can and frequently do suffer from bad code in general. Although they may not suffer from memory leaks, they can just use too much memory because people may not have written them correctly and they can be incredibily slow and tough on all resources just like the bloody "generic" CLR Audit Triggers I'm currently in the process of replacing.
Thats fair, but a SQLCLR assembly using only .net classes included in the base class library is not going to cause an abrupt end to the SQL Server process or worse, which is why I recommend it whenever possible over the sp_OA procs. I guess I am drawing from the LCD-well a bit. Feel free to disagree. I can agree to disagree here, but sp_OA procs are an inferior option to SQLCLR in all fathomable cases (in my mind).
Let me ask you a question. We're talking about finding disk space on 1 or more systems. Who do you actually want doing that? There should be only 2 answers. 1 is the DBA.
Actually, I see this as a borderline task for us or a system administrator, and sometimes we have to do these things. Turning that into a task that emenates from inside the database however is something I choose not to do.
The other is a scheduled job. Both have "SA" privs. Why are you worried about having to lock down sp_OA* usage? Is it because someone screwed up and gave Devs and/or apps "SA" privs??? If so and trivial or not, that means you have a major, extremely grave, extremely dangerous situation on your server and you better fix it RIGHT NOW!!! Disabling sp_OA* and similar "SA only" functionality won't help. Shoot... if you have anyone other than the DBA's or the system itself that has "SA" privs, I can show you how to get to the command line using T-SQL even if xp_CmdShell has been deleted from the system.
Another thing I do not need to worry about because I leave that unnamed feature disabled as well.
...
The first issue is cause enough for me to leave them disabled. The third is also a deal-breaker when looking for or recommending a solution. Since SQL 2005 the OLE Automation procs have been disabled by default forcing us to make a conscious decision to enable them. The same can be said of SQLCLR, and when searching for a solution and deciding which one to enable guess which way I would choose. SQLCLR is my preferred alternative.
If you have not taken the steps to properly lock down a server, then I don't blame you for fearing things like sp_OA*. Just don't think for a minute that you're not going to have some major problems with CLR because not everyone is going to pay attention to the same level of detail nor take time to truly understand CLR as you will.
I do not fear sp_OA because it is disabled. For the record I would NOT ever do this work using the SQLCLR as it would require EXTERNAL_ACCESS and I stick with SAFE assemblies at all costs.
Thank you so much for the backtick hint. While I was waiting for your response, I found that lovely trick on the internet. Thank you for confiming it and for your examples.
You're very welcome.
As a side bar that you might take as a total flip from the pro to the con side on my part, I would absolutely agree that people who post sp_OA* solutions and other solutions that require turning on any feature that requires "SA" privs, also be kind enough to warn of the ramifications in a system that isn't properly locked down.
Geoff did follow up in kind.
Edit: correct mistyped phrase relevant to one of my points
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2012 at 11:01 pm
As always, you're a gentleman and a scholar, Orlando. Thanks for talking about this. There will always be some disagreement between thee and me on these types of things and, yes, I have to agree to disagree about some of it. I do absolutely agree that people should be warned of what could happen if they have users/apps that have "SA" privs when some of this "SA" functionality is turned on. I have to say it again that having those things turned off will do no good if an attacker get's in as "SA" because some folks just don't understand that particular problem.
Shifting gears and having a great amount of fun, you'll be very pleased that I've at least met you half-way by discovering some of the wonders of PowerShell. I say "half way" because... well... you'll figure that out when you see the following code. 😀
IF OBJECT_ID('tempdb..#CmdOutput','U') IS NOT NULL
DROP TABLE #CmdOutput
;
CREATE TABLE #CmdOutput (RowNum INT IDENTITY(1,1), OutputText VARCHAR(512))
;
INSERT INTO #CmdOutput
(OutputText)
EXEC xp_CmdShell 'MODE CON: COLS=200 && PowerShell "$Item = @(''SystemName'',''VolumeName'',''DeviceID'',''Description'',''Compressed'',''DriveType'',''FileSystem'',''FreeSpace'',''MediaType'',''Size'',''VolumeDirty''); Get-WMIObject -query ''Select * From Win32_LogicalDisk'' | Format-Table $item -auto; exit;"'
;
SELECT *
FROM #CmdOutput
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply