/build/static/layout/Breadcrumb_cap_w.png

Software Changes History

I have a SQL report that list out software changes only for the last 30 days. Feel free to improve the report further.



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 30 DAY)
and ASSET_HISTORY.DESCRIPTION not like '%KB%'
and ASSET_HISTORY.DESCRIPTION not like '%reboot%'
and ASSET_HISTORY.DESCRIPTION not like '%mac%'
and ASSET_HISTORY.DESCRIPTION not like '%user%'
and ASSET_HISTORY.DESCRIPTION not like '%ip changed%'
and ASSET_HISTORY.DESCRIPTION not like '%video controller%'
and ASSET_HISTORY.DESCRIPTION not like '%domain changed%'
and ASSET_HISTORY.DESCRIPTION not like '%ram total changed%'
and ASSET_HISTORY.DESCRIPTION not like '%registry max size%'
and ASSET_HISTORY.DESCRIPTION not like '%name%')
and
(ASSET_HISTORY.DESCRIPTION like 'Found%'
or ASSET_HISTORY.DESCRIPTION like '%remove%'
or ASSET_HISTORY.DESCRIPTION like '%change%')
order by TIME DESC

0 Comments   [ + ] Show comments

Answers (2)

Posted by: serkowski 12 years ago
Senior Yellow Belt
0
I changed it to only list "Found software item", since I'm looking for new software installed in the last 30 days.

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 30 DAY)
and ASSET_HISTORY.DESCRIPTION like 'Found software item%')
order by TIME DESC

But I would like to improve the report to be more readable, since after a big software update on a computer, I get something like:

Found software item Microsoft Visual C++ 2005 Redistributable 8.0.61001 in inventory. Found software item Security Update for 2007 Microsoft Office System (KB2553089) in inventory. Found software item Security Update for 2007 Microsoft Office System (KB2584063) in inventory. Found software item Update for Outlook 2007 Junk Email Filter (KB2553110) in inventory. Found software item Security Update for 2007 Microsoft Office System (KB2553074) in inventory. Found software item Security Update for 2007 Microsoft Office System (KB2553090) in inventory. Found software item Security Update for Microsoft Office Excel 2007 (KB2553073) in inventory. Found software item Update for Microsoft Office Outlook 2007 (KB2583910) in inventory. Found software item Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729.6161 9.0.30729.6161 in inventory. Found software item McAfee DLP Agent 9.1.6.4 in inventory. Found software item Microsoft Visual C++ 2008 Redistributable - x64 9.0.30729.6161 9.0.30729.6161 in inventory. Found software item Microsoft Visual C++ 2005 Redistributable (x64) 8.0.59192 in inventory. Found software item Microsoft Visual C++ 2005 Redistributable (x64) 8.0.61000 in inventory. Found software item Security Update for Microsoft Windows (KB2570947) in inventory. Found software item Update for Microsoft Windows (KB2616676) in inventory.

Which is a bit hard to parse. There is actually a newline character between the items, and if looking at computer inventory -> History, it's properly formatted. I did try changing that first line to
select ASSET_HISTORY.TIME, ASSET.NAME, replace(ASSET_HISTORY.DESCRIPTION,'\n','somethingelse') Software
but no matter what I put in for 'somethingelse', it was never able to add a line break. Best I could do was insert a bunch of white space

Once that's done, I would really like to produce a list based on computer label(s) to isolate particular departments and operating systems.

Another useful thing would be to parse that history line to generate date/host/software triplets, then produce a report based on software attributes. For example, discard the Microsoft patches.

Any takers?
Posted by: serkowski 12 years ago
Senior Yellow Belt
0
Found the answer in a different thread, but still looking for a little more help with filtering the output.
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ