cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

Software Inventory Modern Dashboard

Hello,
we have 2 software products that are interdependent. If software 1 is installed, software 2 must also be installed. Unfortunately this is not always the case. I try to create a dashboard with the Software Inventory where I can see which servers have software 1 installed but software 2 is missing. Unfortunately I do not get any further. I get a report that reads all nodes with software 1 and all nodes with software 2 separately. But I can't get this into one report or Modern Dashboard together. The ideal case would be to build a KPI that shows that there are 5 servers that do not have both installed. And then I can display the 5 servers. It is important to ignore nodes that don't have any of these 2 software installed. It should only be checked if software 1 is installed, is software 2 also installed?

Thanks in advance.

 

0 Kudos
6 Replies

I would use Custom properties for this.  create a SQL agent job or an alert with the change custom property action set.  create 2 CPs - software1 and software2,  if software one exists set the custom property to the name of the software, if software two exists set the custom property, software2 to the name of the second software. then the query is easy, where software 1=name of software and software2 is null.  Then you can use groups to automatically assign an application template to everything with software1 or software2 installed.

0 Kudos

thanks for the answer but this is our last plan, we already have so many custom properties we would like to avoid more if possible. but yes this is one way to do it

0 Kudos
Level 8

a screenshot from the graphical query builder is not possible. Because if you go to nodes the software inventory is not available and if you go through software inventory you cannot add nodes. So the screenshot from the graphical query builder would be empty, because it does not offer the options I need.

i read out the number of software 1 and software 2 via a SWQL query and display the quantity via a kpi (see screenshot 1).
SWQL commands:

SELECT COUNT(1) AS Count_items, Publisher FROM(
SELECT n.Name,n.Publisher
FROM Orion.AssetInventory.Software n
WHERE (Name LIKE '%Software1%'))
AS subquery GROUP BY Publisher

SELECT COUNT(1) AS Count_items, Name FROM(
SELECT n.name
FROM Orion.AssetInventory.Software n
WHERE (Name LIKE '%Software2%'))
AS subquery GROUP BY Name

What's missing is a list of the 224 nodes that have software 1 installed but not software 2. I have not found a way to do that.

Enclosed is a screenshot 2 , this is how it should look like at the end.

 

0 Kudos

Maybe some screen shots of what you've accomplished so far?   Or at least the queries you're using?

Even if you used the "graphical query builder" you should be able to take a screenshot of what you've done and paste it here?

The undetailed answer is you need OUTER joins in your SQL or SWQL.

Left outer join will include the table from the joined table - but if no data the result set is does not get smaller

Full outer join will give you all:

- all data only exists in table one

- all data that only exists in table two

-all data that exists in both tables

 

Example made up fragment to show you how:

select a.val, b.val

from tablenamea as a

    LEFT JOIN tablename2 as b

        on a.key = b.key

 

You can further filter the result data by where clause.  Eg if you only want where their is no table two data, you can add this to the above:

WHERE b.key IS NULL

 

 

thats the way i tried it, but it didnt work. Maybe the problem is that both entries are in the same Database Table.

i have managed every possibility but not the one i need, i need the hosts which have installed Software 1 but not Software 2

Here i get all Nodes which have both Software installed:

SELECT
n.NodeName,
n.IPAddress,
s.Name,
c.Name
FROM Orion.Nodes n
INNER JOIN Orion.AssetInventory.Software AS s on s.NodeID = N.NodeID
INNER JOIN Orion.AssetInventory.Software AS c on c.NodeID = N.NodeID
WHERE (s.Name LIKE '%Software1%' and c.Name LIKE '%Software2%')

 

Here i get the Nodes only with Software 1 

SELECT
s.Name,
n.NodeName,
n.IPAddress
FROM Orion.AssetInventory.Software s
INNER JOIN Orion.Nodes AS n on n.NodeID = s.NodeID
WHERE (s.Name LIKE '%Software1%')

The Nodes with Software 2:

SELECT
s.Name,
n.NodeName,
n.IPAddress
FROM Orion.AssetInventory.Software s
INNER JOIN Orion.Nodes AS n on n.NodeID = s.NodeID
WHERE (s.Name LIKE '%Software2%')

 

For me it would be also okay when the result is a table like this:

 

Node Name       Software 1      Software 2

Node1               Software1      

Node2               Software1       Software 2

Node3               Software1       

.....

0 Kudos