Hello,

I created a software asset is my software library. example:
Acrobat reader in the category Office (with dynamic label software level)

I would like to create a query that gives me a list of computers with software my software library. But I can not find how to do this.
Example: Computer1 | Acrobat Reader | Office |
But only with my list of my library of software assets.

how to create a query that gives me a list of computers with my list of software present in the asset section.

Thank you for your help.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

I'm not sure 100% what you are asking, but take a look at this. Let me know if it needs to be modified. Change the "Category" and "Display_name" to match what you need.

SELECT M.NAME, S.DISPLAY_NAME, CATEGORY

FROM SOFTWARE S

LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID)

LEFT JOIN MACHINE M ON (M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID)

WHERE CATEGORY = 'OFFICE'

AND S.DISPLAY_NAME = 'ACROBAT READER'

ORDER BY M.NAME
Answered 06/03/2013 by: dugullett
Red Belt

Please log in to comment
0

Thank you very much for your response. But that does not go for the category is not in the inventory asset part.

I am looking for the SQL level that gives me my computer list by software in the active part of the K1000 and not in the inventory part relationship.

I do not know if I am clear in my explanations :)

Answered 06/03/2013 by: nekkar
Senior Purple Belt

  • I guess I'm still not following. Where is the category coming into play? When you say you created a software asset do you mean you created a new software record under Inventory>Software>Add New Item, or that you created a new an actual new software record?

    Do you want a list of machines with the associated software?
Please log in to comment
0

In Part asset:

I create an entry in software with a dynamic label.
It gives me the number of computers that have this software but I that number.
I would like to have a report that gives me the list of computer names.

Answered 06/03/2013 by: nekkar
Senior Purple Belt

  • Ok. I misunderstood. Off the top of my head I do not know I way to key off of that label name since it doesn't populate in the Software table. I'll look to see if I can find it somewhere else.

    In the meantime could you just do something like this?

    SELECT M.NAME, S.DISPLAY_NAME
    FROM SOFTWARE S
    LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID)
    LEFT JOIN MACHINE M ON (M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID)
    where display_name like '7-Zip %'
    ORDER BY S.DISPLAY_NAME, M.NAME
    • Try this. Change the 2nd to the last line to match your label name.

      SELECT DISTINCT M.NAME,
      GROUP_CONCAT(DISTINCT S.DISPLAY_NAME SEPARATOR '\n') AS SOFTWARE
      FROM SOFTWARE S
      LEFT JOIN MACHINE_SOFTWARE_JT MSJT ON (MSJT.SOFTWARE_ID = S.ID)
      LEFT JOIN MACHINE M ON (M.ID = MSJT.MACHINE_ID)
      LEFT JOIN SOFTWARE_LABEL_JT SLJT ON S.ID=SLJT.SOFTWARE_ID
      LEFT JOIN LABEL L ON L.ID=SLJT.LABEL_ID
      WHERE L.NAME ='<ENTER LABEL NAME HERE>'
      GROUP BY M.NAME
      ORDER BY M.NAME
Please log in to comment
0

This is ok but is not good for me :)

When I run this query:

 SELECT  ASSET.NAME AS ASSET_NAME , (SELECT STRAIGHT_JOIN COUNT(distinct MACHINE_SOFTWARE_JT.MACHINE_ID) FROM  SOFTWARE   JOIN MACHINE_SOFTWARE_JT ON MACHINE_SOFTWARE_JT.SOFTWARE_ID=SOFTWARE.ID  WHERE  (SOFTWARE.ID = ASSET.MAPPED_ID  OR SOFTWARE.ID IN (SELECT SOFTWARE_ID FROM ASSET_DATA_6 JOIN SOFTWARE_LABEL_JT 
ON LABEL_ID = ASSET_DATA_6.FIELD_10001
WHERE ASSET_DATA_6.ID = ASSET.ASSET_DATA_ID))) as INSTALLED , (SELECT SUM(ASSET_DATA_7.FIELD_1)
FROM ASSET_ASSOCIATION J22
JOIN ASSET A22 ON A22.ID = J22.ASSET_ID
JOIN ASSET_DATA_7 ON ASSET_DATA_7.ID = A22.ASSET_DATA_ID
WHERE J22.ASSOCIATED_ASSET_ID = ASSET.ID AND J22.ASSET_FIELD_ID=2) AS LICENSES, AD.FIELD_94 as 'Version en prod' , AD.FIELD_91 as 'Categorie', AD.FIELD_96 as 'Version d OS', AD.FIELD_95 as 'Version' FROM ASSET
LEFT JOIN USER U ON U.ID = ASSET.OWNER_ID
LEFT JOIN MACHINE M ON ASSET.MAPPED_ID = M.ID and ASSET.ASSET_TYPE_ID = 5
LEFT JOIN ASSET_DATA_6 AD ON AD.ID = ASSET.ASSET_DATA_ID
where ASSET.ASSET_TYPE_ID = 6
GROUP BY ASSET.ID
ORDER BY ASSET_NAME
  i have this : 


I get much the number of computers that have this or that software.
So I said that the opposite should be possible to have the list of my computers with the software part of the ASSET (not INVENTORY).

I have 200 in the software part ASSET and 2200 titles in
INVENTORY.
Many
software does not interest me.
that's why I'd like to make the link between asset software and computer name.
Answered 06/03/2013 by: nekkar
Senior Purple Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share