Report - When a Particular Software Installed or Removed
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,
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
Answers (3)
Please log in to answer
Posted by:
rmeyer
12 years ago
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
--------
--------
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
--------
Posted by:
afzal
12 years ago
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.
'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.
Posted by:
GillySpy
12 years ago
What about this:
I just used some random software items so please sub in appropriately.
features:
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
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.