SQL Query for obtaining the application deployed list

[flexeranoob]

We are looking for a query to obtain the following information from the database directly

1. Hostname
2. IP Address
3. Application Name
4. Application Version
5. Last inventory date

I know this is broad, but can you please help me with the query for this?

[jjensen]

Hello @flexeranoob ,

Do you mean something like this?:

SELECT
cc.ComputerName AS 'Hostname',
cc.IPAddress,
st.FullName AS 'Application',
cc.InventoryDate
FROM InstalledApplications ia
JOIN ComplianceComputer cc ON cc.ComplianceComputerID = ia.ComplianceComputerID
JOIN SoftwareTitle st ON st.SoftwareTitleID = ia.SoftwareTitleID
WHERE cc.ComputerName = 'deviceName1'

HTH,

Joseph

[flexeranoob]

Hi @jjensen thanks, something like this, but for all devices.  Can you please help me and add that part. I am not so good at joins. and definitely not at optimizing queries. 

[jjensen]

@flexeranoob,

In theory, you could just remove the WHERE clause if you wanted a return set for all devices.

However - this is potentially going to return a LOT of rows (1 per application per device), so I wouldn't recommend running such a query without considering performance first.

[mfranz]

Just leave out the WHERE line.

SELECT
cc.ComputerName AS 'Hostname',
cc.IPAddress,
st.FullName AS 'Application',
cc.InventoryDate
FROM InstalledApplications ia
JOIN ComplianceComputer cc ON cc.ComplianceComputerID = ia.ComplianceComputerID
JOIN SoftwareTitle st ON st.SoftwareTitleID = ia.SoftwareTitleID

[flexeranoob]

Is there a way I can application version to it?

[mfranz · Accepted solution]

Application version should be part of the FullName attribute. If you want distinct fields for version, edition, etc. the query would include additional tables:

SELECT
cc.ComputerName AS 'Hostname',
cc.IPAddress,
stpub.PublisherName,
stp.ProductName,
stv.VersionName,
ste.EditionName,
cc.InventoryDate
FROM InstalledApplications ia
JOIN ComplianceComputer cc ON cc.ComplianceComputerID = ia.ComplianceComputerID
JOIN SoftwareTitle st ON st.SoftwareTitleID = ia.SoftwareTitleID
JOIN SoftwareTitleProduct stp ON st.SoftwareTitleProductID = stp.SoftwareTitleProductID
JOIN SoftwareTitlePublisher stpub ON stp.SoftwareTitlePublisherID = stpub.SoftwareTitlePublisherID
LEFT JOIN SoftwaretitleVersion stv ON st.SoftwaretitleVersionID = stv.SoftwareTitleVersionID
LEFT JOIN SoftwareTitleEdition ste ON st.SoftwareTitleEditionID = ste.SoftwareTitleEditionID

The inventoryDate is per computer, not per application.

[flexeranoob]

Thank you guys!  This should work.  let me get back to you!!! You are super helpful!

[flexeranoob]

Thanks again.  This query is heavy.  I guess I could limit it to Commercial software by linking it to SoftwareTitleClassification table?

[mfranz]

Here you go.

SELECT
cc.ComputerName AS 'Hostname',
cc.IPAddress,
stpub.PublisherName,
stp.ProductName,
stv.VersionName,
ste.EditionName,
cc.InventoryDate
FROM InstalledApplications ia
JOIN ComplianceComputer cc ON cc.ComplianceComputerID = ia.ComplianceComputerID
JOIN SoftwareTitle st ON st.SoftwareTitleID = ia.SoftwareTitleID
	AND st.SoftwareTitleClassificationID = 3 -- Commercial
JOIN SoftwareTitleProduct stp ON st.SoftwareTitleProductID = stp.SoftwareTitleProductID
JOIN SoftwareTitlePublisher stpub ON stp.SoftwareTitlePublisherID = stpub.SoftwareTitlePublisherID
LEFT JOIN SoftwaretitleVersion stv ON st.SoftwaretitleVersionID = stv.SoftwareTitleVersionID
LEFT JOIN SoftwareTitleEdition ste ON st.SoftwareTitleEditionID = ste.SoftwareTitleEditionID

[mfranz]

If you would like to start SQL scripting or have a little refresh, there is a free SQL course on www.sololearn.com.