Need some help with a Smart Label Query
I have created a smart label query that checks to see if the Asset history description contains an entry for Microsoft office within the last hour and 30 minutes from the current time on the kbox. This will be used to automatically place computers into a patching schedule. I have tried making 2 different variations of this query and testing it in FlySpeed SQL Query, the query seems to work perfectly. Now the problem I'm experiencing is that computers are being added to this label that shouldn't be. After I notice a machine is in the label I can do a force inventory update and the label removes itself. Below are the 2 different queries I have tried.
Select
*,
Unix_Timestamp(Now()) - Unix_Timestamp(MACHINE.LAST_SYNC) As LAST_SYNC_TIME,
Unix_Timestamp(MACHINE.LAST_SYNC) As LAST_SYNC_SECONDS
From
MACHINE 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
1 In (Select
1
From
ASSET_HISTORY Inner Join
ASSET On ASSET_HISTORY.ASSET_ID = ASSET.ID Inner Join
MACHINE On ASSET.MAPPED_ID = MACHINE.ID
Where
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Professional Plus 2007 12.0.6425.1000 in inventory.%' And
Date(ASSET_HISTORY.TIME) = CurDate() And
SubTime(CurTime(), '01:30:00') < Time(ASSET_HISTORY.TIME)) Or
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Standard 2007 12.0.6425.1000 in inventory.%' And
Date(ASSET_HISTORY.TIME) = CurDate() And
SubTime(CurTime(), '01:30:00') < Time(ASSET_HISTORY.TIME)) Or
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Small Business 2007 12.0.6425.1000 in inventory.%' And
Date(ASSET_HISTORY.TIME) = CurDate() And
SubTime(CurTime(), '01:30:00') < Time(ASSET_HISTORY.TIME)))
Select
ASSET_HISTORY.DESCRIPTION,
Date(ASSET_HISTORY.TIME),
Time(ASSET_HISTORY.TIME),
MACHINE.NAME
From
ASSET_HISTORY Inner Join
ASSET On ASSET_HISTORY.ASSET_ID = ASSET.ID Inner Join
MACHINE On ASSET.MAPPED_ID = MACHINE.ID
Where
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Professional Plus 2007 12.0.6425.1000 in inventory.%' And
Date(ASSET_HISTORY.TIME) = CurDate() And
SubTime(CurTime(), '01:30:00') < Time(ASSET_HISTORY.TIME)) Or
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Standard 2007 12.0.6425.1000 in inventory.%' And
Date(ASSET_HISTORY.TIME) = CurDate() And
SubTime(CurTime(), '01:30:00') < Time(ASSET_HISTORY.TIME)) Or
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Small Business 2007 12.0.6425.1000 in inventory.%' And
Date(ASSET_HISTORY.TIME) = CurDate() And
SubTime(CurTime(), '01:30:00') < Time(ASSET_HISTORY.TIME))
Any help with this would be appreciated. Thank you.
Select
*,
Unix_Timestamp(Now()) - Unix_Timestamp(MACHINE.LAST_SYNC) As LAST_SYNC_TIME,
Unix_Timestamp(MACHINE.LAST_SYNC) As LAST_SYNC_SECONDS
From
MACHINE 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
1 In (Select
1
From
ASSET_HISTORY Inner Join
ASSET On ASSET_HISTORY.ASSET_ID = ASSET.ID Inner Join
MACHINE On ASSET.MAPPED_ID = MACHINE.ID
Where
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Professional Plus 2007 12.0.6425.1000 in inventory.%' And
Date(ASSET_HISTORY.TIME) = CurDate() And
SubTime(CurTime(), '01:30:00') < Time(ASSET_HISTORY.TIME)) Or
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Standard 2007 12.0.6425.1000 in inventory.%' And
Date(ASSET_HISTORY.TIME) = CurDate() And
SubTime(CurTime(), '01:30:00') < Time(ASSET_HISTORY.TIME)) Or
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Small Business 2007 12.0.6425.1000 in inventory.%' And
Date(ASSET_HISTORY.TIME) = CurDate() And
SubTime(CurTime(), '01:30:00') < Time(ASSET_HISTORY.TIME)))
Select
ASSET_HISTORY.DESCRIPTION,
Date(ASSET_HISTORY.TIME),
Time(ASSET_HISTORY.TIME),
MACHINE.NAME
From
ASSET_HISTORY Inner Join
ASSET On ASSET_HISTORY.ASSET_ID = ASSET.ID Inner Join
MACHINE On ASSET.MAPPED_ID = MACHINE.ID
Where
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Professional Plus 2007 12.0.6425.1000 in inventory.%' And
Date(ASSET_HISTORY.TIME) = CurDate() And
SubTime(CurTime(), '01:30:00') < Time(ASSET_HISTORY.TIME)) Or
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Standard 2007 12.0.6425.1000 in inventory.%' And
Date(ASSET_HISTORY.TIME) = CurDate() And
SubTime(CurTime(), '01:30:00') < Time(ASSET_HISTORY.TIME)) Or
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Small Business 2007 12.0.6425.1000 in inventory.%' And
Date(ASSET_HISTORY.TIME) = CurDate() And
SubTime(CurTime(), '01:30:00') < Time(ASSET_HISTORY.TIME))
Any help with this would be appreciated. Thank you.
0 Comments
[ + ] Show comments
Answers (10)
Please log in to answer
Posted by:
steelc
13 years ago
Is there a reason that you're looking in the Asset History instead of the installed programs?
Posted by:
darkhawktman
13 years ago
It's the only place that I know of that has a time stamp for when the kbox detected that the office software has been installed. I need to only grab computers that have installed the software within the last hour and a half. If you know of a better way I'm all ears.
Posted by:
GillySpy
13 years ago
CurTime() only contains the time so use NOW() as that is a full timestamp as ASSET_HISTORY.TIME is.
Something like
What is the relevance of a piece of software being on the machine for 90 minutes? Outside that window and you don't want to patch it?
Something like
DATE_SUB(NOW(), INTERVAL 90 MINUTE) < ASSET_HISTORY.TIMEWhat is the relevance of a piece of software being on the machine for 90 minutes? Outside that window and you don't want to patch it?
Posted by:
darkhawktman
13 years ago
Basically I want to have a Patching schedule for newly built computers. So I only want to catch the newly built computers and not all computers that have office installed. I will try your suggestion and see how it works. Thanks for the info.
Posted by:
GillySpy
13 years ago
Would a newly built computer have any other indicator like the creation time of the machine record? If so that time is stored in MACHINE.CREATED .
Posted by:
darkhawktman
13 years ago
Creation time wouldn't work due to the fact that all of our builds are scripted installs. So if I based off of the creation time it would be patching before all of the other software has been installed and would be patched till much later.
Posted by:
darkhawktman
13 years ago
Well the latest label query that I tried is still giving incorrect results as in machines that shouldn't be in this label. Here is my latest query.
Select
*,
Unix_Timestamp(Now()) - Unix_Timestamp(MACHINE.LAST_SYNC) As LAST_SYNC_TIME,
Unix_Timestamp(MACHINE.LAST_SYNC) As LAST_SYNC_SECONDS
From
MACHINE 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
1 In (Select
1
From
ASSET_HISTORY Inner Join
ASSET On ASSET_HISTORY.ASSET_ID = ASSET.ID Inner Join
MACHINE On ASSET.MAPPED_ID = MACHINE.ID
Where
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Professional Plus 2007 12.0.6425.1000 in inventory.%' And
DATE_SUB(NOW(), INTERVAL 90 MINUTE) < ASSET_HISTORY.TIME) Or
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Standard 2007 12.0.6425.1000 in inventory.%' And
DATE_SUB(NOW(), INTERVAL 90 MINUTE) < ASSET_HISTORY.TIME) Or
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Small Business 2007 12.0.6425.1000 in inventory.%' And
DATE_SUB(NOW(), INTERVAL 90 MINUTE) < ASSET_HISTORY.TIME))
I might have to think about a different way to automate this.
Select
*,
Unix_Timestamp(Now()) - Unix_Timestamp(MACHINE.LAST_SYNC) As LAST_SYNC_TIME,
Unix_Timestamp(MACHINE.LAST_SYNC) As LAST_SYNC_SECONDS
From
MACHINE 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
1 In (Select
1
From
ASSET_HISTORY Inner Join
ASSET On ASSET_HISTORY.ASSET_ID = ASSET.ID Inner Join
MACHINE On ASSET.MAPPED_ID = MACHINE.ID
Where
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Professional Plus 2007 12.0.6425.1000 in inventory.%' And
DATE_SUB(NOW(), INTERVAL 90 MINUTE) < ASSET_HISTORY.TIME) Or
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Standard 2007 12.0.6425.1000 in inventory.%' And
DATE_SUB(NOW(), INTERVAL 90 MINUTE) < ASSET_HISTORY.TIME) Or
(ASSET_HISTORY.DESCRIPTION Like
'%Found software item Microsoft Office Small Business 2007 12.0.6425.1000 in inventory.%' And
DATE_SUB(NOW(), INTERVAL 90 MINUTE) < ASSET_HISTORY.TIME))
I might have to think about a different way to automate this.
Posted by:
ncsutmf
13 years ago
With Office 2007, 2010, and maybe older versions, adding a patch to the initial install can be done by adding the appropriate MSP files to the Updates folder in the install source. Then when it installs it won't need to be patched, but you have to keep up with patches in the install source.
Also, for Office 2010 at least, SP1 changed the installed version number from the initial release, and since SP1 included all of the previous patches, anything pre-SP1 can be left out.
Also, for Office 2010 at least, SP1 changed the installed version number from the initial release, and since SP1 included all of the previous patches, anything pre-SP1 can be left out.
Posted by:
darkhawktman
13 years ago
Unfortunately in my build process keeping the office install up to date doesn't help the situation. What happens is that I install Windows XP SP3 with a scripted install and runs through a bunch of scripts and software installs. After we install all of the software including Office we then have the kbox patch it so that all of the Microsoft patches and 3rd party application patches get installed. So I'm just trying to automate when the new machine gets patched. In this case, after Office has been installed.
Posted by:
GillySpy
13 years ago
So you want the PC to :
At the end of step 3 there is going to be some evidence it was completed -- is it a software item showingup in inventory combined with date? If so what item?
e.g.
Or (some like this way because it's easier to write its antithesis):
- be imaged / re-imaged
- checkin to kbox
- install some stuff from kbox (MIs, scripts)
- do patching (that updates some stuff from 2 and 3)
At the end of step 3 there is going to be some evidence it was completed -- is it a software item showingup in inventory combined with date? If so what item?
e.g.
select MACHINE.ID
from MACHINE
JOIN MACHINE_SOFTWARE_JT MS ON MS.MACHINE_ID=MACHINE.ID
JOIN SOFTWARE S ON S.ID=MS.SOFTWARE_ID
WHERE
DATE(MACHINE.CREATED) > DATE_SUB(CURDATE(), INTERVAL X DAY)
and S.DISPLAY_NAME LIKE '%my specific software item%'
and S.DISPLAY_VERSION = '1.0.1234'Or (some like this way because it's easier to write its antithesis):
/* if specific software is INSTALLED then label it */ select MACHINE.ID, MACHINE.NAME
from MACHINE JOIN /**/
(select * from MACHINE_SOFTWARE_JT JOIN SOFTWARE S ON
S.ID=SOFTWARE_ID and
DISPLAY_NAME LIKE '%adobe flash player%' and
DISPLAY_VERSION LIKE '%10.1.85.3%') MS ON MACHINE.ID=MS.MACHINE_ID
WHERE
DATE(MACHINE.CREATED) > DATE_SUB(CURDATE(), INTERVAL X DAY) and
MS.ID IS NOT NULL /* software is not missing*/
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.