Here is an example of what I use. CAST(FreeSpace0 AS nvarchar) + ' MB' AS FreeSpace, CAST(Size0 AS nvarchar) + ' MB' AS TotalSize, FreeSpace0 * 100 / Size0 AS PercentFree
[cid:[email protected]] Daniel Ratliff From: [email protected] [mailto:[email protected]] On Behalf Of Marcum, John Sent: Friday, January 03, 2014 3:46 PM To: '[email protected]' Subject: RE: [mssms] SQL query question - convery to GB or MB To add text into the cell you can do something like what I found here. http://msdn.microsoft.com/en-us/library/ms187928.aspx select top 5 cast (HDD_FreeSpace_MB0 /1024 AS VARCHAR) + ' GB' as 'Free Space' from v_GS_BDNA_BDNA_HARDWARE_DATA_1_30 Here's another example of what you are trying to do; SELECT sys.Name0 AS [Comptuer Name], hw.OS_Name0 AS OS, hw.OS_ServicePack0 AS [Service Pack], hw.GeneralAvailabilityDate0 AS [HW Availability Date], hw.Manufacturer0 AS Manufacturer, hw.Product0 AS Model, hw.Model0 AS Submodel, hw.ObsoleteDate0 AS [HW Obsolete Date], ROUND (CONVERT (FLOAT, hw.HDD_TotalSpace_MB0) / 1024, 2) AS [Disk Space], ROUND (CONVERT (FLOAT, hw.HDD_FreeSpace_MB0) / 1024, 2) AS [Free Space], ROUND (ROUND(CONVERT (FLOAT ,hw.RAM_TOTALPHYMEMORY_KB0) / 1048576, 2) * 1024, 0) AS Memory, hw.CPU_ClockSpeed0 AS [CPU Speed], hw.NumberOfCores0 AS [Core Count], SUBSTRING(SYS.User_Name0, CHARINDEX('\', SYS.User_Name0) + 1, LEN(SYS.User_Name0)) AS [User Name], TUSER.sn0 AS [Last Name], TUSER.givenName0 AS [First Name], TUSER.Mail0 AS [Email Address], TUSER.department0 AS Department, TUSER.l0 AS Facility FROM dbo.v_R_System sys LEFT OUTER JOIN v_GS_BDNA_BDNA_HARDWARE_DATA_1_30 hw ON sys.SMS_Unique_Identifier0 = hw.SMSUniqueIdentifier0 LEFT OUTER JOIN v_R_User TUSER ON TUSER.User_Name0 = SUBSTRING(sys.User_Name0, CHARINDEX('\', sys.User_Name0) + 1, LEN(sys.User_Name0)) WHERE (sys.Name0 =@machinename) ________________________________ John Marcum Sr. Desktop Architect Bradley Arant Boult Cummings LLP ________________________________ From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Dzikowski, Michael Sent: Friday, January 03, 2014 2:28 PM To: [email protected]<mailto:[email protected]> Subject: RE: [mssms] SQL query question - convery to GB or MB Ah! Thank you. That got me closer. So when I add this: ROUND(MEM.TotalPhysicalMemory0 / 1024.00, 0) AS [Memory (MB)] I get this: [cid:[email protected]] When I try to divide again: ROUND((MEM.TotalPhysicalMemory0 / 1024.00) / 1024, 0) AS [Memory (MB)] That got me here :) [cid:[email protected]] How can I get rid of those trailing zeros? From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Jim Boberg Sent: Friday, January 03, 2014 3:12 PM To: [email protected]<mailto:[email protected]> Subject: RE: [mssms] SQL query question - convery to GB or MB Mike, You can use ROUND in your Select statement and do the math there. For example, replace TotalPhysicalMemory0 as 'Total Physical Memory' with ROUND(MEM.TotalPhysicalMemory0 / 1024.00, 0) AS [Memory (MB)] Jim ________________________________ From: [email protected]<mailto:[email protected]> To: [email protected]<mailto:[email protected]> Subject: [mssms] SQL query question - convery to GB or MB Date: Fri, 3 Jan 2014 19:56:23 +0000 How can I convert the results from this query to show in MB or GB? select Netbios_Name0 as 'Server Name', Description0, InitialSize0 as ' Page File Initial Size', MaximumSize0 as 'Page File Max Size', TotalPageFileSpace0 as 'Total Page File Space', TotalPhysicalMemory0 as 'Total Physical Memory' From v_R_System VRS Join v_GS_PAGE_FILE_SETTING PFILE on PFILE.ResourceID = VRS.ResourceID JOIN v_GS_X86_PC_MEMORY MEM on MEM.ResourceID = VRS.ResourceID [cid:[email protected]] I saw John Nelson has a post on this, but was wondering if there was a different way: http://myitforum.com/myitforumwp/2011/11/02/sql-function-convert-bytes-to-kb-mb-gb-tb-pb-or-eb-and-format-the-output-to-human-readable-format/ Mike D- ________________________________ Confidentiality Notice: This e-mail is from a law firm and may be protected by the attorney-client or work product privileges. If you have received this message in error, please notify the sender by replying to this e-mail and then delete it from your computer. ________________________________ Confidentiality Notice: This e-mail is from a law firm and may be protected by the attorney-client or work product privileges. If you have received this message in error, please notify the sender by replying to this e-mail and then delete it from your computer. The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information.
<<inline: image004.png>>
<<inline: image005.png>>
<<inline: image006.png>>
<<inline: image007.png>>
