CODEWITHSUNDEEP
sqlpowershell
deepti
deepti

Reputation: 729

powershell and SQL

I am working with powershell and SQL using a query to extract drive information from a server

I am writing following query

set @sql = 'C:\WINDOWS\SysWOW64\WindowsPowerShell\v1.0\powershell.exe -c "Get-WmiObject -Class Win32_Volume -Filter ''DriveType = 3'' | select name,label,capacity,freespace | foreach{$_.name+''!''+$_.label+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'   

xp_cmdshell @SQL

i get following output

output

*******************************************************************************************
C:\!RISCDCC36N03C$|139980.3984375%35242.921875*
D:\!RISCDCC36N03D$|139977.99609375%34774.08984375*
G:\!RISCDCSQL552G|92151.9375%46329.1875*
M:\!|0%0*
M:\RISCDCSQL557BMP\!RISCDCSQL557BMP|81911.9375%31869.3125*
M:\RISCDCSQL557DMP\!RISCDCSQL557DMP|40954.9375%37753.5*
M:\RISCDCSQL557CMP\!RISCDCSQL557CMP|20475.9375%7643.375*
T:\!RISCDCSQL563T$|81911.9375%15462*
R:\!RISCDCSQL561R$|35836.9375%19392.0625*
P:\RISCDCSQL560BMP\!RISCDCSQL560BMP|225278.9375%15844.625*
P:\!RISCDCSQL560P$|245759.9375%13014.75*
P:\RISCDCSQL560CMP\!RISCDCSQL560CMP|122876.9375%29950.9375*
P:\RISCDCSQL560AMP\!RISCDCSQL560AMP|102398.9375%100423.25*
L:\!RISCDCSQL556L$|20479.9375%5072.1875*
I:\!RISCDCSQL553I$|512003.9375%81162.5*
I:\RISCDCSQL553MP1\!RISCDCSQL553MP1|307200.9375%137322.9375*
X:\RISCDCSQL567CMP\!RISCDCSQL567CMP|97288.9375%45540.125*
X:\!|0%0*
X:\RISCDCSQL567AMP\!RISCDCSQL567AMP|35841.9375%28526.125*
U:\!RISCDCSQL564U$|66552.9375%7892*
NULL


**************************************************************************

I don't understand why for M drive and X drive it is giving 0%0. I am using this information to calculate further growth of space. while calculating it is givng me divide by zero.

Upvotes: 0

Views: 200

Answers (1)

AjV Jsy
AjV Jsy

Reputation: 6095

( docs http://msdn.microsoft.com/en-us/library/windows/desktop/aa394515%28v=vs.85%29.aspx )

It looks like drive M and X each have a full volume/partition (no free space) amongst the others.

Remove them from the output with an extra filter clause -
-Filter ''DriveType = 3 and freespace>0''

That works for me, in the Powershell prompt...

PS C:\Windows\System32> Get-WmiObject -Class Win32_Volume -Filter 'DriveType = 3
and freespace>0' | select name,label,capacity,freespace

Upvotes: 1

Related Questions