How to sort strings based on number character?

[POR160893]

Hi,

I have a field which is a concatenation of a URL and a Sequence number, e.g. /google.ie:23 or /ebay.com:43.

I need to order this string field in descending order base based on the string number at the end of the field and then create 2 fields "To" and "From" showing:

To                                From
/yahoo.ie:1             /google.ie:2
/google.ie:2             /facebook.ie:3
.............................


At the moment, I am able to do the concatenation, but I am unable to sort on the numbers or create the required "To" or "From" fields:
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 combo_time
| table combo_time


Can you please help?



Many thanks,
P

[POR160893]

Hey,

Yes, I sorted on SEQUENCE. This is where I am currently at:

......
| 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 combo_time
| table combo_time, SEQUENCE
| eval temp = tonumber(SEQUENCE)
| sort temp
| rename combo_time as from
| eval to = from
| table from,to

The output is as follows:


For the "to" field, I need to remove the first  number and replace it with the 2nd, and continue doing this until "from" has its last number and "to" has nothing, like this:


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

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



How can I do this?????


Many thanks!

[ITWhisperer]

Can you not just sort by SEQUENCE?

[POR160893]

Hey,

Yes, I sorted on SEQUENCE. This is where I am currently at:

......
| 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 combo_time
| table combo_time, SEQUENCE
| eval temp = tonumber(SEQUENCE)
| sort temp
| rename combo_time as from
| eval to = from
| table from,to

The output is as follows:


For the "to" field, I need to remove the first  number and replace it with the 2nd, and continue doing this until "from" has its last number and "to" has nothing, like this:


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

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



How can I do this?????

(I gave you Karma for your last message though 😀)


Many thanks!