Splunk Search

the way fields are arranged in a timechart / join combination particularily the 2nd search being joined

Motivator

I basically have 3 KPIs that I want to do a search on
search1 will be for yesterday and
search 2 will be for some period in the past(note here it is just yesterday)

Then I want to join them together based on a common field, in this case, it is Date
My sample search is below:

This is my sample search:

| makeresults 
     | eval data = "
1-Sep    657 34 35;
2-Sep    434 34 35;
    " 
     | makemv delim=";" data
     | mvexpand data
     | rex field=data "(?<Date>\d+-\w+)\s+(?<kpi1>\d+)\s+(?<kpi2>\d+)\s+(?<kpi3>\d+)"
     | fields + Date kpi1 kpi2 kpi3 | fields - _time
     | search kpi1 = * 
     |rename kpi1 as  "Incoming Calls in Mobile Office Directions - Call Release by Bearer Fail Times" 
     | rename kpi2 as "Incoming Calls in Mobile Office Directions - Common Resources Application Failures" 
     | rename kpi3 as "Incoming Calls in Mobile Office Directions - Assignment Failures"
     |
     join type=outer [
     | makeresults 
     | eval data = "
1-Sep    657 34 35;
2-Sep    434 34 35;
    " 
     | makemv delim=";" data
     | mvexpand data
     | rex field=data "(?<Date>\d+-\w+)\s+(?<kpi1_d>\d+)\s+(?<kpi2_d>\d+)\s+(?<kpi3_d>\d+)"
     | fields + Date kpi1_d kpi2_d kpi3_d | fields - _time
     | search kpi1_d = *
     |rename kpi1_d as  "Incoming Calls in Mobile Office Directions - Call Release by Bearer Fail Times_d" 
     | rename kpi2_d as "Incoming Calls in Mobile Office Directions - Common Resources Application Failures_d" 
     | rename kpi3_d as "Incoming Calls in Mobile Office Directions - Assignment Failures_d"
     ]

My question is why are the field names not appearing across the top in the same order they are in the search, that is in search2? That is 1,2,3 in the search and then 1,2,3 from left to right in the table. It does this for the first search. But it changes the order for the second search.
The picture below hopefully demonstrates this.

Note: the names I am using are a bit cumbersome. The issue does not happen if I just use the kpi names kpi1,kpi2, and kpi3. So I am trying to understand why it does it for the names I have chosen.
Note2: I can explicitly use the fields command to rearrange them but I would ideally like to not have to do this
pic of the search and the associated table

Tags (3)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

Hi HattrickNZ,

well, Splunk sorts fields alphabetically and therefore Incoming Calls in Mobile Office Directions - Assignment Failures_d comes before Incoming Calls in Mobile Office Directions - Call Release by Bearer Fail Times_d followed by Incoming Calls in Mobile Office Directions - Common Resources Application Failures_d.
This is also the reason why it works when you use kpi1 and so on

Hope this helps ...

cheers, MuS

View solution in original post

0 Karma

Esteemed Legend

Alphabetical is Alphabetical. The fields names must be less similar than you believe them to be. Often the difference is various forms of dashes vs. hyphens, spaces vs. tabs, or numbers/positions of spaces. Try typing the field names in BY HAND, character by character, without cut/paste.

Esteemed Legend

First, DO NOT USE join. Splunk resorts the fields alphabetically most of the time but you can use | table Your Field Names In This Order Here to reorder them, like so:

| makeresults 
| eval data = "1-Sep    657 34 35;2-Sep    434 34 35" 
| makemv delim=";" data 
| mvexpand data 
| rex field=data "(?<Date>\d+-\w+)\s+(?<kpi1>\d+)\s+(?<kpi2>\d+)\s+(?<kpi3>\d+)" 
| search kpi1 = * 
| rename kpi1 as "Incoming Calls in Mobile Office Directions - Call Release by Bearer Fail Times" 
| rename kpi2 as "Incoming Calls in Mobile Office Directions - Common Resources Application Failures" 
| rename kpi3 as "Incoming Calls in Mobile Office Directions - Assignment Failures" 
| append
    [| makeresults 
    | eval data = "1-Sep    657 34 35;2-Sep    434 34 35" 
    | makemv delim=";" data 
    | mvexpand data 
    | rex field=data "(?<Date>\d+-\w+)\s+(?<kpi1_d>\d+)\s+(?<kpi2_d>\d+)\s+(?<kpi3_d>\d+)" 
    | search kpi1_d = * 
    | rename kpi1_d as "Incoming Calls in Mobile Office Directions - Call Release by Bearer Fail Times_d" 
    | rename kpi2_d as "Incoming Calls in Mobile Office Directions - Common Resources Application Failures_d" 
    | rename kpi3_d as "Incoming Calls in Mobile Office Directions - Assignment Failures_d"]
| table Date Inc*
| selfjoin Date
0 Karma

Motivator

Splunk resorts the fields
alphabetically most of the time

Most of the time really?

And could it be explained why I should not use join?

It does in this order using my search:

Incoming Calls in Mobile Office Directions - Call Release by Bearer Fail Times

Incoming Calls in Mobile Office Directions - Common Resources Application Failures

Incoming Calls in Mobile Office Directions - Assignment Failures

Incoming Calls in Mobile Office Directions - Assignment Failures_d

Incoming Calls in Mobile Office Directions - Call Release by Bearer Fail Times_d

Incoming Calls in Mobile Office Directions - Common Resources Application Failures_d

Why does it not give me this order: (if it does it alphabetically for the first search, why does it not do it for the 2nd search? All I am doing is adding _d to the end )

Incoming Calls in Mobile Office Directions - Call Release by Bearer Fail Times

Incoming Calls in Mobile Office Directions - Common Resources Application Failures

Incoming Calls in Mobile Office Directions - Assignment Failures

Incoming Calls in Mobile Office Directions - Call Release by Bearer Fail Times_d

Incoming Calls in Mobile Office Directions - Common Resources Application Failures_d
Incoming Calls in Mobile Office Directions - Assignment Failures_d

0 Karma

SplunkTrust
SplunkTrust

Hi HattrickNZ,

well, Splunk sorts fields alphabetically and therefore Incoming Calls in Mobile Office Directions - Assignment Failures_d comes before Incoming Calls in Mobile Office Directions - Call Release by Bearer Fail Times_d followed by Incoming Calls in Mobile Office Directions - Common Resources Application Failures_d.
This is also the reason why it works when you use kpi1 and so on

Hope this helps ...

cheers, MuS

View solution in original post

0 Karma

Motivator

So I need to have my fields in search 1 sorted alphabetically and then search 2 will be alligned? tks.
Think I get it.

| makeresults 
      | eval data = "
 1-Sep    657 34 35;
 2-Sep    434 34 35;
     " 
      | makemv delim=";" data
      | mvexpand data
      | rex field=data "(?<Date>\d+-\w+)\s+(?<kpi1>\d+)\s+(?<kpi2>\d+)\s+(?<kpi3>\d+)"
      | fields + Date kpi1 kpi2 kpi3 | fields - _time
      | search kpi1 = * 
      | rename kpi1 as "Incoming Calls in Mobile Office Directions - Assignment Failures"
      | rename kpi2 as "Incoming Calls in Mobile Office Directions - Call Release by Bearer Fail Times" 
      | rename kpi3 as "Incoming Calls in Mobile Office Directions - Common Resources Application Failures" 
      |
      join type=outer [
      | makeresults 
      | eval data = "
 1-Sep    657 34 35;
 2-Sep    434 34 35;
     " 
      | makemv delim=";" data
      | mvexpand data
      | rex field=data "(?<Date>\d+-\w+)\s+(?<kpi1_d>\d+)\s+(?<kpi2_d>\d+)\s+(?<kpi3_d>\d+)"
      | fields + Date kpi1_d kpi2_d kpi3_d | fields - _time
      | search kpi1_d = *
      |rename kpi1_d as  "Incoming Calls in Mobile Office Directions - Call Release by Bearer Fail Times_d" 
      | rename kpi2_d as "Incoming Calls in Mobile Office Directions - Common Resources Application Failures_d" 
      | rename kpi3_d as "Incoming Calls in Mobile Office Directions - Assignment Failures_d"
      ]
0 Karma