I'm creating a Computer smart label to search for particular software in this case Mozilla Firefox with a Software Version less than 9.0.1 - I can then assign the smart Label to a Managed Install and upgrade said software to the latest version. Problem is it DOES NOT working and I am only using the "Test Smart Label" to check results.

Using the smart label wizard I have set the following options:
Software Titles contains Mozilla Firefox AND Software Version number < 9.0.1 [I get ALL the computers that contain Mozilla Firefox]
Software Titles contains Mozilla Firefox AND Software Version number > 9.0.1 [I get ALL the computers that contain Mozilla Firefox]

To test I was not in the wrong I tested the following:
Software Titles contains Mozilla Firefox AND Software Version number = 9.0.1 [this works correctly]
and also tested
Software Titles contains Mozilla Firefox AND Software Version number != 9.0.1 [this works correctly]

Looks like a bug as the Less Than and Greater Than options DOES NOT work in creating a Computer Smart Label.
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
It is because "Software Version" is not Mozilla Firefox version, it is all software.

It would be nice with some nice logic that could automatically do that

*edit*

and it can be done via SQL query, but I just don't know how to :/
Answered 01/18/2012 by: rmeyer
Second Degree Blue Belt

  • There is a blog post on doing this via the GUI with minimal MySQL editing.
    http://www.itninja.com/blog/view/how-to-build-a-machine-label-based-on-software-title-and-version
Please log in to comment
3
To test I was not in the wrong I tested the following:
Software Titles contains Mozilla Firefox AND Software Version number = 9.0.1 [this works correctly]
and also tested
Software Titles contains Mozilla Firefox AND Software Version number != 9.0.1 [this works correctly]

Actually this does not work correctly. What this says in english is:
"give me ALL the machines that have any software that is called similar to mozilla firefox and give me all the machines that also have any software (whether it's the same software or not) that is at version 9.0.1"

So if you have office 9.0.1 and firefox 8 you would get a hit which is what you probably do not want.

The > and < do not work because version number are actually version strings. See this post (http://itninja.com/question/silent-uninstall-oracle-8-cilent2378&mpage=1&key=DISPLAY_VERSION&#72401). If you are able it's usually best to write a regex for these. If you need help please respond.

The SQL for all 4 (in same order ) is:
select MACHINE.ID from MACHINE
JOIN MACHINE_SOFTWARE_JT MS ON MACHINE_ID=MACHINE.ID
JOIN SOFTWARE S ON S.ID=MS.SOFTWARE_ID
WHERE
S.DISPLAY_NAME like '%mozilla firefox%'
and S.DISPLAY_VERSION rlike '^([1-9][0-9]+.*|9[[...]]([1-9].*|0[[...]](2|[1-9][0-9]+)))$' -- greater than 9.0.1

select MACHINE.ID from MACHINE
JOIN MACHINE_SOFTWARE_JT MS ON MACHINE_ID=MACHINE.ID
JOIN SOFTWARE S ON S.ID=MS.SOFTWARE_ID
WHERE
S.DISPLAY_NAME like '%mozilla firefox%'
and S.DISPLAY_VERSION rlike '^(8[...].*|9[[...]]0[[...]]0)$' -- less than 9.0.1


select MACHINE.ID from MACHINE
JOIN MACHINE_SOFTWARE_JT MS ON MACHINE_ID=MACHINE.ID
JOIN SOFTWARE S ON S.ID=MS.SOFTWARE_ID
WHERE
S.DISPLAY_NAME like '%mozilla firefox%'
and S.DISPLAY_VERSION='9.0.1'


select MACHINE.ID from MACHINE
JOIN MACHINE_SOFTWARE_JT MS ON MACHINE_ID=MACHINE.ID
JOIN SOFTWARE S ON S.ID=MS.SOFTWARE_ID
WHERE
S.DISPLAY_NAME like '%mozilla firefox%'
and S.DISPLAY_VERSION!='9.0.1' -- not equal to


Note: these are not tested so let me know if they do not work
Answered 01/18/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
I keep getting same error for all 4:

mysql error: [1052: Column 'ID' in field list is ambiguous] in EXECUTE(
"select ID from MACHINE
JOIN MACHINE_SOFTWARE_JT MS ON MACHINE_ID=MACHINE.ID
JOIN SOFTWARE S ON S.ID=MS.SOFTWARE_ID
WHERE
S.DISPLAY_NAME like '%mozilla firefox%'
and S.DISPLAY_VERSION rlike '^(8[...].*|9[[...]]0[[...]]0)$' -- less than 9.0.1")
Answered 01/18/2012 by: tcunningham
Senior Yellow Belt

Please log in to comment
0
ok that one's easy. Should be MACHINE.ID. i edited the post. let me know if anything else.
Answered 01/18/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
The wizard puts together some odd SQL when you try to do this. It builds a query with this WHERE clause (using Java 6, older than update 27, as an example):

where ((( (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and SOFTWARE.DISPLAY_NAME like '%Java(TM) 6%')) ) AND (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and SOFTWARE.DISPLAY_VERSION < '6.0.270')) ))

Basically, it returns all machines where Java 6 is installed and all machines that have any software with a version less than 6.0.270 without or without Java 6 installed. (If you do version = 6.0.270 in my environment, the results look "right", as Java is the only software title I have with that exact version.) I redid the WHERE clause for the label to this:

where (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and SOFTWARE.DISPLAY_NAME like 'Java(TM) 6%' and SOFTWARE.DISPLAY_VERSION < '6.0.270') )

Putting the title and version together returned what I was looking for: machines that have Java older than 6 update 27.


Edit: Gerald scooped me while I was typing. Using < or > has worked for me so far, YMMV.
Answered 01/18/2012 by: sramsey
Yellow Belt

Please log in to comment
0
sramsey, your query will not work in the long run. You are "getting lucky". < 6.0.270 is an character sort on string positions since version are not numbers they are strings. Taking out the periods won't work either. Cannot have a number with variable length silos.

In principle combining them on the same software entry is what I did, but I also:
  • made the query more efficient (better performing for big deployments) and
  • easier to read for the layperson and
  • easier to modify by hand
  • and work correctly for > and < by using a regex
Answered 01/18/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Nice work I like it works a dream - I'm gonna to try amend SQL query to add in (x86 Fr) and a seperates ones for (x86 de), (x86 en-US) and (x86 en-GB)

If I get stuck will it be ok to ask for your help again
Answered 01/18/2012 by: tcunningham
Senior Yellow Belt

Please log in to comment
4
ORIGINAL: tcunningham

Nice work I like it works a dream - I'm gonna to try amend SQL query to add in (x86 Fr) and a seperates ones for (x86 de), (x86 en-US) and (x86 en-GB)

If I get stuck will it be ok to ask for your help again

If you keep putting +1 (or +2) beside my answers you can ask me anything k1000 related including break/fix, how-to, concepts, enhancements, etc
Answered 01/18/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Here is my French Mozilla Firefox does it look about right as not seeming to work

select MACHINE.ID from MACHINE
JOIN MACHINE_SOFTWARE_JT MS ON MACHINE_ID=MACHINE.ID
JOIN SOFTWARE S ON S.ID=MS.SOFTWARE_ID
WHERE
S.DISPLAY_NAME like '%mozilla firefox%'
and S.DISPLAY_NAME like '%(x86 fr)%'
and S.DISPLAY_VERSION rlike '^(8[...].*|9[[...]]0[[...]]0)$' -- less than 9.0.1
Answered 01/18/2012 by: tcunningham
Senior Yellow Belt

Please log in to comment
0
Back to class then...
Answered 01/18/2012 by: sramsey
Yellow Belt

Please log in to comment
0
Mmmmmmmm I spoke to soon, Gillyspy.

Currently using....

select MACHINE.ID from MACHINE
JOIN MACHINE_SOFTWARE_JT MS ON MACHINE_ID=MACHINE.ID
JOIN SOFTWARE S ON S.ID=MS.SOFTWARE_ID
WHERE
S.DISPLAY_NAME like '%mozilla firefox%'
and S.DISPLAY_VERSION rlike '^(8[...].*|9[[...]]0[[...]]0)$' -- less than 9.0.1

....as a smart label but after all the machines reported in it has placed 193 computers into the smart label and have chacked multiple machines within that label and a majority have Firefox 9.0.1 already installed and should not be appearing in the label - HELP as would really like to learn how to make these computer labels.


------------------------------------------------------------------------------------------
The only work around I have at the moment (this is long winded so bear with me people) is:

1. Inventory > Software to create a Custom Software Inventory Rule like below:

"Firefox version less than 9.0.1 - English":

FileExists(C:\Program Files (x86)\Mozilla Firefox\firefox.exe)
ProductVersionLessThan(C:\Program Files (x86)\Mozilla Firefox\firefox.exe,9.0.1)
RegistryKeyExists(HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\MUI\UILanguages\en-US)

Replacing the en-US at end of rule with de-DE or fr-FR would result in looking for German or French versions etc. Unfortunately within the properties of the firefox.exe Language detail is set to Language Neutral.

This now creates an entry on the computers software inverntory entry in Dell Kace to see it has "Firefox Less then 9.0.1 - (English)".

2. I then create a Computer Smart Label with the following to break up the US and GB languages as we have a naming convention based off geographic location - thank god.

Firefox Less Than 9.0.1 (London):

select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (((( (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and SOFTWARE.DISPLAY_NAME like '%Firefox Less Than 9.0.1 - English%')) ) AND MACHINE.NAME like 'wslon%'))

For the US I just replace SOFTWARE.DISPLAY_NAME like '%Firefox Less Than 9.0.1 - English%' AND MACHINE.NAME like 'wsus%' and do the same for German and French based off their Custom inventory rules created in step 1 and their naming convention.

3. I then create the Managed Install in this case for London: "Mozilla Firefox 9.0.1 (x86 en-GB) (9.0.1)" and assign the computer label created in Step 2 "Firefox Less Than 9.0.1 (London)" to the Limit Deployment to Labels field.

Hey presto it works.......well seems to in my case.
------------------------------------------------------------------------------------------

But as I say I would really like it to work with your way Gillyspy as would make my life easier
Answered 01/19/2012 by: tcunningham
Senior Yellow Belt

Please log in to comment
0
I thought that these queries wouldn't work because the display version field isn't actually a number, i.e. MySQL can't compare 8.0.3 to 9.0.1 because they are actually text values (numbers don't have multiple decimal points).
Answered 01/19/2012 by: steelc
Senior Yellow Belt

Please log in to comment
0
But I'm letting the Custom Inventory Rule do all the work so "Firefox Less Than 9.0.1 - English" appears as installed software on the computers inventory....then creating a smart label based off that software inventory title
Answered 01/19/2012 by: tcunningham
Senior Yellow Belt

Please log in to comment
0
a smart label but after all the machines reported in it has placed 193 computers into the smart label and have checked multiple machines within that label and a majority have Firefox 9.0.1 already installed and should not be appearing in the label - HELP as would really like to learn how to make these computer labels.
This looks really good to me still:

S.DISPLAY_NAME like '%mozilla firefox%'
and S.DISPLAY_VERSION rlike '^(8[...].*|9[[...]]0[[...]]0)$' -- less than 9.0.1

Are you certain? Did the machines run an MI after this to update themselves?

To be certain run this query as a report:

select MACHINE.ID , S.ID, S.DISPLAY_NAME, S.DISPLAY_VERSION from MACHINE
JOIN MACHINE_SOFTWARE_JT MS ON MACHINE_ID=MACHINE.ID
JOIN SOFTWARE S ON S.ID=MS.SOFTWARE_ID
WHERE
S.DISPLAY_NAME like '%mozilla firefox%'
and S.DISPLAY_VERSION rlike '^(8[...].*|9[[...]]0[[...]]0)$' -- less than 9.0.1


If:
  • your machine has the label but doesn't show up in that query then something changed firefox e.g. an MI was run. checking them in again should remove the label
  • your machine is in that list but the software item listed is something else very similar ("firefox spoofer"?) then your DISPLAY_NAME criteria is too inclusive
Also didn't see your updates as I subscribe to replies on my posts (have to click reply on the post not thread for that to work) and new threads. Sorry about that.
Answered 01/19/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Also didn't see your updates as I subscribe to replies on my posts (have to click reply on the post not thread for that to work) and new threads. Sorry about that.
as i say that i get a bunch of email. Yay!
Answered 01/19/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0

To be certain run this query as a report:
select MACHINE.ID , S.ID, S.DISPLAY_NAME, S.DISPLAY_VERSION from MACHINE
JOIN MACHINE_SOFTWARE_JT MS ON MACHINE_ID=MACHINE.ID
JOIN SOFTWARE S ON S.ID=MS.SOFTWARE_ID
WHERE
S.DISPLAY_NAME like '%mozilla firefox%'
and S.DISPLAY_VERSION rlike '^(8[...].*|9[[...]]0[[...]]0)$' -- less than 9.0.1


As a report the above works how I want it to: I have re-edited my smart label to have exactly the same as above and now just have to sit and wait for results as machines check in
Answered 01/24/2012 by: tcunningham
Senior Yellow Belt

Please log in to comment
0
mmmmm still no joy.........i made my SQL query as follows

select MACHINE.ID , S.ID, S.DISPLAY_NAME, S.DISPLAY_VERSION from MACHINE
JOIN MACHINE_SOFTWARE_JT MS ON MACHINE_ID=MACHINE.ID
JOIN SOFTWARE S ON S.ID=MS.SOFTWARE_ID
WHERE
S.DISPLAY_NAME like 'mozilla firefox%'
and S.DISPLAY_NAME like '%(x86 de)'
and S.DISPLAY_VERSION rlike '^(8[...].*|9[[...]]0[[...]]0)$' -- less than 9.0.1

........and still gettign randoms with firefox, wrong language, some with no firefox installed at all.

Maybe if you got time one day we can organize a WebEx and you can take a look. It's really frustrating me now
Answered 01/26/2012 by: tcunningham
Senior Yellow Belt

Please log in to comment
0
Most management apps like KACE, SCCM don't treat versions as numbers, therefore arguements like greater than, less than don't work. Go with the 'is different' or find something else to differentiate versions.
Answered 01/31/2012 by: rfabian
Senior Yellow Belt

  • .
  • Is it possible to do this with a kace script and for example look for a registry key?
Please log in to comment
2
Use REGEX. If you are unfamiliar with it, do some Google searching - there is a ton of documentation/examples. As Rfabian explained, you can't use greater/less than for version numbers.
Answered 02/01/2012 by: airwolf
Tenth Degree Black Belt

Please log in to comment
4
I created a machine smart label to do this using REGEX for you. The drop down smart label builder wont be able to do this so I wrote it in MySQL.

select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where 1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and (SOFTWARE.DISPLAY_NAME like '%firefox%' and (SOFTWARE.DISPLAY_VERSION rlike '[1-8].[0-9].[1-9][0-9]' OR SOFTWARE.DISPLAY_VERSION rlike '[1-8].[0-9].[0-9]' OR SOFTWARE.DISPLAY_VERSION rlike '[1-8].[0-9]' OR SOFTWARE.DISPLAY_VERSION like '9.0.0')))

This should capture all versions of Firefox 1.0 through 9.0.0.
Answered 02/01/2012 by: awenzel@kace.com
Senior Purple Belt

  • updated query:select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
    UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
    from ORG1.MACHINE
    LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID
    LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
    where (1 in
    (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT
    where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
    and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
    and SOFTWARE.DISPLAY_NAME like '%firefox%'
    AND SOFTWARE.DISPLAY_NAME NOT LIKE '%extension%'
    AND SOFTWARE.DISPLAY_NAME NOT LIKE '%dell%'
    and (SOFTWARE.DISPLAY_VERSION rlike '^([1-8]\.[0-9])' OR SOFTWARE.DISPLAY_VERSION like '9.0')))
Please log in to comment
0
Sorry didn't see your reply cuz you responded to your own post.

I think it should be (missed the proper declaration of all whole numbers less than 8 and was only using 8).
^([0-8][...].*|9[[...]]0[[...]]0)$

Run it through reporting or even better run it with a tool like the mysql query browser and see what you get.
Answered 03/08/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0

I found that the following SQL query works as a Computer SMART Lable and allows for system to be found based on the Software Pulisher ( SOFTWARE.PUBLISHER variable) and the version of the software ( DISPLAY_VERSION variable). The following example is to find system that have Java ( Pulisher = oracal) that  is a version less then 7.0.320. You should be able to insert the information you are looking for into the two variable for the same effect. Please keep in mind that this Lable will not be fully applied until ALL of the system check in after it is applied.

select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
                       UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
                  from ORG1.MACHINE
                  LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
                 where ((  (1  in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and SOFTWARE.PUBLISHER like '%Oracle%' AND
SOFTWARE.DISPLAY_VERSION < '7.0.320')) ))

Answered 05/04/2012 by: stevech
Purple Belt

  • I have a similar issue but my software has multiple installs and the version numbers are identical for each piece and have the same publisher. So lets say that SoftwareA ver. 4.00.389 and SoftwareB ver. 4.02.1130 and ver. 4.00.389 exist . When I try to identify SoftwareA with version 4.00.389 I get pc's that have that version of SoftwareB installed. Really need way to tie the version number to the Software.
  • you will be able to group those two things in a 5.5 definition. Also you will be able to sort on version number properly. The problem today is you would get two subqueries and then it becomes and "= any" (i.e. OR)

    here is how do it now simply:
    select ID from MACHINE
    join MACHINE_SOFTWARE_JT MS on MS.MACHINE_ID
    join SOFTWARE S on S.ID=MS.SOFTWARE_ID
    where
    S.DISPLAY_VERSION like '%123.123%'
    and S.DISPLAY_NAME like '%mysoftware title%'
    and S.ID = 123 /* optional line that lets you get specific on something you found */

    otherwise, create a software label that represents your combination and use that:
    select ID from MACHINE
    join MACHINE_SOFTWARE_JT MS on MS.MACHINE_ID
    join SOFTWARE S on S.ID=MS.SOFTWARE_ID
    join SOFTWARE_LABEL_JT SL ON SL.SOFTWARE_ID=S.ID
    join LABEL L on L.ID=SL.LABEL_ID
    where
    L.NAME = 'softwarelabelyoucreated'
Please log in to comment
2

Hey Guys,

I came up with the following Query that will do what your after..

 

Select
*,
  Unix_Timestamp(Now()) - Unix_Timestamp(MACHINE.LAST_SYNC) As LAST_SYNC_TIME,
  Unix_Timestamp(MACHINE.LAST_SYNC) As LAST_SYNC_SECONDS,
    S.DISPLAY_VERSION,
  S.DISPLAY_NAME
From
  MACHINE Left Join
  MACHINE_SOFTWARE_JT MS On MS.MACHINE_ID = MACHINE.ID Join
  SOFTWARE S On S.ID = MS.SOFTWARE_ID Left Join
  KBSYS.KUID_ORGANIZATION On KBSYS.KUID_ORGANIZATION.KUID = MACHINE.KUID
  Left Join
  KBSYS.SMMP_CONNECTION On KBSYS.SMMP_CONNECTION.KUID = MACHINE.KUID And
    KBSYS.KUID_ORGANIZATION.ORGANIZATION_ID = 1
Where
  S.DISPLAY_VERSION < 12 And
  S.DISPLAY_NAME Like '%itunes%'

Let me know how you go, Just change the software version to your requirements and software display name.

 

Answered 07/23/2012 by: Troy_Grey
Senior Yellow Belt

  • Thanks Troy That worked a treat for me. I replaced the 12 with version 10.5 for example and it worked. Thanks JV
  • Troy! Thanks man... This is my new template for simple software version labels.
Please log in to comment
Answer this question or Comment on this question for clarity