Please find below SQL , to find the installation date/time of a particular software item.
This SQL is customized for Windows XP SP3, during drilling the Asset History logs i found that Windows XP (SP3) is reported in two different ways so i made two entries, you can remove the 2nd part if only one software item is required.
Further, I will appreciate if someone could provide the simplified version of this SQL.


SELECT NAME, ASSET_HISTORY.TIME,
Concat(
substr(DESCRIPTION,LOCATE('Found software item Windows XP Service Pack 3',DESCRIPTION),IF(LOCATE('Found software item Windows XP Service Pack 3',DESCRIPTION)<>0,45,0)),
substr(DESCRIPTION,LOCATE('Found software item Microsoft Windows XP Professional (Service Pack 3)',DESCRIPTION),IF(LOCATE('Found software item Microsoft Windows XP Professional (Service Pack 3)',DESCRIPTION)<>0,71,0))) as Description2
FROM ASSET_HISTORY
Join ASSET on (ASSET_HISTORY.ASSET_ID = ASSET.ID)
WHERE DATE_SUB(NOW(), INTERVAL 200 DAY) < DATE(ASSET_HISTORY.TIME)
and ( DESCRIPTION like '%Found software item Windows XP Service Pack 3%' OR DESCRIPTION like '%Found software item Microsoft Windows XP Professional (Service Pack 3)%' )
order by TIME DESC


1. Enter your desired Software name by replacing 'Found software item Windows XP Service Pack 3' at all locations.

2. Enter the length of the software item in this case it is ‘45’ and ‘71’



Thanks,
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
This is a tiny bit simpler, not much but everything helps I guess :)
--------
select ASSET_HISTORY.TIME, ASSET.NAME, ASSET_HISTORY.DESCRIPTION
from ASSET
left join ASSET_TYPE on ASSET.ASSET_TYPE_ID = ASSET_TYPE.ID
left join ASSET_HISTORY on ASSET.ID = ASSET_HISTORY.ASSET_ID
where
(ASSET_TYPE.ID = 5
and NOW() < DATE_ADD(ASSET_HISTORY.TIME, INTERVAL 90 DAY)
and ASSET_HISTORY.DESCRIPTION like 'Found software item Crystal Reports 2008 SP3%')
order by TIME DESC
--------
Answered 12/15/2011 by: rmeyer
Second Degree Blue Belt

Please log in to comment
0
Thanks Meyer, the only problem with this is it will fetch all other things along with the selected Item. I mean if user has changed many things between two inventory intervals then it will fetch all these with because all these information is stored as a single row of a table in SQL DB , E.g.
'Found software item Crystal Reports 2008 SP3'
'Found software item Windows XP (SP3)'

We have experienced this when we rebuild the PC or installed two or more software at once instance.
Answered 12/15/2011 by: afzal
Fourth Degree Green Belt

Please log in to comment
0
What about this:
SELECT NAME, ASSET_HISTORY.TIME,
group_Concat(
substr(DESCRIPTION,LOCATE(Concat(PREFIX,S),DESCRIPTION),IF(LOCATE(Concat(PREFIX,S),DESCRIPTION)<>0,LENGTH(PREFIX)+LENGTH(S),0))
separator ','
) as SHORT_DESCRIPTION,
DESCRIPTION FULL_DESCRIPTION
FROM ASSET_HISTORY
Join ASSET on (ASSET_HISTORY.ASSET_ID = ASSET.ID)
join (
select 'Security Update for Windows Internet Explorer 7 (KB2183461) for Microsoft Windows (KB2183461-IE7) 1' S,
'Found software item ' PREFIX,
' in inventory.' SUFFIX
UNION ALL
select 'Adobe Flash Player 10 Plugin 10.1.82.76' S,
'Found software item ' PREFIX,
' in inventory.' SUFFIX
UNION ALL
select 'Adobe Flash Player 10 Plugin 10.1.53.64' S,
'software item ' PREFIX,
' removed from inventory.' SUFFIX
) T ON 1=1
WHERE DATE_SUB(NOW(), INTERVAL 20000 DAY) < DATE(ASSET_HISTORY.TIME)
and DESCRIPTION rlike CONCAT('(^|\n)',PREFIX,S,SUFFIX,'($|\n)')
group by ASSET.NAME, ASSET_HISTORY.TIME
order by TIME DESC ;


I just used some random software items so please sub in appropriately.

features:
  • put your list of software items in the union.
  • no matter how big your list you are only adding items to the union
  • you can look for removals and other things by changing the prefix and suffix for that history entry. note that i added in an example of a removal
  • calculates the length for you
  • uses regex which is more precise (but takes longer). easy to sub back in Like if you prefer
Answered 12/18/2011 by: GillySpy
Seventh Degree Black Belt

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