2 Replies Latest reply on Sep 10, 2014 4:17 AM by dbyrne727

    Custom Reports

    dbyrne727 Apprentice
      Hi All,

      Anyone got custom SQl scripts for listing all blocked apps for a particular list of machines starting with name Server* . I had a quick look at the tables and couldn't find the table with all the apps that were blocked. The Reports in the management Console contain too much info for what I need.
        • 1. Re: Custom Reports
          gregf SupportEmployee
          Blocked exes + dlls during the last 60 days for machines whose NetBIOS name starts with 'Server', in order of most-frequently blocked.



          Code:
          Use [ManagementServer]
          
          SELECT [value]  AS 'Path', 
                 Count(*) AS 'Number of blocks' 
          FROM   [EventParamStrings] 
                 INNER JOIN ( [Events] 
                              INNER JOIN [Machines] m
                                      ON [Events].MachineFK = m.MachinePK
                 AND m.NetBiosName LIKE 'Server%') 
                         ON [EventParamStrings].eventfk = [Events].eventpk 
                            AND [Events].EventDefinitionFK = 9000 
                            AND [EventParamStrings].EventDefinitionParamFK IN (SELECT 
                                EventDefinitionParamPK 
                                  FROM 
                                [EventDefinitionParams] 
                                  WHERE 
                                EventDefinitionFK = 9000 
                                AND Name = 'Full File Path') 
                            AND (value LIKE '%.exe' OR value LIKE '%.dll')
                AND [Events].[Time] >= 
                                Dateadd("day", -60, Getdate())
          GROUP  BY Value 
          ORDER  BY Count(*) DESC 
          
          


          Adapt as required.
          • 2. Re: Custom Reports
            dbyrne727 Apprentice
            Thanks for the help :)