Software Inventory Modern Dashboard

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 problem is that there is no way to select the fields using the graphical query builder. Therefore a screenshot makes little sense as it will simply be empty.
If you report on nodes, the graphical query builder will not be able to use the software inventory. If you report on Software Inventory there is no node selection. Therefore I cannot show anything on the graphical query builder.

What i have done so far is to count on how many systems software 1 and software 2 are installed

I used the SWQL query for this:

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

And over this 2 SWQL query i put a Numeric KPI that shows the count (i attached a screenshot).

what I need additionally now is a list with the difference of the nodes.

In this Case i need a list with the  214 Nodes that have Installed Software 1 but not Software 2. And i find no way how to do this. 

Attached a second screenshot how it should look like in the end

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.

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

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.

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       

.....

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