Audit software installations r2

2nd November 2009 09:23
Print Friendly

With this basic report you will be able to monitor any new applications installed and registrered in Add/Remove programs during the last 7 days on a client computer. 

Thanks to Claus Codam for helping out

The SQL code

SELECT TOP (100) PERCENT
        dbo.v_R_System.Name0,
        dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0,
        dbo.v_GS_ADD_REMOVE_PROGRAMS.InstallDate0,
        DATEDIFF(Day, CONVERT(date,dbo.v_GS_ADD_REMOVE_PROGRAMS.InstallDate0), GETDATE()) AS [days since installed]

FROM
        dbo.v_R_System INNER JOIN
        dbo.v_GS_ADD_REMOVE_PROGRAMS ON
        dbo.v_R_System.ResourceID = dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID

WHERE
        (SELECT ISDATE(dbo.v_GS_ADD_REMOVE_PROGRAMS.InstallDate0)) = 1

GROUP BY
        dbo.v_R_System.Name0,
        dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0,
        dbo.v_GS_ADD_REMOVE_PROGRAMS.InstallDate0,
        DATEDIFF(Day, CONVERT(date,dbo.v_GS_ADD_REMOVE_PROGRAMS.InstallDate0), GETDATE())

HAVING
        (
        (CASE WHEN (ISDATE(dbo.v_GS_ADD_REMOVE_PROGRAMS.InstallDate0))=1 THEN
                (DATEDIFF(Day, CONVERT(date,dbo.v_GS_ADD_REMOVE_PROGRAMS.InstallDate0), GETDATE()))
        ELSE    500
        END) > 0 AND
        (CASE WHEN (ISDATE(dbo.v_GS_ADD_REMOVE_PROGRAMS.InstallDate0))=1 THEN
                (DATEDIFF(Day, CONVERT(date,dbo.v_GS_ADD_REMOVE_PROGRAMS.InstallDate0), GETDATE()))
        ELSE    500
        END) < 7
        )

The Report

image

Category: Config Mgr. Inventory and Reporting, Software distribution  |  Comment (RSS)

Comments (13):

  1. Amnon says:
    6th November 2009 at 20:51

    Great site, i am happy I found it..
    I am trying to figure out a way to find a if a specific service is NOT running, for example, if I have any windows 2003 machines that do not have SNMP installed?

    Reply
  2. Kent Agerlund Kent Agerlund says:
    8th November 2009 at 13:01

    Hi Amnon,
    For a solution to your question check out this post written by Sherry Kissinger – http://myitforum.com/cs2/blogs/skissinger/archive/2009/04/25/hardware-inventory-customization-only-specific-services.aspx

    Reply
  3. jeremy pearce says:
    1st December 2009 at 17:01

    I have tried using the audit software sql code but get these errors
    Server: Msg 242, Level 16, State 3, Line 1
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
    Server: Msg 241, Level 16, State 1, Line 1
    Conversion failed when converting datetime from character string.
    any ideas on how this can be solved

    Reply
  4. Kent Agerlund Kent Agerlund says:
    1st December 2009 at 17:26

    Hi,
    Send me a mail and I will return the report.mof file for you to import

    Reply
  5. jeremy pearce says:
    2nd December 2009 at 11:27

    Hi kent Did you get my mail?

    Reply
  6. Jason Scott says:
    4th December 2009 at 15:27

    I’ve tried this but getting the error:

    Conversion failed when converting datetime from character string

    Any tweaks needed if the server has a non-US time/date format?

    Reply
  7. Kent Agerlund Kent Agerlund says:
    5th December 2009 at 14:25

    Hi,
    the problem is the way data is stored in the db. I just found a few examples to illustrate it. I will try and find a work around.
    10/21/2009
    12/19/2008
    197011
    197011
    20030519
    20031204
    20031205

    Reply
  8. Sachin says:
    6th April 2010 at 00:37

    Hi Kent,

    I got same error as mentioned above as “Conversion failed when converting datetime from character string”

    Is there any way to fix this ?

    Regards,

    Reply
  9. Tharanga Weerakkody says:
    17th June 2010 at 08:02

    Hi Kent,

    I get the above error mentioned above is there a fix for it?
    Thanks

    Reply
  10. Kent Agerlund Kent Agerlund says:
    23rd June 2010 at 14:58

    The report has been updated and will eliminate the errors you have seen

    Reply
  11. TJ says:
    12th October 2011 at 17:07

    Could you modify the report to show just the count for software installed in the last 7 days. Example: Office 2007 – 34 installs

    Reply
  12. CS says:
    12th January 2012 at 12:58

    When I execute this query I am getting this error message

    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘dbo.v_R_System’

    Reply
  13. Travis says:
    18th February 2012 at 21:54

    Awesome, thanks for the info.
    Do you think there is any way to filter out what was installed by a task sequence?
    I am more interested in unapproved software installs.

    Reply

Leave a Reply

CAPTCHA Image
(required)