How to sort a field list which below with an uppercase letter followed by "- N"?

[jip31]

hi

I need to sort a field list which below with an uppercase letter followed by "- N"

How to do please?

[VatsalJagani]

@jip31 How about the below query:

| makeresults | eval data="abc
C - N
A - N
def
B - N
ghi" | eval data=split(data,"
") | mvexpand data

| eval is_capital=if(match(data,"^[A-Z].*"), 1, 0)
| sort is_capital, data
| fields - is_capital

@richgalloway - Sorry I steal makeresults part of your query. 😀

[somesoni2]

Please provide some sample values and corresponding expected output.

[jip31]

Hi

I put an example of my need in one post

[jip31]

Hi

Sorry it doesnt works but i need to share more explanations

The fields I need to sort correspond to the column-name of a transpose command

| transpose 0 header_field=time column_name=KPI

Nb d'utilisateurs CDE		
CAP CXX - Nb d'erreurs		
CAP CXX - Nb d'utilisateurs 			
DOSSIER XX - Nb d'erreurs		
DOSSIER XX - Nb d'utilisateurs 			
Nb d'utilisateurs ABC		
Nb d'utilisateurs EFG					
SHARE - Nb d'erreurs		
SHARE - Nb d'utilisateurs	
Signal 		
EDGE - Crash		
Vélocité RTT (% de CPU)		
WEST UNTED- Nb d'erreurs		
WEST UNITED - Nb d'utilisateurs

Here is a example of my fields not sorted 

first, I need to display the field which begin with an uppercase folowed by - N

after i nedd to sort the field with begin with a lowercase

And at the end I need to sort the fields which begins with an uppercase but followed by something else than "- N"

CAP CXX - Nb d'erreurs
CAP CXX - Nb d'utilisateurs
DOSSIER XX - Nb d'erreurs
DOSSIER XX - Nb d'utilisateurs
SHARE - Nb d'erreurs
SHARE - Nb d'utilisateurs
WEST UNTED- Nb d'erreurs
WEST UNITED - Nb d'utilisateurs
Nb d'utilisateurs ABC
Nb d'utilisateurs CDE
Nb d'utilisateurs EFG
Signal
Vélocité RTT (% de CPU)
EDGE - Crash

 which di 

[yuanliu · Accepted solution]

Even so, @VatsalJagani's method should still work, e.g.,

| eval order=case(match(KPI, "^[A-Z].*- Nb"), 0, match(KPI, "^[a-z]"), 1, match(KPI, "^[A-Z]"), 2)
| sort order, KPI
| fields - order

 Using the sample input you listed, output is

KPI

CAP CXX - Nb d'erreurs

CAP CXX - Nb d'utilisateurs

DOSSIER XX - Nb d'erreurs

DOSSIER XX - Nb d'utilisateurs

SHARE - Nb d'erreurs

SHARE - Nb d'utilisateurs

WEST UNITED - Nb d'utilisateurs

WEST UNTED- Nb d'erreurs

EDGE - Crash

Nb d'utilisateurs ABC

Nb d'utilisateurs CDE

Nb d'utilisateurs EFG

Signal

Vélocité RTT (% de CPU)

[jip31]

could you help me please concerning my last question?

[jip31]

thanks it's almost perfect

I just forgotten the case where the field sarts with an uppercase follewed by " - F"

I wrote this but it changes nothing 

match(KPI, "^[A-Z].*- F"), 3

[yuanliu]

---

@jip31 wrote:

I just forgotten the case where the field sarts with an uppercase follewed by " - F"

I wrote this but it changes nothing 

match(KPI, "^[A-Z].*- F"), 3

---

I assume that you did 

| eval order=case(match(KPI, "^[A-Z].*- Nb"), 0, match(KPI, "^[a-z]"), 1, match(KPI, "^[A-Z]"), 2, match(KPI, "^[A-Z].*- F"), 3)

This does not change outcome because every match for "^[A-Z].*- F" already matches "^[A-Z]" (2) which precedes that entry in the case statement.  In many regex expressions, this would make some complex study, but because case statement is evaluated sequentially, you can simply force the order of case:

| eval order=case(match(KPI, "^[A-Z].*- Nb"), 0, match(KPI, "^[a-z]"), 1, match(KPI, "^[A-Z].*- F"), 3, match(KPI, "^[A-Z]"), 2)

This reads a bit awkward but should do the job.  If the awkwardness impedes maintenance in the future, the following makes the logic clearer:

| eval order=case(match(KPI, "^[A-Z].*- Nb"), 0, match(KPI, "^[a-z]"), 1, match(KPI, "^[A-Z].*- F"), 3, true(), 2)

The last statement expresses the idea that  "2" is used as a default. (Semantically this is different from 'match(KPI, "^[A-Z]"), 2', so you want to make a decision based on anticipated data.)

[PickleRick]

By default the fileds are sorted alphabetically. There is also one thing we can use - the fields in table command don't repeat.

So you can simply request the table command to print the '* -N*' fields first.

| table '* -N*' *

Unfortunately, if you want to do some more sophisticated reordering (like putting the N* fields sorted in reverse or something similarily unusual), there's no easy way to do that short of transposing all results, sorting them and transposing back (which is very ineffective).

[richgalloway]

 It's not clear what you're trying to do.  Please use more words to describe the data and the desired results.

[jip31]

Hi

I need to sort fields which belows with an upper case followed by N -

Something like that

"Test - N"

[kamlesh_vaghela]

@jip31 

Any sample data or expected out would be appreciated  to help you here.

KV

🙂

[jip31]

For example, imagine that my fields are :

abc

C - N

A - N

def

B - N

ghi

I would like to sort like this :

abc

def

ghi

A - N

B - N

C - N

[richgalloway]

The challenge here is Splunk's sort command sorts strings in lexicographical order, which means upper-case letters will come before lower-case letters.  To get lower-case letters sorted before upper-case letters would require some SPL gymnastics (and I'm not sure where to start).

[richgalloway]

After thinking about this for a little while, I came up with this query which should do the job.

| makeresults | eval data="abc
C - N
A - N
def
B - N
ghi" | eval data=split(data,"
") | mvexpand data
```Ignore the above as it is just for test data```
```Prepend text ending in "- N" with "zz" to force it to the end of the list ```
| eval sorter=if(match(data,"- N$"),"zz".data, data)
```Sort on the new field```
| sort sorter 
```Discard the sort field```
| fields - sorter

[PickleRick]

That ain't that obvious, I'm afraid. OK, if the values in the field have predictable format, it might work relatively easily, but the lexicographic order is a bit more problematic.

See for yourself

| makeresults 
| eval _raw="a,_b,:c,$d,A,Z,ę,Ń,ę,1,\\,|" 
| eval raw=split(_raw,",")
| mvexpand raw
| sort raw