Using join, earliest, table and latest.

[xploresplunk]

I am a new splunk user and I want to create a stats table showing different findings of an event using fields. However, I am running into error when I use the earliest command twice. Here's what I have so far:

index= xxx source=xxx sourcetype=xxx
| stats latest(name) as name, latest(call_time) as call_time
| eval call_time=strftime(...)
| table name, call_time

| join name
   [ search index=xxx source=xxx sourcetype=xxx conversation="\*hello\*"
   | stats earliest(_time) as first_hello by name
   | eval first_hello=srtftime(...) ]

| join name
   [ search index=xxx source=xxx  sourcetype=xxx messages="\*how\*"
   | stats earliest(_time) as first_how by name
   | eval first_how=srtftime(...) ]

| table name, call_time, first_hello, first_how

My errors are the following:
1. Both first_hello and first_how, are displaying the same time.
2. As I added the 'join' I could tell that the number of statistics decreased. I want to find a way that it displays all the events and that if a certain time (or word) cannot be found then it will just stay blank. I don't know if this is possible. I can see how that contradicts the purpose of 'join' but I couldn't find another way to do it.

Clarifications:
1. When it comes to messages and conversations, I want to find the first time that each field had a value containing the specific word(hello and how correspondingly). These two fields contain values that look like paragraphs. In other words, I want to find the first time that xxname said hello in conversation and how in messages.

Goal:
Display a table that shows: name,TIME of the last call (corresponding to that name), TIME of the first time the word hello was said in the values of the conversation field, TIME of the first time the word how was said in the values of the messages field.

Let me know if I need to clarify anything else.

[woodcock]

This cannot work because when you use the earliest() aggregation function, you can only pass in a field name (think about it; it makes sense).

[xploresplunk]

Hello, I tried using this and the table doesn't even show anymore. It says that no results were found. Any other ideas? Thank you so much for your help

[memarshall63]

I'm not sure if I understand the question exactly, but let me try to take a swing at it.
First, let's get a query that works. Here's something that should return results for you.

index=_internal sourcetype=scheduler 
 | stats latest(host) as name, latest(_time) as call_time
 | eval call_time=strftime(call_time,"%c")
 | table name, call_time
 | join name
    [ search index=_internal sourcetype=splunkd_ui_access
    | rename host as name
    | stats earliest(_time) as first_hello by name
    | eval first_hello=strftime(first_hello,"%c") 
    ]
 | join name
    [ search index=_internal sourcetype=splunk_web_access
    | rename host as name
    | stats earliest(_time) as first_how by name
    | eval first_how=strftime(first_how,"%c") ] 
 | table name, call_time, first_hello, first_how

When you run this you get a hostname and 3 timestamps based on the timestamps records for the 3 sourcetypes named.

If you replace one of the sourcetypes with something that doesn't exist. Say maybe in your 3rd join like this:

 ...
   | join name
      [ search index=_internal sourcetype=splunk_web_access_NOT_THERE
      | rename host as name
 ...

You get no results. This is because the (default:inner) join fails. There are no names that have records in all 3 joined segments. If you want all of the calls to show, but if they don't include a "hello" or a "how", it should leave those fields blank, then you want to use a left join. Like this:

 ...
    | join type=left name
      [ search index=_internal sourcetype=splunk_web_access_NOT_THERE
      | rename host as name
 ...

This should return your values from your first and second joins, but leave the 3rd timestamp blank. I hope that helps.

[xploresplunk]

I dont think this is doing what I'm looking for. Inside the 'join' youre not looking for the first instance of the word "hello" or "how". My join looks like this:
| join name
[ search index=xxx source=xxx sourcetype=xxx conversation="*hello*"
| stats earliest(_time) as first_hello by name
| eval first_hello=srtftime(...) ]

I wrote 'conversation="*hello*" ' because I want to look for the first value that contains the world 'hello' in a field named 'conversation'. Same thing for the second join.

As I wrote in the question, my problem is that both joins are storing the same timestamp and I dont understand what Im doing wrong.

Also, why are you renaming name as host and then again as name?

[memarshall63]

Yeah. Because I don't have the same data, I was just using a different sourcetype to return different records with different timestamps. Are your basic searches working? For example does this bit return all the "hello" events?

 index=xxx source=xxx sourcetype=xxx conversation="*hello*"

If both of your timestamps are showing the same time... maybe if you force them to be different, you can see what's happening better.

You can add something like this:
| eval _time=relative_time(now(),"-45m")
in one join and..
| eval _time=relative_time(now(),"-15m")
in another to make sure that you're getting the right timestamps.

[xploresplunk]

Is there any way to clear the earliest(_time)? When I delete one join it works perfectly. I believe that it is storing the earliest time that the word 'hello' or 'how' was written, instead of the first time that each was written. In other words, the table is showing the same timestamp for first_hello and first_how. Thank you for your help

I dont really understand what you mean by relative_time?

[xploresplunk]

No, there aren't. This shouldn't be happening anyways since I am looking at the data of a specific field. I cannot upload data because it's confidential. Does the join work for you?

[memarshall63]

Yes. My join (from the _internal index) works as expected in both the "join name" and "join type=left name" cases.

[xploresplunk]

Is there any way to clear the earliest(_time)? Both of my fields are under the same index, source and sourcetype so that might be a difference between mine and yours. I tried the join individually and it does get the data that I need. I think when I use earliest(_time) it keeps saving the earliest earliest time. Is this possible?

[memarshall63]

I agree that doesn't seem right.

My best suggestion is to try to add some additional fields to the joined stats commands. Maybe something like this:

| stats earliest(_time) as first_hello values(_time) AS all_hello_times values(_raw) as all_hello_raw by name

The results will be a mess... but it may give you some insight as to what's happening.

[memarshall63]

The other note I have after reading the documentation on "stats earliest()" is that remember that earliest(_time) doesn't return the lowest value _time it returns the time of the event with a value in _time... which in your case should be the same thing.. but that's a subtle difference...

I believe you could say "| stats earliest(_raw)" or "| stats earliest(conversation) and you would get the same thing.

[xploresplunk]

When I use | stats earliest(_raw)" or "| stats earliest(conversation), the table doesn't even show up anymore. Any other ideas on how to approach this?

[memarshall63]

hmm.. not sure how that would happen. Are there some events with both "HOW" and "HELLO" in them?

Can you upload some example data in a .csv file and full text of your search?

[xploresplunk]

For example, if the word hello was written at 2:54 and how was written at 1:22 then both, first_hello and first_how, are storing and displaying 1:22