How to create a new field based on another field under certain conditions?

[POR160893]

Hi,

I have created a field, "from", which is a concatenation of 2 string fields, as follows:
index = .....
| eval time_epoch = strptime('SESSION_TIMESTAMP', "%Y-%m-%d %H:%M:%S")
| convert ctime(time_epoch) as hour_minute timeformat="%Y-%m-%d %H:%M"
| strcat URL_PATH ":" SEQUENCE from
| table  from

The "from" field is made up of a URL string , a : character and then a number in string format.

I need to create another field "to", so that for each Nth event where the respective "from" value ends in the number N, the corresponding "to" has  the URL for the (N+1) event, : and (N+1)th value.

Example:
from                                                  to
....:1                                                       ......:2
.....2                                                       .......:3
.....:3                                                      .......:4

........................................
.........N                                                  <BLANK>

In this way, the last value of the "from" field would have a blank "to" value.
Essentially, I need to slid the "from" values up by 1 and name this other field as "to".

I have tried Regex and different eval combinations but no success.

Can you please help?


Many thanks,
P

[richgalloway]

You should be able to use eval to get the SEQUENCE value for the to field.

index = .....
| eval time_epoch = strptime('SESSION_TIMESTAMP', "%Y-%m-%d %H:%M:%S")
| convert ctime(time_epoch) as hour_minute timeformat="%Y-%m-%d %H:%M"
| eval SEQUENCEto = tonumber(SEQUENCE) + 1
| strcat URL_PATH ":" SEQUENCE from
| strcat URL_PATH ":" SEQUENCEto to
| table  from

[POR160893]

Hi,

Your query does not give me the required output.

If URL X is associated to 1, Y associated to 2 and Z associated to 3 and each are in the "from column", the respective "to" column is Y to 2,  Z to 3, ...... and blank at the very end as the last URL in "from" to connected to no other URL.

Your query just increases the final number, without changing the URL associated with that number. So, the above example would make the "to" column simply X is associated to 2, Y associated to 3 and Z associated to 4.

This is further shown on the output I got in splunk:

I need to maintain the relationship with the URL and the final number as I increase each value by 1 to create the "to" field and have the last value blank .


Can you help?

(I gave your last message Karma though 😀)


Thanks!

[richgalloway]

Could you provide a better example output?  The one in the OP left too much to the imagination and, obviously, I imagined wrong.

[bowesmana · Accepted solution]

Does 

| autoregress from as to

give you what you need. That simply copies the previous event's from field to the current event's to field.

Not sure if I fully understood your need though

Simple example to demonstrate

index=_audit
| stats count by user
| eval user=user.":".count
| rename user as from
| autoregress from as to

[POR160893]

This was EXACTLY what I was looking for and thanks for teaching me about the autoregress function too.

Needless to say, I gave your answer much deserved Karma 😀

[richgalloway]

If your problem is resolved, then please click the "Accept as Solution" button to help future readers.