0 Replies Latest reply on Apr 17, 2015 6:55 AM by gregf

    Generating a huge amount of fake event data

    gregf SupportEmployee
      In the latest of a series of posts solving problems that no-one has, here's a script to generate 10 million fake event 9000s.

      In my lab it generates 15,000 events per minute.

      # generateManyEvent9000s.ps1
      #
      #
      # Script to generate a lot of event 9000s
      
      $numEventsToGenerate = 10000000
      
      $SQLServer = "dcsql01"
      $SQLDBName = "ManagementServer86-01"
      $ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
      
      $dbCon = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
      $dbCon.Open()
      
      # Find out the event param numbers - in this case, the most recently polling one
      $sqlQuery = @"
      SELECT edp.*
        FROM [EventDefinitionParams] edp
        WHERE edp.EventDefinitionFK = 9000
      "@
      
      $command = New-Object System.Data.SQLClient.SQLCommand
      $command.Connection = $dbCon
      $command.CommandText = $sqlQuery
      $sqlReader = $command.ExecuteReader() 
      while ($sqlReader.Read()) {
          if ($sqlReader["Name"] -eq 'Full File Path') { $fullFilePathParam = $sqlReader["EventDefinitionParamPK"]}
          if ($sqlReader["Name"] -eq 'Client Name') { $clientNameParam = $sqlReader["EventDefinitionParamPK"]}
      }
      $sqlReader.Close()
      
      
      
      # Pick a random machine - in this case, the most recently polling one
      $sqlQuery = @"
      SELECT TOP 1 m.[MachinePK], m.[GroupFK]
        FROM [Machines] m
        ORDER BY m.LastPollTime DESC
      "@
      
      $command = New-Object System.Data.SQLClient.SQLCommand
      $command.Connection = $dbCon
      $command.CommandText = $sqlQuery
      $sqlReader = $command.ExecuteReader() 
      while ($sqlReader.Read()) {
          $machinePK = $sqlReader["MachinePK"]
          $groupFK = $sqlReader["GroupFK"]
      }
      $sqlReader.Close()
      
      
      # pick a random user - in this case the most recently created
      $sqlQuery = @"
      SELECT TOP 1 u.Name
        FROM [Users] u
        ORDER BY u.CreationTime DESC
      "@
      
      $command = New-Object System.Data.SQLClient.SQLCommand
      $command.Connection = $dbCon
      $command.CommandText = $sqlQuery
      $sqlReader = $command.ExecuteReader() 
      while ($sqlReader.Read()) {
          $userName = $sqlReader["Name"]
      }
      $sqlReader.Close()
      
      
      # create the event - using calc[0-15].exe for random exe data
      $sqlQuery = @"
          DECLARE @eventKey INT
          EXEC Event_Create @eventKey OUTPUT, 9000, @machineKey, @groupKey, @userName, @time
          EXEC Event_AddStringParamDirect @eventKey, @fullFilePathParam, @filePath
          EXEC Event_AddStringParamDirect @eventKey, @clientNameParam, @deviceName
      "@
      
      $command = New-Object System.Data.SQLClient.SQLCommand
      $command.Connection = $dbCon
      $command.CommandText = $sqlQuery 
      $command.Parameters.Add("@machineKey", [System.Data.SqlDbType]::UniqueIdentifier).Value = $machinePK
      $command.Parameters.Add("@groupKey", [System.Data.SqlDbType]::UniqueIdentifier).Value = $groupFK
      $command.Parameters.Add("@userName", [System.Data.SqlDbType]::NVarChar).Value = $userName
      $command.Parameters.Add("@time", [System.Data.SqlDbType]::DateTime).Value = Get-Date
      
      $command.Parameters.Add("@fullFilePathParam", [System.Data.SqlDbType]::Int).Value = $fullFilePathParam
      $command.Parameters.Add("@clientNameParam", [System.Data.SqlDbType]::Int).Value = $clientNameParam
      $command.Parameters.Add("@filePath", [System.Data.SqlDbType]::NVarChar)
      $command.Parameters.Add("@deviceName", [System.Data.SqlDbType]::NVarChar).Value = 'SOME-PC'
      
      # our blocked file will be $fileNameRoot[d] where d is a number between 0 and $variations
      $fileNameRoot = 'c:\windows\system32\calc'
      $variations = 15
      $metadata = ' [ProductVersion: 6.1.7600.16385] [FileVersion: 6.1.7600.16385] [Product Name: Microsoft® Windows® Operating System] [Company Name: Microsoft Corporation] [Vendor: ] [File Description: Windows Calculator]'
      
      1..$numEventsToGenerate | % {
          $command.Parameters['@filePath'].Value = $fileNameRoot + (Get-Random -Minimum 0 -Maximum $variations) + '.exe' + $metadata
          $command.ExecuteNonQuery() | out-null
          $_
          }
      
      
      $dbCon.Close()