Dashboards & Visualizations

How to sort strings based on number character?

POR160893
Builder

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

Labels (1)
0 Karma

POR160893
Builder

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!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Can you not just sort by SEQUENCE?

POR160893
Builder

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!

Tags (2)
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...