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

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
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?
Answered 10/06/2011 by: serkowski
Senior Yellow Belt

Please log in to comment
0
Found the answer in a different thread, but still looking for a little more help with filtering the output.
Answered 11/14/2011 by: serkowski
Senior Yellow Belt

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