/build/static/layout/Breadcrumb_cap_w.png

software on active machines

I need to know how many machines each software is installed on, disregarding inactive machines (K1000). Is there any way to collect this information through the database or through labels?


0 Comments   [ + ] Show comments

Answers (4)

Answer Summary:
Posted by: nick.wood 2 years ago
Yellow Belt
1

If I understand the question and assuming you are utilizing the Asset Status field set for your Devices (Active devices are set to Active), I think there are two approaches you could take.

Approach 1 - create a multi-topic report using the Report Wizard

To get you started, these are the minimum settings I would choose:

Title and Topic

  • Category: Software
  • Topic: Software
  • click "Add Subtopic" link and choose Device
  • check "Only show rows from Software with at least one Device row"

Fields to display

  • Software Info -> Display Name
  • [optional] Software Info -> Display Version
  • Device Identity Information > System Name

Sorts and Breaks

--- Software Order By:

  • Display Name, Ascending, check the "Break Header" box
  • [optional] Display Version, Ascending, check the "Break Header" box

Filters

--- Software (Matching all the following)

  • Devices  >  0
  • Is a Patch  is  False

--- Device (Match all the following)

  • Asset Status = Active

After you save the report, if you would like additional reports showing counts of other statuses, simply Duplicate the report and change the filter for Asset Status to something else.
With this approach, you will see item counts and the System Names as well.


Approach 2 - Create a SQL Report

Since you are just looking for counts by status, this code will get you: Software Names, Total count, Active Status count, All Other Status count. You should be all set to paste this into the SQL box:

SELECT ORG1.SOFTWARE.DISPLAY_NAME AS SoftwareName,
ORG1.SOFTWARE.INSTALLED_COUNT AS TotalCount,
SUM(CASE WHEN ORG1.ASSET_STATUS.NAME = "Active" THEN 1 ELSE 0 END) AS ActiveCount,
SUM(CASE WHEN ORG1.ASSET_STATUS.NAME != "Active" THEN 1 ELSE 0 END) AS OtherCount
FROM ORG1.MACHINE
JOIN ORG1.MACHINE_SOFTWARE_JT ON ORG1.MACHINE_SOFTWARE_JT.MACHINE_ID=ORG1.MACHINE.ID
JOIN ORG1.SOFTWARE ON ORG1.SOFTWARE.ID=ORG1.MACHINE_SOFTWARE_JT.SOFTWARE_ID
JOIN ORG1.ASSET ON ORG1.ASSET.MAPPED_ID=ORG1.MACHINE.ID
JOIN ORG1.ASSET_STATUS ON ORG1.ASSET_STATUS.ID=ORG1.ASSET.ASSET_STATUS_ID
WHERE ORG1.SOFTWARE.INSTALLED_COUNT > 0 AND ORG1.SOFTWARE.IS_PATCH=0
GROUP BY ORG1.SOFTWARE.DISPLAY_NAME
ORDER BY ORG1.SOFTWARE.DISPLAY_NAME;


If you are interested in being a bit more granular on Software Version as well, here's that added in:

SELECT ORG1.SOFTWARE.DISPLAY_NAME AS SoftwareName,
ORG1.SOFTWARE.DISPLAY_VERSION AS SoftwareVersion,
ORG1.SOFTWARE.INSTALLED_COUNT AS TotalCount,
SUM(CASE WHEN ORG1.ASSET_STATUS.NAME = "Active" THEN 1 ELSE 0 END) AS ActiveCount,
SUM(CASE WHEN ORG1.ASSET_STATUS.NAME != "Active" THEN 1 ELSE 0 END) AS OtherCount
FROM ORG1.MACHINE
JOIN ORG1.MACHINE_SOFTWARE_JT ON ORG1.MACHINE_SOFTWARE_JT.MACHINE_ID=ORG1.MACHINE.ID
JOIN ORG1.SOFTWARE ON ORG1.SOFTWARE.ID=ORG1.MACHINE_SOFTWARE_JT.SOFTWARE_ID
JOIN ORG1.ASSET ON ORG1.ASSET.MAPPED_ID=ORG1.MACHINE.ID
JOIN ORG1.ASSET_STATUS ON ORG1.ASSET_STATUS.ID=ORG1.ASSET.ASSET_STATUS_ID
WHERE ORG1.SOFTWARE.INSTALLED_COUNT > 0 AND ORG1.SOFTWARE.IS_PATCH=0
GROUP BY ORG1.SOFTWARE.DISPLAY_NAME, ORG1.SOFTWARE.DISPLAY_VERSION
ORDER BY ORG1.SOFTWARE.DISPLAY_NAME, ORG1.SOFTWARE.DISPLAY_VERSION;


If these aren't the exact reports you desire, I hope they at least get you started on the path to creating the perfect one!

Posted by: Marcos.vin 2 years ago
White Belt
0

Top Answer

I came to the solution as follows: I created two labels to classify the active and inactive machines. After that, through the K1000 database, I did the following SQL query.


SELECT ORG1.MACHINE_SOFTWARE_JT.MACHINE_ID, ORG1.MACHINE.NAME,

ORG1.SOFTWARE.DISPLAY_NAME AS SoftwareName,

ORG1.SOFTWARE.DISPLAY_VERSION AS SoftwareVersion,

ORG1.LABEL.ID, ORG1.LABEL.NAME

FROM MACHINE_LABEL_JT 

JOIN ORG1.MACHINE_SOFTWARE_JT ON ORG1.MACHINE_SOFTWARE_JT.MACHINE_ID=ORG1.MACHINE_LABEL_JT.MACHINE_ID

JOIN ORG1.SOFTWARE ON ORG1.SOFTWARE.ID=ORG1.MACHINE_SOFTWARE_JT.SOFTWARE_ID

JOIN ORG1.LABEL ON ORG1.LABEL.ID = ORG1.MACHINE_LABEL_JT.LABEL_ID

JOIN ORG1.MACHINE ON ORG1.MACHINE.ID = ORG1.MACHINE_SOFTWARE_JT.MACHINE_ID

WHERE   ORG1.MACHINE_LABEL_JT.LABEL_ID = 166 LIMIT 10000


I hope this solution will help other people!

Posted by: Nico_K 2 years ago
Red Belt
0
if you just want a sneak peek, you can go to Inventory | Software and search for it. There you can see how many systems have it installed and which ones.

If you want more, Reporting is made for that.
Posted by: Marcos.vin 2 years ago
White Belt
0
In inventory it is not possible to separate the software that is installed on the inactive machines from the active machines. And I couldn't find a way to generate a report with that information. I am currently trying to query directly through the database, but i have not yet succeeded. Do you know any other ways to separate software that is installed on inactive machines from active machines?
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ