«
»

Audit software installations r2

Kent Agerlund - 2nd November 2009

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)

10 Comments

  1. Amnon:

    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?

    6th November 2009, 20:51

  3. jeremy pearce:

    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

    1st December 2009, 17:01
  4. Kent Agerlund

    Kent Agerlund:

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

    1st December 2009, 17:26

  5. jeremy pearce:

    Hi kent Did you get my mail?

    2nd December 2009, 11:27

  6. Jason Scott:

    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?

    4th December 2009, 15:27
  7. Kent Agerlund

    Kent Agerlund:

    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

    5th December 2009, 14:25

  8. Sachin:

    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,

    6th April 2010, 00:37

  9. Tharanga Weerakkody:

    Hi Kent,

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

    17th June 2010, 08:02
  10. Kent Agerlund

    Kent Agerlund:

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

    23rd June 2010, 14:58

Leave a comment