transpose on selected fields

[Susha]

Hi Team,

I want to transpose few fields as below ..

(index=abc OR index=def) category= * OR NOT blocked =0 AND NOT blocked =2
|rex field=index "(?<Local_Market>[^cita]\w.*?)_"
| stats count(Local_Market) as Blocked by Local_Market
| addcoltotals col=t labelfield=Local_Market label="Total"
| append [search (index=abc OR index=def) blocked =0 | rex field=index "(?<Local_Market>\w.*?)_"
| stats count as Detected by Local_Market
| addcoltotals col=t labelfield=Local_Market label="Total"]
| stats values(*) as * by Local_Market
| transpose 0 header_field=Local_Market column_name=Local_Market

here i want to add one column of date ( eval Time=strftime(_time,"%m/%d/%y")) which should not be transpose ..

date                    Local_Market    Total   a  b   c
05-09-2021       INDIA                     3      1  1  1
05-09-2021       UK                          5       3  2  0

[PickleRick]

I don't follow. You want to have the date column left alone and all other columns transposed? It can't work even from simply algebraic point of view - you have two rows but three columns you want to transpose to rows.

Or is the table you presented the desired format?

Either way, I'd just add "static" column or row and transpose it if needed.

[ITWhisperer]

_time does not exist due to the stats commands removing it from the event pipeline. Which value of time do you want to use? now() or earliest/latest from the search timeframe or something else?

[Susha]

@ITWhisperer  this report is scheduled for yesterday so i want to give yesterday date only .. like only 09-06-2021

[ITWhisperer]

| addinfo
| eval date=info_min_time
| fieldformat date=strftime(date,"%m-%d-%Y")
| fields - info_*

[Susha]

thanks a lot @ITWhisperer  its working perfectly .. just one more thing .. as of now date column is coming at last column .. how can we place it in first column ..