Any way to break this row into two columns?

[bhavlik]

I am setting up a dashboard that monitors count of events on a daily basis and a previous 30 day average by customer. I have the search built and each customer has a row of data. For the dashboard, I want to display each customer in its own table. But I am struggling on how to convert the row of data in to the table format I'd like to display.

Here is the row of data after I query a customer from the saved search:

Here's what I'd like it to look like:

Any thoughts on how to achieve this?

[woodcock · Accepted solution]

Like this:

| makeresults 
| eval _raw="billing_yest=541 ftg_yest=3816 main_yest=4537 nav_yest=2685 billing_avg=688.37 ftg_avg=5128.5 main_avg=4694 nav_avg=3317.97" 
| kv 
| fields - _* 

|  rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| untable foo index_agg metric
| rex field=index_agg "^(?<index>.*)_(?<agg>.*)$"
| eval {agg} = metric
| rename avg AS Average, yest AS Daily
| selfjoin index
| table index Daily Average

[bhavlik]

This is great. Thanks! I definitely need to read up on a couple commands you used, specifically untable and the eval { }.

[woodcock]

The untable command is transpose with an anchor or pivot field (in this case foo which does not exist so it transposes everything).

[bhavlik]

I was able to get the desired results with the following:

  | loadjob savedsearch="Monitoring" 
    | search propertyId=123456789 
    | fields - propertyId FullHospitalName date phi* *_avg 
    | rename billing_yest as Billing ftg_yest as FTG main_yest as Main nav_yest as Nav 
    | transpose column_name=Index 
    | rename "row 1" as DailyCount 
    | join type=left Index 
        [| loadjob savedsearch="Monitoring" 
        | search propertyId=123456789 
        | fields - propertyId FullHospitalName date phi* *_yest 
        | rename billing_avg as Billing ftg_avg as FTG main_avg as Main nav_avg as Nav 
        | transpose column_name=Index 
        | rename "row 1" as Avg30]
    | eval Status=case(DailyCount=0,"No Events",DailyCount<Avg30*.8,"Warning",true(),"Good")

Is there a better way to do this?

[woodcock]

Do not use join.