Can I query PDQ Inventory's database directly?

Pete,

You can get access to the database via ODBC, see link below.  We don't have any documentation for the database schema (yet) but the structure is pretty self explanatory.  Feel free to post any questions about the data layout here.

http://www.adminarsenal.com/admin-arsenal-blog/bid/67236/ODBC-Database-Access

Thanks for your response Adam. I've been working with this since you sent it along and overall it's fairly easy to understand. I am however seeing some odd/inconsistent data returns which I wanted to ask about. I'm not sure if there is a join I need to do that I'm just not aware of, or what's going on, but here is an example.

I have a report that looks for a certain application. This report that I put together (in the PDQ Inventory app), shows me that 593 computers have this app installed with the latest version, as well as a number of other computers with differing versions.

Next I run the query below from MS Query in Excel, which I believe should be doing essentially the same thing.

SELECT A.Name, A.Version, Count(*)
FROM main.Applications A
WHERE (A.Name Like 'Application-I'm-looking-for%')
GROUP BY A.Version

This query results in a result of 763 computers on which the application, with the same version, is installed. Since my query doesn't take into account any groups, as the PDQ Inventory app can, I am making sure to run the PDQ report against "All Computers". I'm a bit stumped as to why this is happening, so would appreciate your help.

Pete

Pete,

A couple of things spring to mind.  First, add A.Name to the group by.  It's probably not an issue, but if there are any rows with the same version and different names then they will be included together.  Second, and more likely, you may have some orphaned Applications (Applications without a computer).  PDQ Inventory can sometimes leave these behind, the next version will be better at cleaning them up.  You can join with Computers to do a subquery:

SELECT A.Name, A.Version, Count(*)
FROM main.Applications A
WHERE (A.Name Like 'Application-I'm-looking-for%')
  and ComputerId in (select ComputerId from Computers)
GROUP BY A.Name, A.Version

Give this a try and see if you get what you're expecting.

Thanks Adam, but outside of the fact that there are now two different entries with the same version, but different names (one ActiveX the other not), I get essentially the same result; 764 installations (590 ActiveX and 174 non-ActiveX).

Pete

Can you export the report to XML (right-click on the report name in the main window).  I'll see if something in there might explain the difference.

Sure, Here you are.

Thanks, I see the difference.  The report is showing the number of computers but the SQL is showing the number of applications and some computers have more than one matching application so they show up more than once in the SQL query.

Change your select to this:

SELECT A.Name, A.Version, Count(Distinct ComputerId)

That should give you what you want.

Thanks Adam, That was it! I’m now getting an exact match between my query and the PDQ Report.

Thanks for your help,

Pete

Excellent, let me know if there's anything else you need.