/bundles/itninjaweb/img/Breadcrumb_cap_w.png
How can I have the result of one query be a parameter in a second query. The screenshot shows what I am attempting to do. In the first I get the software id of a particular piece of software and then that software id is used to generate a list of computers that have it installed. The software versions get updated fairly regularly and this would automate the list, without hardcoding in something that has to be changed regularly.

PVaiduY.png
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Community Chosen Answer


Answers

1
You use a sub select statement:
SELECT column1, column2, column3 from table
WHERE column4 = (select column5 from another table)

That's a very simple example, obviously. The sub select statement needs to return just one value in this instance, so the query should just return the SC.ID. If you need the outer statement to match multiple values, then you need to craft the sub select statement to return a comma separated list using GROUP_CONCAT and group the results. Your where statement would then become column4 in (select group_concat(column5) from table group by column6).

Answered 03/19/2018 by: chucksteel
Red Belt

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

Answers

This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

0
You may use smart sql labels:

1. Create a Device Smart Label and name it (ie: "PCs with Solstice"). Save it. Go again in editing mode and click Edit SQL. Paste your first SQL code and save it
2. Create a report and apply it only to the "PCs with Solstice" label
Answered 03/16/2018 by: c.castellari
Orange Belt

  • The first sql query tells me the software_id (from the software catalog) of solstice. Then that software_id is used to generate the list of machines with the second query. I am not following your example. Can you elaborate? The results of the first query doesn't return a device. It returns an id. I went ahead and tried your steps, just to see. I don't see a place when creating a report to apply it to a label.
    • Sorry, my mistake.
      Try this:

      1. Home -> Label Management -> Smat Label
      2. Create a new *Software* Smart Label
      3. In the "Choose label" box, set a name for your Smart Label (ie: Solstice)
      4. Save the Smart Label
      5. Go again in Home -> Label Management -> Smat Labels and click the name of your new Smart Label
      6. Click Edit SQL and paste your SQL

      Now you must wait that some devices complete their inventory, or force the inventory for some PCs, or wait tomorrow (the smart label mechanism updates its datas at 4 AM)

      Now your *software* smart label must contain at least one row.

      Create your report and filter it only for the smart label you created

      But, frankly, I do not understand why you are doing this steps. What are you traying to obtain? A list of all PC with this software and its version?
      • Yea, there is probably an easier way to skin the cat. It was just scripts that I already had built. Getting the id from sc was necessary, because software metering needs to be enabled and that's the way to identify it. Solstice doesn't show up in the catalog, except for this weird back channel.
Please log in to comment

Share