Combining multiple rows of chart data into one for alerting

[VikhyathMaiya]

Hello splunk community. I have a search query which i am using to report the daily api stats. I have a requirement where i want to send the result of below query (which is a chart table) into slack.

Query:

index=api* metaData.pid="apiDdata" | chart count BY apiName status

And the result looks like

What i learnt from the the splunk webhooks is that it can send only one row of data at a time. So if i have to send the whole data, i need to send it result by result.

So my question is, is there any way to combine the table into a single value something like below so that i can send it to slack at one shot ?

Something like below

=============================
|| ApiName    |      Success    |      NULL  ||
---------------------------------------------------
|| Api 1            ||    123               ||       222     ||
|| Api 1            ||    123               ||       222     ||
|| Api 1            ||    123               ||       222     ||
|| APi 2            ||       123            ||       222.    ||
----------------------------------------------------
The above table is a single string value which i am expecting it to be sent to slack. Is it possible ? Please help 🙂

[VikhyathMaiya]

@ITWhisperer Hello. Thanks for your answer. This seems like working. Just an extended question. Is there any way we can ensure the formatting of this table ? Since apiNames could be of varying length. Is there any way to achieve this ?

[ITWhisperer · Accepted solution]

| makeresults
| eval _raw="apiName    success    NULL
Api 1            123            222
Api 1            123            222
Api 1            123            222
APi 2            123            222"
| multikv forceheader=1
| table apiName success NULL
| eval line=printf("%-30s% 7d% 7d",apiName, success, NULL)
| stats list(line) as line
| eval headers=printf("%-30s% 7s% 7s","apiName","success","NULL")
| eval line=mvappend(headers,line)
| fields - headers

[VikhyathMaiya]

Just a follow up question. Incase i have an additional field called apiTime in each field and i want to calculate tp99 of grouped by apiName, how can we achieve that with this ? I tried couple of things with with appendCols.. Nothing made it work. Could you please help me with this as well ?

[VikhyathMaiya]

This is awesome. Works like a charm. Thank you 🙂

[ITWhisperer]

Would something like this work?

| makeresults
| eval _raw="apiName    success    NULL
Api 1            123            222
Api 1            123            222
Api 1            123            222
APi 2            123            222"
| multikv forceheader=1
| table apiName success NULL
| eval line=mvappend(apiName, success, NULL)
| eval line=mvjoin(line,"    ")
| transpose 0
| eventstats list(eval(if(column!="line",column,null()))) as headers
| eval headers=mvjoin(headers,"    ")
| transpose 0 header_field=column
| sort 0 column
| stats list(line) as line