title: Splunk Query - Compute stats by removing duplicates and custom query
body: <p>Need help in writing a splunk query that can help me measure the stats correctly. Please note the data is sometimes sent to splunk more than once sometimes due to an error on our server </p>
  
  <p>Here is an overview . 
  I am interested in knowing for how long a user has been using my application. An event will have
  user_id, connection state ( values are connected, paused and terminated ), session_length, timestamp. The usage of application for a user can be retrieved from field session_length.</p>
  
  <p>When the user is either in paused or terminated state, it would contain session length value. If the user state contains connected, paused and terminated for a sequence , then the session length should be derived from terminated state, else the session length should be computed from paused state.</p>
  
  <p>Unfortunately the paused and terminated data is sent more than once to the server. I would like to filter such data.</p>
  
  <p>The sample data is written below.</p>
  
  <p>Consideration --> If for a sequence of events for that user , the terminated/paused state is reported twice, then use the oldest session length for that sequence.</p>
  
  <p>Sample Data</p>
  
  <pre><code>User1 Terminated session_length=13 timestamp=10.13.
  User1 Terminated session_length=11 timestamp=10.11.
  User1 Paused     session_length=10 timestamp=10.10
  User1 Connected                    timestamp=10.00
  --
  User1 Paused     session_length=5  timestamp=9.05
  User1 Connected                    timestamp=9.00
  --
  User2 Terminated session_length=13 timestamp=10.13.
  User2 Terminated session_length=11 timestamp=10.11.
  User2 Paused     session_length=10 timestamp=10.10
  User2 Connected                    timestamp=10.00
  --
  User1 Terminated session_length=6  timestamp=9.06
  User1 Connected                    timestamp=8.00
  </code></pre>
  
  <p>--</p>
  
  <p>For the sample data set above, I have used the delimiter -- to filter the sequences and explain how the duration will be calculated.
  1) For the User1 ( from top to bottom ), consider the first sequence . User state is connected, paused, terminated,Terminated. So its session length 11 ( consider the earliest reported session length ).
  2) For User 1 , sequence 2 , the user state is connected, paused. So its session length - 5
  3) For user 2 , sequence 2, the User2 states are connected, paused, terminated, terminated. Its session length - 11.
  4) For User 1 , sequence 4 , the user state is connected, terminated. Its session length is 6</p>
  
  <p>The splunk query should report , a sum of session lengths per user which is</p>
  
  <p>User_Id TotalSessionLength
  User1    22
  User2    11</p>