/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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
0 Comments   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

Answers

0
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
Answered 03/07/2011 by: blentz31
Purple Belt

Please log in to comment
0
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?
Answered 03/07/2011 by: dchristian
Red Belt

Please log in to comment
0
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.
Answered 03/07/2011 by: cblake
Red Belt

Please log in to comment
0
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
Answered 03/08/2011 by: blentz31
Purple Belt

Please log in to comment
0
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.
Answered 03/08/2011 by: cblake
Red Belt

Please log in to comment
0
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


Answered 03/09/2011 by: diverdaveman1
Purple Belt

Please log in to comment
0
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
Answered 03/10/2011 by: azd_dmt
Senior Yellow Belt

Please log in to comment
0
azd_dmt,

To Exclude these from chis' report at the following line to the where statement.
AND S.DISPLAY_NAME NOT LIKE '%UPDATE%WINDOWS%'
Answered 03/11/2011 by: dchristian
Red Belt

Please log in to comment
0
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.
Answered 11/14/2011 by: serkowski
Senior Yellow Belt

Please log in to comment