1. PDQ Deploy & Inventory Help Center
  2. Community
  3. PDQ Inventory
 

Important Notice: On February 29th, this community was put into read-only mode. All existing posts will remain but customers are unable to add new posts or comment on existing. Please feel to join our Community Discord for any questions and discussions.

SQL Report on Computer Collections Not Working

Avatar
Michael Bierch

I've got a problem with the SQL Report I'm creating. I'm trying to get a File Inventory report generated and limiting it to a specific collection of Computers. I've tried it a couple of ways but the report includes all of the computers instead of just the computers in the collection. I've tried selecting the "Select Collection Source" option and I've tried using the following SQL:

select Computers.Name,FilePaths.PathName,Files.FileName,Files.Modified,Files.Size,Files.Type FROM Computers LEFT JOIN CollectionComputers ON Computers.ComputerId = CollectionComputers.ComputerId LEFT JOIN Files ON Computers.ComputerId = Files.ComputerId LEFT JOIN FilePaths ON Files.FilePathId = FilePaths.FilePathId WHERE CollectionComputers.CollectionId = 1748 ORDER BY Computers.Name

Nothing seems to limit the report output to the desired collection. What am I doing wrong?

0

Comments

3 comments
Date Votes
  • Avatar
    Margus Potter

    You dont need define in sql any collections / id-s etc, make generic one and run report directly on collection container, you know, right click and Run Report...

    0
  • Avatar
    Colby Bouma

    You need to use WHERE <ComputerFilter>, a special value that Inventory's reporting engine uses to filter your results by the selected collection. https://documentation.pdq.com/PDQInventory/17.1.0.0/sql-reports.htm#creatingsqlreport

    SELECT
	  Computers.Name
	, FilePaths.PathName
	, Files.FileName
	, Files.Modified
	, Files.Size
	, Files.Type
FROM
	Computers
LEFT JOIN
	Files ON Computers.ComputerId = Files.ComputerId
LEFT JOIN
	FilePaths ON Files.FilePathId = FilePaths.FilePathId
WHERE
	<ComputerFilter>
ORDER BY
	Computers.Name
    0
  • Avatar
    Michael Bierch

    Ah...enlightenment! Thank you...makes sense...working like a champ!

    Thanks a lot!

    0