How do you combine a pair of 2 fields into a column?

[anu41]

I need to create a Dashboard with below columns  from below event data.   I couldn't able to get "Status" column value which is combination of  eventData{}.StatusCount{}.status and eventData{}.StatusCount{}.count

Thanks in advance!!!

Dashboard five columns  and  expected values:   

 Date : "2021-10-14",
eventKey: "event.request",
ReceivedCount: 10,
ProcessedCount: 10,
MismatchCount: 0,

Status : 

• DOCUMENT_REQUEST_RECEIVED:10
• DOCUMENT_SUCCESS:10
• DOCUMENT_NOTIFY_SUCCESS:10

"eventData": [
{
"Date": "2021-10-14",
"eventKey": "event.request",
"ReceivedCount": 10,
"ProcessedCount": 10,
"MismatchCount": 0,
"StatusCount": [
{
"status": "DOCUMENT_REQUEST_RECEIVED",
"count": 10
},
{
"status": "DOCUMENT_SUCCESS",
"count": 10
},
{
"status": "DOCUMENT_NOTIFY_SUCCESS",
"count": 10
}
]
}
]

[yuanliu]

If the array eventData{}.statusCount{} is always fully populated, i.e., there is no null value for either eventData{}.StatusCount{}.status or eventData{}.StatusCount{}.count, the answer can be as simple as mvzip.

| eval Status = mvzip('eventData{}.StatusCount{}.status', 'eventData{}.StatusCount{}.count', ":")

If any of them could be null, you'll need mvexpand (as well as spath).  Something like

| spath path=eventData{}.StatusCount{}
| mvexpand eventData{}.StatusCount{}
| spath input=eventData{}.StatusCount{}
| eval Status = status . ":" count
| rename eventData{}.* as *
| stats values(Status) as Status by Date eventKey ReceivedCount ProcessedCount MismatchCount _time
``` your data illustration doesn't seem to include a unique key for eventData{} so I'm using _time ```

Hope this helps.

[anu41]

Thanks yuanliu for solution!!

The first solution mvzip function works for me, but  i didn't get values for the 'eventData{}.StatusCounr{}.status' ,  If remove the single codes (eventData{}.StatusCounr{}.status) getting  "Error in 'eval' command: The expression is malformed. Expected )."  Could you please suggest

| eval Status = mvzip('eventData{}.StatusCount{}.status', 'eventData{}.StatusCount{}.count', ":")

[yuanliu]

I assume that you already get eventData{}.* from Splunk?  I do notice that your sample data is missing outer brackets to make it JSON compliant.  Is that a real, complete (anonymized) event?

This is the emulation that I used:

| makeresults
| eval _raw="{\"eventData\": [
{
\"Date\": \"2021-10-14\",
\"eventKey\": \"event.request\",
\"ReceivedCount\": 10,
\"ProcessedCount\": 10,
\"MismatchCount\": 0,
\"StatusCount\": [
{
\"status\": \"DOCUMENT_REQUEST_RECEIVED\",
\"count\": 10
},
{
\"status\": \"DOCUMENT_SUCCESS\",
\"count\": 10
},
{
\"status\": \"DOCUMENT_NOTIFY_SUCCESS\",
\"count\": 10
}
]
}
]
}"
| spath
``` the above is data emulation ```

With this emulation, I get

Status	_raw	eventData{}.Date	eventData{}.MismatchCount	eventData{}.ProcessedCount	eventData{}.ReceivedCount	eventData{}.StatusCount{}.count	eventData{}.StatusCount{}.status	eventData{}.eventKey
DOCUMENT_REQUEST_RECEIVED:10 DOCUMENT_SUCCESS:10 DOCUMENT_NOTIFY_SUCCESS:10	{"eventData": [ { "Date": "2021-10-14", "eventKey": "event.request", "ReceivedCount": 10, "ProcessedCount": 10, "MismatchCount": 0, "StatusCount": [ { "status": "DOCUMENT_REQUEST_RECEIVED", "count": 10 }, { "status": "DOCUMENT_SUCCESS", "count": 10 }, { "status": "DOCUMENT_NOTIFY_SUCCESS", "count": 10 } ] } ] }	2021-10-14	0	10	10	10 10 10	DOCUMENT_REQUEST_RECEIVED DOCUMENT_SUCCESS DOCUMENT_NOTIFY_SUCCESS	event.request

[anu41]

Hi yuanliu , Thanks again for checking!!

I am able to get data but has an issue with the "Status"  values as below and screenshot

	EventDate: 21/10/2022	EventDate: 20/10/2022
Expected "Status"	DOCUMENT_ERROR:2 DOCUMENT_REQUEST_RECEIVED:2
Actual "Status"	DOCUMENT_REQUEST_RECEIVED:2	DOCUMENT_ERROR:2

|eval Status = mvzip('eventData{}.eventStatusCount{}.status', 'eventData{}.eventStatusCount{}.count', ":") | table "eventData{}.eventDate","eventData{}.ReceivedCount",
"eventData{}.ProcessedCount","eventData{}.MismatchCount","Status"
| rename eventData{}.eventDate as "EventDate",eventData{}.ReceivedCount as "Total Event Received Count", eventData{}.ProcessedCount as "Total Event Processed Count",
eventData{}.MismatchCount as "Total Event Mismatch Count"

"eventData": [
{
"eventDate": "2022-10-20",
"eventKey": "event.request",
"ProcessedCount": 0,
"eventStatusCount": [],
"ReceivedCount": 100,
"MismatchCount": 100
},
{
"eventDate": "2022-10-21",
"eventKey": "event.request",
"ProcessedCount": 2,
"eventStatusCount": [
{
"status": "DOCUMENT_ERROR",
"count": 2
},
{
"status": "DOCUMENT_REQUEST_RECEIVED",
"count": 2
}
],
"ReceivedCount": 1000,
"MismatchCount": 998
}
]

[yuanliu · Accepted solution]

I forgot to mvexpand the events themselves.

| spath path=eventData{}
| mvexpand eventData{}
| spath input=eventData{}
| eval Status = mvzip('eventStatusCount{}.status', 'eventStatusCount{}.count', ":")
| table eventDate, ReceivedCount, ProcessedCount, MismatchCount, Status
| rename ReceivedCount as "Total Event Received Count", ProcessedCount as "Total Event Processed Count", MismatchCount as "Total Event Mismatch Count"

eventDate	Total Event Received Count	Total Event Processed Count	Total Event Mismatch Count	Status
2022-10-20	100	0	100
2022-10-21	1000	2	998	DOCUMENT_ERROR:2 DOCUMENT_REQUEST_RECEIVED:2

[anu41]

Thanks @yuanliu for the solution!! It is working