How to Create a Report for Recently Installed Software on KBOX

Question

I am supposed to write a report to get the recently installed software on peoples computers.  I am new to writing reports in KBOX and KBOX in general and I am not real good at writing scripts to begin with.  If someone could please help me with this I would appreciate it or if someone can explain how to go about doing this/if it is even possible that would be great also. 
 
 I am looking to get the report with a date and time or at least the date, Name of the System, Software Title, Vendor Information, Description, etc.  Basically I need the general information about the software that was recently installed.
 
 Thanks! 
 ~blentz31

Answer

Also I need to have this report on a weekly basis. I know how to set the schedule to get it to run on a weekly basis for the report but I need the information for weekly changes.
 
 Thanks,
 ~Becky

Answer

blentz31,
 
 This is going to be tricky because of the way the KBOX handles assets in the database....
 
 Here's a basic report that will list all software changes within the last 7 days (rolling 7 days like you asked).
 SELECT A.NAME,
        AH.TIME,
        AH.DESCRIPTION
 FROM   ASSET_HISTORY AH,
        ASSET A
 WHERE  AH.ASSET_ID = A.ID
        AND AH.DESCRIPTION LIKE '%SOFTWARE%INVENTORY%'
        AND AH.TIME > DATE_SUB(NOW(),INTERVAL 7 DAY)
 ORDER BY NAME
 Does anybody else have something they use?

Answer

Here's what I use:

SELECT CONCAT(S.DISPLAY_NAME, ' ', S.DISPLAY_VERSION) AS 'Software',
 A.NAME as 'Computer', AH.TIME as 'Installed'
 FROM ASSET_HISTORY AH, ASSET A, ASSET_TYPE AST, SOFTWARE S
 WHERE AST.ASSET_CATEGORY = "Computer"
 and A.ID = AH.ASSET_ID
 and AH.DESCRIPTION LIKE CONCAT('%%Found software item ', S.DISPLAY_NAME,
 ' ', S.DISPLAY_VERSION, '%%')
 and DATE(AH.TIME) > DATE(DATE_SUB(NOW(), INTERVAL 2 DAY))
 ORDER BY Installed, Computer

Almost verbatim what David said, so it's gotta be darned close to correct between us :)
 "INTERVAL 2 DAY" you'd change to 7, or 20, or whatever you wanted your target to be. I set this up as a daily report because it was too much to sift through on a weekly basis. 
  
 Edit- Side note, I used this as a custom ticket rule and had emails sent that way, instead of reporting. Not because it's better, but because that's how reports were scheduled back in the stoneage when I wrote this.

Answer

Thank You! I will give this a try tomorrow and see which one of these works out best.  I am thankful for getting help on this as I am new to SQL and Kbox.  If anyone else has any other input please let me know!
 
 Thanks again!
 ~Becky

Answer

Becky- I overlooked your question on scheduling the reports; next to every report on the list view you'll see a red alarm clock- anywere you see that on K1 it's a scheduling option. For this report I'd likely use the option for only sending the report when results exist.

Answer

cblake 
 
 I'm trying to take your code and run it in MSSQL for different reasons. I can't seem to get it to work even though I've changed the code over to MSSQL. 
 
 Here is the MSSQL code if have wrote:

SELECT (S.DISPLAY_NAME+' '+ S.DISPLAY_VERSION) AS 'Software',
         M.NAME as 'Computer', 
         AH.TIME as 'Installed'
 FROM KBOX...ASSET_HISTORY AH, KBOX...ASSET A, KBOX...ASSET_TYPE AST, KBOX...SOFTWARE S, KBOX...MACHINE M
 WHERE AST.ASSET_CATEGORY = 'Computer'
   and A.ID = AH.ASSET_ID
   and AH.DESCRIPTION LIKE ('%%Found software item '+S.DISPLAY_NAME+' '+S.DISPLAY_VERSION+'%%')
   --and DATE(AH.TIME) > DATE(DATE_SUB(NOW(), INTERVAL 2 DAY))
   and DATEDIFF(DD,AH.TIME,GETDATE())>2 
   AND M.ID = A.MAPPED_ID
   AND M.OS_NAME LIKE '%SERVER%'
 ORDER BY Software, Computer

Do you or anyone else have any suggestions on my question? 
 
 Thanks
 Dave

Answer

cblake
 
 Thank you for the code.
 
 If their a string I can add to it so it does not display Microsoft Security Updates that have been recently installed?  The comon name appears in the report as:  "Security Update for Windows...."
 
 Also, the report shows software on a few machine being installed in 1 min intervals.  Here is an example?
 
 330	Winamp 5.541	azurew001	          03/10/2011 21:39:14		
 331	Winamp 5.541	azurew001	          03/10/2011 22:39:23		
 332	Winamp 5.541	azurew001	          03/10/2011 23:39:43

Thank You

Answer

azd_dmt,
 
 To Exclude these from chis' report at the following line to the where statement.
 AND S.DISPLAY_NAME NOT LIKE '%UPDATE%WINDOWS%'

Answer

What do I need to add to filter the results by operating system.  I particularly want to exclude Linux, so we will only see our Windows hosts.

How to Create a Report for Recently Installed Software on KBOX

Answers (9)

Posted by:

View more:

Related Questions

Related Links

Related Posts

ITNinja

How to Create a Report for Recently Installed Software on KBOX

Answers (9)

Posted by:

Don't be a Stranger!

View more:

Related Questions

Related Links

Related Posts

Share