Splunk Search

How to convert transpose rows to columns by column name match

josephpe
Explorer

I have result like this

 

 

column,	                   row 1
TotalHits: Create,	   171
TotalHits: Health,	   894
TotalHits: Search,	   172
TotalHits: Update,	   5
perc90(Elapsed): Create,   55
perc90(Elapsed): Health,   52
perc90(Elapsed): Search,   60
perc90(Elapsed): Update,   39

 

 

 

I want to convert this into

 Total Hitsperc90(Elapsed)
Create17155
Update552
Search17260
Health89452

 

What query should I use

Btw, to reach the above output I used like this, even this I am not sure whether its the best way
index=xyz
| search Feature IN (Create, Update, Search, Health)
| bin _time span=1m
| timechart count as TotalHits, perc90(Elapsed) by Feature
| stats max(*) AS *
| transpose

Basically I am trying to get the MAX of the 90th percentile and Total Hits during a time window.


Labels (3)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

Try this:

 

index=xyz Feature IN (Create, Update, Search, Health)
| timechart span=1m count as TotalHits, perc90(Elapsed) by Feature
| appendpipe
    [stats max("Total Hits: *") as *
    | eval _time = "Total Hits"]
| fields - "Total Hits: *"
| appendpipe
    [stats max("perc90(Elapsed): *") as *
    | eval _time = "perc90(Elapsed)"]
| fields - "perc90*"
| tail 2
| transpose header_field=_time column_name=Feature
| where Feature != "_span"

 

Two additional pointers:

  1. Do not use a second search line if Feature is already available in indexed data.
  2. Do not use a separate command for time bucket if you are going to use timechart.

This is my emulation:

 

index=_internal
| rename date_second as Elapsed, log_level as Feature
| eval Feature = case(Feature == "INFO", "Create", Feature == "WARN", "Health", Feature == "ERROR", "Search", true(), "Update")
``` the above emulates
index=xyz Feature IN (Create, Update, Search, Health)
```

 

With this, the result is

Featureperc90(Elapsed)Total Hits
Create59.0000000000000001283
Health48.700000000000000191
Search59212
Update52.000000000000000551

View solution in original post

Tags (1)
0 Karma

PickleRick
SplunkTrust
SplunkTrust

Ok, regardless of your transposing issues you have a logical flaw in your search (or I'm misunderstanding something)

index=xyz
| search Feature IN (Create, Update, Search, Health)
| bin _time span=1m
| timechart count as TotalHits, perc90(Elapsed) by Feature

This part I understand but here:

| stats max(*) AS *

Youre finding a max value separately for each column which means that max(count) might have been during a different time period than max('perc90(Elapsed)'). Are you sure that is what you want?

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Try this:

 

index=xyz Feature IN (Create, Update, Search, Health)
| timechart span=1m count as TotalHits, perc90(Elapsed) by Feature
| appendpipe
    [stats max("Total Hits: *") as *
    | eval _time = "Total Hits"]
| fields - "Total Hits: *"
| appendpipe
    [stats max("perc90(Elapsed): *") as *
    | eval _time = "perc90(Elapsed)"]
| fields - "perc90*"
| tail 2
| transpose header_field=_time column_name=Feature
| where Feature != "_span"

 

Two additional pointers:

  1. Do not use a second search line if Feature is already available in indexed data.
  2. Do not use a separate command for time bucket if you are going to use timechart.

This is my emulation:

 

index=_internal
| rename date_second as Elapsed, log_level as Feature
| eval Feature = case(Feature == "INFO", "Create", Feature == "WARN", "Health", Feature == "ERROR", "Search", true(), "Update")
``` the above emulates
index=xyz Feature IN (Create, Update, Search, Health)
```

 

With this, the result is

Featureperc90(Elapsed)Total Hits
Create59.0000000000000001283
Health48.700000000000000191
Search59212
Update52.000000000000000551
Tags (1)
0 Karma
Get Updates on the Splunk Community!

Cisco Use Cases, ITSI Best Practices, and More New Articles from Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Build Your First SPL2 App!

Watch the recording now!.Do you want to SPL™, too? SPL2, Splunk's next-generation data search and preparation ...

Exporting Splunk Apps

Join us on Monday, October 21 at 11 am PT | 2 pm ET!With the app export functionality, app developers and ...