Systems Management

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.

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

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

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.

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.

 

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.

Hey, this is a few years late but hopefully this will help someone out in the future.

Spent quite awhile trying to get this working today. Was finally able to get something working with perfect consistency using some modified bits from the SQL GillySpy supplied.

The STRCMP function is perfect for this kind of job. It will return the greatest value depending on the position of the variables.

In this example if S.Display_Version < "78.0.3904.87" the function returns -1.  > Would return 1 while = would be 0. I didn't think it would work for version numbers but it actually does without any issues.

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 '%Google Chrome%'
and ((Select STRCMP( S.DISPLAY_VERSION, "78.0.3904.87") as test) = -1)

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 :/

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")

ok that one's easy. Should be MACHINE.ID. i edited the post. let me know if anything else.

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.

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
  

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

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

Back to class then...

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

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).

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

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.

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!

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

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

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.

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')) ))