Basic Report - Disk Space Used

Comments

12 comments

  • Colby Bouma

    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
    Comment actions Permalink
  • Michael D

    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
    Comment actions Permalink
  • Michael D

    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
    Comment actions Permalink
  • Colby Bouma

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

    0
    Comment actions Permalink
  • Colby Bouma

    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
    Comment actions Permalink
  • Michael D

    Thanks Colby

    0
    Comment actions Permalink
  • Michael D

    Hi Colby,

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

    Michael D.

    0
    Comment actions Permalink
  • Colby Bouma

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

    0
    Comment actions Permalink
  • Michael D

    Yes.

    0
    Comment actions Permalink
  • Colby Bouma

    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
    Comment actions Permalink
  • Michael D

    I will check.

    0
    Comment actions Permalink
  • Michael D

    Well, not much better. 148 hits now.

    0
    Comment actions Permalink

Please sign in to leave a comment.