Basic Report - Disk Space Used

• 

  Colby Bouma

  September 12, 2018 17:20

  No, you have to use a SQL Report for that.

  SELECT
      Computers.Name AS "Computer Name"
      , LogicalDisks.LogicalDeviceId AS "Drive Letter"
      , ( (LogicalDisks.Size - LogicalDisks.FreeSpace) / 1024 / 1024 / 1024 ) AS "Used Space GB"
  FROM
      Computers
  INNER JOIN
      LogicalDisks USING (ComputerId)
  WHERE
      <ComputerFilter>
  

  0
• 

  Michael D

  September 12, 2018 17:36

  Yeah.. I had a sneaking suspicion.

  Any reason why this could not be included as an option in a basic report. Is it not as simple as getting the total disk size or free space from Windows ?

  0
• 

  Michael D

  September 14, 2018 14:13

  And if I wanted to use/add a field from a Custom Field. I have an "End-of-Life" field for example. A simple TRUE/FALSE.

  0
• 

  Colby Bouma

  September 25, 2018 22:48

  It would take up space in the database and you can calculate it from the data that is already gathered.

  0
• 

  Colby Bouma

  September 25, 2018 23:41

  It was surprisingly difficult to add a Custom Field to this report. I'll put in a feature request to add a Used Space field 😃

  SELECT
      Computers.Name AS "Computer Name"
      , LogicalDisks.LogicalDeviceId AS "Drive Letter"
      , ( (LogicalDisks.Size - LogicalDisks.FreeSpace) / 1024 / 1024 / 1024 ) AS "Used Space GB"
      , CustomComputerValues.Value AS "End-of-Life"
  FROM
      Computers
  -- LEFT JOIN lets you see all computers whether or not their Custom Field value has been set.
  LEFT JOIN
      CustomComputerValues USING (ComputerId)
  INNER JOIN
      LogicalDisks USING (ComputerId)
  WHERE
          -- This subquery allows you to use the Custom Field name instead of manually looking up your CustomComputerItemId.
          CustomComputerValues.CustomComputerItemId = (
              SELECT
                  CustomComputerItemId
              FROM
                  CustomComputerItems 
              WHERE
                  Name = 'End-of-Life'
          )
      OR
          -- This goes along with the LEFT JOIN in letting you see all computers.
          CustomComputerValues.CustomComputerItemId IS NULL
      AND
          <ComputerFilter>
  

  0
• 

  Michael D

  September 26, 2018 09:12

  Thanks Colby

  0
• 

  Michael D

  September 26, 2018 13:10

  Hi Colby,

  I think something is wrong. I am only getting 130 hits out of my 1500+ active PC's.

  Michael D.

  0
• 

  Colby Bouma

  September 27, 2018 21:45

  Do you have your Collection Source set to All Computers? "File --> Select Collection Source".

  0
• 

  Michael D

  September 28, 2018 06:14

  Yes.

  0
• 

  Colby Bouma

  October 09, 2018 00:05

  Aha, I only tested with 1 Custom Field! Things get a lot more complicated with multiple.

  I think this will work better:

  SELECT
  	CustomFieldSuperTable.Name AS "Computer Name"
  	, CustomFieldSuperTable.LogicalDeviceId AS "Drive Letter"
  	, ( (CustomFieldSuperTable.Size - CustomFieldSuperTable.FreeSpace) / 1024 / 1024 / 1024 ) AS "Used Space GB"
  	, CustomFieldSuperTable.Value AS "End-of-Life"
  FROM
  	-- Name the final result. I'm not 100% sure this is necessary, but I ran into problems with addressing CustomComputerItemId without it.
  	(
  			Computers
  		-- LEFT JOIN lets you see all computers whether or not their Custom Field value has been set.
  		LEFT JOIN
  			CustomComputerValues USING (ComputerId)
  		-- Create 1 row for each Custom Field for each Computer. This is required when some Custom Fields have been set and others haven't.
  		LEFT JOIN
  			CustomComputerItems USING (CustomComputerItemId)
  		INNER JOIN
  			LogicalDisks USING (ComputerId)
  	) AS "CustomFieldSuperTable"
  WHERE
  		-- This subquery allows you to use the Custom Field name instead of manually looking up your CustomComputerItemId.
  		CustomFieldSuperTable.CustomComputerItemId = (
  			SELECT
  				CustomComputerItemId
  			FROM
  				CustomComputerItems 
  			WHERE
  				Name = 'End-of-Life'
  		)
  	OR
  		-- Show computers whose Custom Fields have not been explicitly set yet.
  		CustomFieldSuperTable.CustomComputerItemId IS NULL
  	AND
  		<ComputerFilter>
  ORDER BY
  	-- COLLATE NOCASE makes the sort case insensitive
  	CustomFieldSuperTable.Name COLLATE NOCASE
  

  0
• 

  Michael D

  October 09, 2018 16:06

  I will check.

  0
• 

  Michael D

  October 10, 2018 09:51

  Well, not much better. 148 hits now.

  0

Please sign in to leave a comment.