How to convert transpose rows to columns by column name match

[josephpe]

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 Hits	perc90(Elapsed)
Create	171	55
Update	5	52
Search	172	60
Health	894	52

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.

[PickleRick]

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?

[yuanliu · Accepted solution]

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

Feature	perc90(Elapsed)	Total Hits
Create	59.000000000000000	1283
Health	48.700000000000000	191
Search	59	212
Update	52.000000000000000	551