/build/static/layout/Breadcrumb_cap_w.png
07/17/2019 161 views

I'm not really experienced with SQL but I made a duplicate of this built in KACE report "Software on Computer"
It grabs all systems but I would like to modify it to select only systems under a  label or specific system by name:

SQL:
Select CONCAT(MACHINE.NAME, "\\", MACHINE.SYSTEM_DESCRIPTION) as MACHINE_NAME, DISPLAY_NAME, DISPLAY_VERSION, PUBLISHER from (SOFTWARE, MACHINE_SOFTWARE_JT, MACHINE)  

where 

MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and

SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID 

and not IS_PATCH

order by MACHINE_NAME, DISPLAY_NAME

0 Comments   [ + ] Show comments

Comments


All Answers

0

If you do not know SQL stick with the wizard.

see this question and look at the answers there.

you would use the filter page to set what you want to view there. (label contains or device name = or contains)

https://www.itninja.com/question/k1000-compare-software-with-smart-labels

Answered 07/17/2019 by: SMal.tmcc
Red Belt

  • Once you create your base report you could easily duplicate and use wizard to just change filter for checking installed software on any label or machine
    • Ok I will try but the wizard doesn't give the same format I want like the SQL as it creates weird sub columns! Looks like garbage in Excel
      • ok create one with the wizard with the filters you want and export as sql, that will give the other code you need to insert into this one.
    • I found the missing code, now I got it working from your suggestion and thanks!
      I can now query a single machine or use a label...I just have to insert after "where"
      10 is for a single machine and 2)using a label
      1) WHERE ((MACHINE.NAME = 'xxx-xxx-xxx')) GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME
      2) WHERE ((( exists (select 1 from LABEL, MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID AND LABEL.TYPE <> 'hidden' and LABEL.NAME = 'xxxx')) ))
      • I understand the format problem, I usually stick to html view as much as I can.