Inventory Report Question / Logic

Comments

4 comments

  • Colby Bouma

    I'm pretty sure this can only be done with a SQL Report. I took a crack at it and I think I have what you're looking for. Unfortunately, Custom Fields are kinda nasty to work with in SQL Reports. Once you've confirmed this report is working I'll try to add your Custom Field to it.

    SELECT
    	  Computers.Name  		AS "Computer Name"
    	, Computers.SuccessfulScanDate	AS "Successful Scan Date"
    	, Computers.OSVersion		AS "O/S Version"
    	, Adobe.Name			AS "Application Name"
    	, Adobe.Version			AS "Application Version"
    FROM
    	Computers
    LEFT JOIN
    	-- LEFT JOIN keeps everything from Computers. If there isn't a match in Adobe it leaves the cell blank.
    	-- Adobe is a subquery that lets us store the results of a query in a temporary table and use it for things like this JOIN.
    	(
    		SELECT
    			  Applications.Name
    			, Applications.Version
    			, Applications.ComputerId
    		FROM
    			Applications
    		WHERE
    				Applications.Name LIKE 'Adobe Reader%'
    			OR
    				Applications.Name LIKE 'Adobe Acrobat%'
    	) Adobe USING (ComputerId)
    WHERE
    	<ComputerFilter>
    ORDER BY
    	-- This does a case insensitive sort by Computer Name
    	Computers.Name COLLATE NOCASE
    
    0
    Comment actions Permalink
  • Bill Clark

    Oh wow! Yes that worked perfectly from what I can tell. Shortly after posting my question and poking around some more I was like I bet this has to be done as a SQL report, if SQL can even do the conditions / statement programming needed. Unfortunately, my SQL knowledge is limited.

    The custom field name is called "Owner" and is Text type if that helps you. Appreciate you helping with this!

    0
    Comment actions Permalink
  • Bill Clark

    So I might not have done this the best way, but I took a stab at what you provided, and looked at PDQ Documentation on how you use SQL Import for custom fields and came up with the following that works.

    I found that CustomComputerItemID value is 1 for the field of Owner with this:

    select CustomComputerItemId from CustomComputerItems where Name like 'Owner';
    

    Then from there used pieces of the Import SQL stuff from your help page to piece this together.

    I will likely make more modifications to this report but here is the code I got working.

    SELECT
    	  Computers.Name  		AS "Computer Name"
    	, Computers.SuccessfulScanDate	AS "Successful Scan Date"
    	, CustomField.Value		AS "Owner"
    	, Computers.OSVersion		AS "O/S Version"
    	, Adobe.Name			AS "Application Name"
    	, Adobe.Version			AS "Application Version"
    FROM
    	Computers
    LEFT JOIN
    	-- LEFT JOIN keeps everything from Computers. If there isn't a match in Adobe it leaves the cell blank.
    	-- Adobe is a subquery that lets us store the results of a query in a temporary table and use it for things like this JOIN.
    	(
    		SELECT
    			  Applications.Name
    			, Applications.Version
    			, Applications.ComputerId
    		FROM
    			Applications
    		WHERE
    				Applications.Name LIKE 'Adobe Reader%'
    			OR
    				Applications.Name LIKE 'Adobe Acrobat%'
    	) Adobe USING (ComputerId)
    LEFT JOIN
    	(
    		SELECT
    			  CustomComputerValues.Value
    			, CustomComputerValues.ComputerId
    		FROM
    			CustomComputerValues
    		WHERE
    				CustomComputerValues.CustomComputerItemId LIKE '1'
    	) CustomField USING (ComputerId)
    
    WHERE
    	<ComputerFilter>
    ORDER BY
    	-- This does a case insensitive sort by Computer Name
    	Computers.Name COLLATE NOCASE
    
    0
    Comment actions Permalink
  • Colby Bouma

    That looks great! I'm glad you were able to piece that together. Let me know if you need help with any tweaks to it!

    0
    Comment actions Permalink

Please sign in to leave a comment.