We are looking for a query to obtain the following information from the database directly
I know this is broad, but can you please help me with the query for this?
May 05, 2020 09:30 AM
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.
May 05, 2020 10:13 AM
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
May 05, 2020 09:44 AM
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.
May 05, 2020 09:51 AM
May 05, 2020 09:58 AM
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
May 05, 2020 10:00 AM
Is there a way I can application version to it?
May 05, 2020 10:02 AM
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.
May 05, 2020 10:13 AM
Thank you guys! This should work. let me get back to you!!! You are super helpful!
May 05, 2020 10:17 AM
Thanks again. This query is heavy. I guess I could limit it to Commercial software by linking it to SoftwareTitleClassification table?
May 05, 2020 01:07 PM - edited May 05, 2020 01:38 PM
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
May 06, 2020 01:47 AM
If you would like to start SQL scripting or have a little refresh, there is a free SQL course on www.sololearn.com.
May 05, 2020 10:20 AM