Form a results table view from splunk multiple logs

We have several logs that our application pushes to splunk. Out of which, we need to prepare a table with few standard columns with each column has it's own specific search criteria.

Below are the some of the sample logs.

Request Received

The below log will appear for every request received by the API.

{
  "Timestamp": 1722604623.878,
  "Attributes": {
    "cloudevents.event_id": "e340d8d0-9b33-4523-b870-5ca9cbc65b96",
    "deployment.environment": "dev"
  },
  "TraceId": "7f51f69d9a575e5ca9aba8d69ed3e665",
  "SpanId": "5b404c7c746df8c6",
  "SeverityText": "INFO",
  "Body": "Started processing the event"
}

Request Successful

This log will be recoded, only when the request successfully processed by the API.

{
  "Timestamp": 1722604623.976,
  "Attributes": {
    "cloudevents.event_id": "e340d8d0-9b33-4523-b870-5ca9cbc65b96",
    "deployment.environment": "dev"
  },
  "TraceId": "7f51f69d9a575e5ca9aba8d69ed3e665",
  "SpanId": "9017a2df177ba231",
  "SeverityText": "INFO",
  "Body": "Event published successfully"
}

Request Failure

A request can be failure for several reasons, based on the reason the Body attribute will differ. However, all the failure logs will have the SeverityText is either WARN OR ERROR. Also this is mutually exclusive with the success log.

Below is the sample log.

{
  
  "Timestamp": 1722605277.139,
  "Attributes": {
    "deployment.environment": "dev",
    "cloudevents.event_id": "ef410f62-62b4-4ad5-9464-902d829ea5e0"
  },
  "TraceId": "83f26927a04718955a6d7bee22eec2d9",
  "SpanId": "c3d856ec2b782973",
  "SeverityText": "WARN",
  "Body": "Found schema violations for event : [$.data.clinicName: does not have a value in the enumeration [Mayo Clinic, Appollo, Care]]"
}

From all these kind of logs, need to build the following table.

Tried using appendcols like the query - index="my-index" Attributes.deployment.environment="dev" "Started processing the event" | top Attributes.cloudevents.event_id | table Attributes.cloudevents.event_id | appendcols Attributes.cloudevents.event_id [index="my-index" Attributes.deployment.environment="dev" SeverityText IN ("WARN", "ERROR")].

This is not a full query. Getting the error Unknown search command 'index'., so not able to proceed to create full query to full fill this.

I am not quite sure, what your criteria for getting a "No" for "Published" are. The current logic puts "Yes" if it has a "Body" of "Event published successfully", else "No". It is further unclear to me when an ID is recieved; so I assume that every event we see is received and thus it would always be "Yes". (If no event is received we would not know the ID, would we??) Please share feedback on this points or adopt them in the actual query by yourself.

This query the outputs your desired results:

First part makes this a run-anywhere example

| makeresults format=json data="[
{
  \"Timestamp\": 1722604623.878,
  \"Attributes\": {
    \"cloudevents.event_id\": \"e340d8d0-9b33-4523-b870-5ca9cbc65b96\",
    \"deployment.environment\": \"dev\"
      },
  \"TraceId\": \"7f51f69d9a575e5ca9aba8d69ed3e665\",
  \"SpanId\": \"5b404c7c746df8c6\",
  \"SeverityText\": \"INFO\",
  \"Body\": \"Started processing the event\"
    },
{
  \"Timestamp\": 1722604623.976,
  \"Attributes\": {
    \"cloudevents.event_id\": \"e340d8d0-9b33-4523-b870-5ca9cbc65b96\",
    \"deployment.environment\": \"dev\"
      },
  \"TraceId\": \"7f51f69d9a575e5ca9aba8d69ed3e665\",
  \"SpanId\": \"9017a2df177ba231\",
  \"SeverityText\": \"INFO\",
  \"Body\": \"Event published successfully\"
    },
{
  
  \"Timestamp\": 1722605277.139,
  \"Attributes\": {
    \"deployment.environment\": \"dev\",
    \"cloudevents.event_id\": \"ef410f62-62b4-4ad5-9464-902d829ea5e0\"
      },
  \"TraceId\": \"83f26927a04718955a6d7bee22eec2d9\",
  \"SpanId\": \"c3d856ec2b782973\",
  \"SeverityText\": \"WARN\",
  \"Body\": \"Found schema violations for event : [$.data.clinicName: does not have a value in the enumeration [Mayo Clinic, Appollo, Care]]\"
    }
]" 
| fields _raw
| spath 
| rename "Attributes.cloudevents.event_id" AS "Event Id"

Second part does the work

| eval Published=case(Body=="Event published successfully","Yes",true(),null())
| eval Received="Yes"
| stats values(Published) AS Published BY TraceId "Event Id" Received
| fillnull value="No" Published