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
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)
Please log in to answer
Posted by:
serkowski
13 years ago
I changed it to only list "Found software item", since I'm looking for new software installed in the last 30 days.
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
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?
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 DESCBut I would like to improve the report to be more readable, since after a big software update on a computer, I get something like:
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') Softwarebut 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
13 years ago
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.