Summary:  Instructions on how to create a machine Smart label by software version.

How To:

Create a Machine Smart label by Software Version

1.  First use Inventory Software to narrow down your search using Display Name (title) and Display version  , using the syntax that gives you the best result  IE contains , does not contains , lesser than greater than , equals does not equals etc ….

2.  Once you find the correct Software Title plus Version that you want , write down how you found it .

3.  Next go to Inventory >Computers and create a New  smart label

4.  Make your criteria the same , only there is no display version so for now were going to substitute that with software publisher when you test label it should not find any machines and this is fine .  

5.  Name and Create your smart label

6.  Now go to Home > Labels > Smart Labels and find your newly created smart label  

7.  Looking at the SQL Query , there will be 1 instance of  SOFTWARE.PUBLISHER  replace it SOFTWARE.DISPLAY_VERSION  and save the smart label.  Query listed below. 

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 'Microsoft Office%')) ) 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.PUBLISHER = '14.0.4763.1000')) ))

 

 

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 'Microsoft Office%')) ) 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  = '14.0.4763.1000')) )) 

8.  As machines start checking in they should be added to the label based on the search criteria you used before.