/build/static/layout/Breadcrumb_cap_w.png

Report : relationship between computer and software assets

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

Answers (4)

Posted by: dugullett 10 years ago
Red Belt
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
Posted by: nekkar 10 years ago
Senior Purple Belt
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 :)


Comments:
  • 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? - dugullett 10 years ago
Posted by: nekkar 10 years ago
Senior Purple Belt
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.


Comments:
  • 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 - dugullett 10 years ago
    • 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 - dugullett 10 years ago
Posted by: nekkar 10 years ago
Senior Purple Belt
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.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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