Inventory Report Question / Logic

0

I am trying to build a report to capture some basic machine info but can't seem to figure out how to do it without creating 2 reports and merging the info after. The main issue is because I am trying to capture specific Application info, but I don't need a line item for every app installed on the machine.

Report Columns: Computer - Name, Computer - Owner (Custom field), Computer - Successful Scan Date, Computer - O/S Version, Application - Name, Application - Version

Filter: Any Application Name Starts with Adobe Acrobat or Adobe Reader. I tried doing if Application Name equal blank but that didn't work.

I need the report to contain every computer name from the collection at least once even if the Application info is empty, then if either Application Name starts with "Adobe Acrobat" OR "Adobe Reader" it will report the name and version, which I know would be separate lines if both are installed. I just don't want a line item for every App on the machine, but I need every PC to show at least once. So right now, the report is only reporting machines that have at least one of those Adobe products installed. For the ones that do not contain either Adobe on them, is there a way to make it show them at least once with the application fields blank? Or will I need to make 2 reports and merge them later if I want them on 1 report?

Cancel
login to comment
0

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
Cancel
login to comment

0

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!

Cancel
login to comment

0

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

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!

Cancel
login to comment

Reply