subsearch query

[djoobbani]

Can someone please help me with this.

So I have the following query:

source=abc type=Change msg=" consumed" event_type="*"

Now for each of the above searches i need to do the following:

source=abc AND type=Change AND msg=" finished" event_type= above event type

Basically for each first one do another search for the same event_type 

What would the full query look like?

Thanks

[bowesmana]

You could start with something like this

source=abc type=Change (msg=" consumed" OR msg=" finished") event_type="*"
| stats values(msg) as msgs by event_type
| where mvcount(msgs)=2

where you search for all events with either event type and then only find those that have both consumed AND finished.

It will depend on what you want to do with the results as to what your search will look like - can you say what your goal is with the results?

[djoobbani]

Thanks @bowesmana  for your help.

So here are two queries:

Query #1

source=abc type=Changed msg="consumed event" event_id="*" | 

Query #2

source=abc type=Changed" msg=" finished processing"  duration>1 | stats avg(duration)

I want to be able to do the above two searches in one. based on the event_id being the same and at the end displaying the average of the duration which is the field from the second query.

Thanks!

[djoobbani]

Sorry @bowesmana i just changed the Query #1 slightly:

Query #1

source=abc type=Changed msg="consumed event" event_id="*" | rename event.data{}.id id | where id="XYZ"

Query #2

source=abc type=Changed" msg=" finished processing"  duration>1 | stats avg(duration)

I want to join the two above queries and at the end show the avg(duration). duration is a field from the second query.

Thanks!

[bowesmana]

OK, so you want to find events either where

id is XYZ AND finished duration is > N

OR 

id is XYZ OR finished duration is > N

Assuming it is AND then try this

source=abc type=Changed ((msg="consumed event" AND event_id="*") OR (msg=" finished processing" AND duration>1) 
| rename event.data{}.id as id 
| where id="XYZ"
| stats avg(duration)

Not totally sure if event_id and event.data{}.id are the same thing and if your finished processing events will have the event.data.id field - if not then you have to change the where clause.

Without seeing your data, it's not clear if the above will give you what you want, but the principle is the same in that you just need to combine the data types in a single query, so you have OR for the msg=XX statements.

The stats command needs to combine the events so that it can determine if there are both events for the result so you don't get events that ONLY have duration > 1

If you can give an example of the 2 events you want to collect together that would help

[djoobbani]

So here are two examples of events:

Event #1

Event #1
{
  source: api
  event_id: abcde
  msg: "consumed event"
  type: abc
  event: {
     data: {
        id: 12345
     }
  }
}

Event #2
{
  source: api
  event_id: abcde
  msg: "finished processing event"
  type: abc
  duration: 0.023456789
}

first need to query for msg="consumed event" and msg="finished processing event" that have the same event_id. Also need to only accept the ones that for msg="consumed event", its event.data.id is specific for a value:

Query #1

source=api AND msg="consumed event" | rename event.data{}.id AS id | where id=12345

Query #2

source=api AND msg="finished processing event" AND duration>0 | stats ave(duration)

I need to joion these queries into one where their event_id field is the same and at the end calculate and display the average duration which is a field only in events where msg="finished processing event"

[bowesmana · Accepted solution]

Please try not to keep editing and deleting messages, as I just lost my reply as a result. So here again...

Try this

source=abc type=Changed event_id="*" (msg="consumed event" OR (msg=" finished processing" AND duration>1) 
``` Rename consumed customer Id ```
| rename event.data{}.id as id 
``` Join start/finish events together for the event_id
| stats count values(duration) as duration values(id) as id by event_id
``` and filter only for customer ```
| where id="XYZ" AND count=2
``` Get the average ```
| stats avg(duration) as duration 

This assumes there is more than one sequence per customer. Will one customer have more than one pair of events for each event id? This counts the events to make sure that there are two events per event_id and then filters for your customer and then gets the average of all events

[djoobbani]

One thing i forgot to mention is that event.data.id can be an array:

event: {

    data: {

         [

             {

                   id: "XYZ"

             }

            {

                   id: "123"

             }

         ]

   }
}

[bowesmana]

You need to give more detail about your data - what do you want to occur when there are multiple IDs per event and you want to see averages for XYZ? In that example what does duration mean when there are two customer ids?

If you add the extra mvexpand line after the

| stats count values(duration) as duration values(id) as id by event_id
| mvexpand id

[djoobbani]

There is no relationship between id and duration.

It's jut that since there can be multiple id, we need to only accept msg="consumed event" events where the id="XYZ".

When i run the below query i get msg="consumed event" for every event, regardless what their id is.

[bowesmana]

Can you post a screenshot of your query and results - it's not easy to visualise what's going on with just the messages

[djoobbani]

Attached please find the query screen shot.

[bowesmana]

You put the mvexpand in the wrong place - it should be before the where clause.

Did this produce any results - there are none shown

[djoobbani]

Attached please find the modified query and part of the result screen shot

[bowesmana]

What actually IS the problem here - you are showing me the list of events, but not the statistics tab, which is what the result is.

You are searching in verbose mode, so you will see the events, but that is what happens in verbose mode - is there anything wrong with the result?

[djoobbani]

Never mind, my bad, i wasn't inside the Statistics tab. I now see the events matching the id.

Thank you for all your help

[djoobbani]

The query u provided does display the average duration, however for id="XYZ" doesn't seem to be check, because i get events for everything.

[bowesmana]

Run the query without the last line and you will see what the results are before the average

[richgalloway]

Check out the map command.