1 Reply Latest reply on May 31, 2016 12:47 PM by BChriscoli

    AM - Event Data Parsing

    BChriscoli Expert
      We collect a lot of data into our Management DB that has an undesired effect on the operational status of the console and the performance.
      This has been raised with AppSense (along with numerous customers over the years), but has failed to be improved. I've got it on somewhat decent knowledge that it should be changing in 10.1

      However, for the time being, we have an issue whereby, we need to keep an examine the data, but don't want to impact our operations.
      As such, we came up with a parsing routing.

      It basically grabs all the relevant data and splits it into the appropriate columns. Rather than having 4 rows of data for a single event (*sigh*), we get a single row, for a single event.

      Firstly it requires a new table to be created - obviously this has no links to any other table, or operational functionality, so *should* not have any adverse affect to the support anyone gets from AppSense.

      From Table creation:

       


      CREATE TABLE dbo.EventsParsed
      (EventID bigint NOT NULL
      ,AMEventID int NOT NULL
      ,UserID nvarchar(50) NOT NULL
      ,MachineName, nvarchar(50) NULL
      ,FileName nvarchar(MAX) NULL
      ,FilePath nvarchar(MAX) NULL
      ,ProductVersion nvarchar(MAX) NULL
      ,FileVersion nvarchar(MAX) NULL
      ,ProductName nvarchar(MAX) NULL
      ,CompanyName nvarchar(MAX) NULL
      ,Vendor nvarchar(MAX) NULL
      ,Description nvarchar(MAX) NULL
      ,HashValue nvarchar(MAX) NULL
      ,FileSize nvarchar(MAX) NULL
      ,Reason nvarchar(MAX) NULL
      ,EventTime datetime NULL)
      [/quote]

      Next we need to introduce the parsing routine.
      We currently only audit 9000 and 9023, so this works for us, however some modification may be required for other events.
      Once its parsed the data, it clears out the relevant Events and EventParamStrings tables of their relevant events.

      From Parsing Routine:

       


      /** AM Auditing Parsing Routing
          
           Bryan Chriscoli - AppSense SME
           UnitedHealth Group
           v1.0 - 31/5/16
      **/

      --Declares
      declare  @begin bigint
        ,@end bigint

      -- Grab the events in the DB
      Select @begin = Min(EventPK), @end = Max(EventPK)
      From dbo.events

      --Insert the data after parsing.
      INSERT INTO [dbo].[EventsParsed]
                 ([EventID]
           ,AMEventID
           ,UserID
           ,MachineName
           ,[FileName]
                 ,[FilePath]
                 ,[ProductVersion]
           ,[FileVersion]
                 ,[ProductName]
                 ,[CompanyName]
                 ,[Vendor]
                 ,[Description]
                 ,[HashValue]
                 ,[FileSize]
           ,[Reason]
           ,EventTime)
      select EventFK
      ,e.EventDefinitionFK
      ,e.UserName
      ,m.NetBiosName
      ,Reverse(Left(Reverse(SUBSTRING(value, 1, charindex(' [', value, 1)))
      ,charindex('\',Reverse(SUBSTRING(value, 1, charindex(' [', value, 1))))-1)) flnm
      ,SUBSTRING(value, 1, charindex(' [', value, 1)) filepath
      ,Replace(Substring(Replace( SUBSTRING(value, charindex('[ProductVersion: ', value, 1), Charindex('] [FileVersion: ', value, 1)), '[ProductVersion: ', '')
        ,1,Charindex('] [FileVersion: ', Replace( SUBSTRING(value, charindex('[ProductVersion: ', value, 1), Charindex('] [FileVersion: ', value, 1)), '[ProductVersion: ', ''), 1)),']','') ProductVersion
      ,ltrim(rtrim(Replace(Substring(Replace(SUBSTRING(value, charindex('[FileVersion: ', value, 1), Charindex('] [Product Name:', value, 1)),'[FileVersion:','')
        ,1,Charindex('] [Product Name:', Replace(SUBSTRING(value, charindex('[FileVersion: ', value, 1), Charindex('] [Product Name:', value, 1)),'[FileVersion:',''),1)),']',''))) FileVersion
      ,ltrim(rtrim(Replace(Substring(Replace(SUBSTRING(value, charindex('[Product Name: ', value, 1), Charindex('] [Company Name:', value, 1)),'[Product Name:','')
        ,1,Charindex('] [Company Name:', Replace(SUBSTRING(value, charindex('[Product Name: ', value, 1), Charindex('] [Company Name:', value, 1)),'[Product Name:',''),1)),']',''))) ProductName
      ,ltrim(rtrim(Replace(Substring(Replace(SUBSTRING(value, charindex('[Company Name: ', value, 1), Charindex('] [Vendor:', value, 1)),'[Company Name:','')
        ,1,Charindex('] [Vendor:', Replace(SUBSTRING(value, charindex('[Company Name: ', value, 1), Charindex('] [Vendor:', value, 1)),'[Company Name:',''),1)),']',''))) CompanyName
      ,ltrim(rtrim(Replace(Substring(Replace(SUBSTRING(value, charindex('[Vendor: ', value, 1), Charindex('] [File Description:', value, 1)),'[Vendor:','')
        ,1,Charindex('] [File Description:', Replace(SUBSTRING(value, charindex('[Vendor: ', value, 1), Charindex('] [File Description:', value, 1)),'[Vendor:',''),1)),']',''))) Vendor
      ,ltrim(rtrim(Replace(Substring(Replace(SUBSTRING(value, charindex('[File Description: ', value, 1), Charindex('] [Hash:', value, 1)),'[File Description:','')
        ,1,Charindex('] [Hash:', Replace(SUBSTRING(value, charindex('[File Description: ', value, 1), Charindex('] [Hash:', value, 1)),'[File Description:',''),1)),']',''))) FileDescription
      ,ltrim(rtrim(Replace(Substring(Replace(SUBSTRING(value, charindex('[Hash:', value, 1), Charindex(' Size:', value, 1)),'[Hash:','')
        ,1,Charindex(' Size:', Replace(SUBSTRING(value, charindex('[Hash:', value, 1), Charindex(' Size:', value, 1)),'[Hash:',''),1)),']',''))) HashValue
      ,ltrim(rtrim(Replace(Substring(Replace(SUBSTRING(value, charindex(' size:', value, 1), Charindex('bytes]', value, 1)),'size:','')
        ,1,Charindex(' bytes]', Replace(SUBSTRING(value, charindex(' size:', value, 1), Charindex(' bytes]', value, 1)),' size:',''),1)),']',''))) FileSize
      ,Case When EventDefinitionParamFK = 472 THEN (Select distinct [Value] from dbo.eventparamstrings where EventDefinitionParamFK = 475 and EventPK = EventFK) END as Reason
      ,e.[Time]
      from dbo.eventparamstrings p
        Join dbo.Events e on e.EventPK = p.EventFK
        Left outer join dbo.Machines m on m.MachinePK = e.MachineFK
      where EventDefinitionParamFK in (475, 472, 93, 81)
      and Value like '%[[]%'
      and value like '%Hash:%'
      and e.EventPK >=@begin and e.EventPK <=@end

      -- Clear out the Relevant tables of the parsed events
      delete from  dbo.eventparamstrings
      where EventfK >=@begin and EventfK <=@end

      delete from events
      where EventPK >=@begin and EventPK <=@end
      [/quote]