How can I add a field to an extra column, depending on a condition?

[j_r]

Hi,

My log files look like this:

       ID Job_Type Target
Event1    1  A     X
Event2    1  B     Y
Event3    2  A     X1
Event4    2  B     Y1

X/X1= Startpoint
Y/Y1 = Endpoint

Startpoint is defined by Job_Type. So if Job_Type = A, then Targe = Startpoint

my search...

|basesearch
|stats values(Target) by ID

...gives me the following results:

    ID  values(Target)
    ID1 Startpoint
         Endpoint
    ID2 Startpoint
         Endpoint

How can I add the "Target" field to an extra column, depending on whether it is Job_Type=A or Job_Type=B?

Like this: ID, Startpoint, Endpoint

I tried if-condition, but it didn't work.

[whrg · Accepted solution]

Hello @j_r,

It should be possible by using stats with eval expressions.

Using stats in combination with eval looks like this:

index=_* | stats count(eval(sourcetype=="splunkd")) as count_splunkd

So in your case, try:

basesearch
| stats first(eval(if(Job_Type=="A",Target,NULL))) as Startpoint first(eval(if(Job_Type=="B",Target,NULL))) as Endpoint by ID

You could use values() instead of first(), but there should only be one value.

[j_r]

If i want to add another field to be displayed in the statistics, how do i do this?
with:

| table Startpoint, Endpoint, ID, Another_Field

does not work. Field stays empty

[whrg]

It depends on what you want to do.
However, the table command does not create any new fields.

[j_r]

It worked by adding values(another_field) 🙂 Thanks!

[j_r]

Thanks for this, but the columns for Target stayed empty .
I changed the search to this and its working now:

basesearch
| stats first(eval(if(like(Job_Type, "A%"),Target,NULL))) as Startpoint first(eval(if(like(Job_Type, "B%"),Target,NULL))) as Endpoint by ID

[whrg]

@j_r Can you post a table of what your desired results should look like?

[j_r]

The result should looks like this (from example above):

ID Startpoint Endpoint
1 X Y
2 X1 Y1

At the moment the results for Startpoint and endpoint are in the same column. I would like to have them in separate columns