Splunk Search

How to create a table from JSON?

Karanreddy
Engager

Hi, 

Can someone please help me to build a table using following JSON

My search results  as follows 

 

 

{ [-]
   docker: { [+]
   }
   kubernetes: { [+]
   }
   log: LOGGER {"name":"some text here","pathname":"/some/path","timestamp":"2023-05-03T20:35:06Z","action":"pageview","payload":{"category":"cloths","country":"US","appEnv":"production"},"uID":"0023493543"}
   stream: stdout
}

 

 

From this I would like draw the table as 

uID pathname category eventName country
0023493543
/some/path
cloths
some text here
US


Thanks in advance

Labels (2)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

Your raw event is itself in JSON, in which the log node embeds another JSON object mixed with other text.  Try extract that embedded JSON first.

The following assumes that the embedded JSON is not escaped in some other ways but is already conformant:

| rex field=log "LOGGER (?<LOGGER>{.*})"
| spath input=LOGGER

The above will not work if the LOGGER piece is escaped in some way.  Please post your sample data in raw text format if that fails

Tags (1)
0 Karma

TrangCIC81
Communicator

To create a table from the given JSON, you will need to extract the relevant fields from the "log" object and create a new object containing these fields. You can then use this object to populate the rows of a table.

Here's an example of how you can achieve this using Javascript&colon;

// Sample JSON data
const jsonData = {
  docker: {},
  kubernetes: {},
  log: {
    LOGGER: {
      name: "some text here",
      pathname: "/some/path",
      timestamp: "2023-05-03T20:35:06Z",
      action: "pageview",
      payload: {
        category: "cloths",
        country: "US",
        appEnv: "production"
      },
      uID: "0023493543"
    }
  },
  stream: "stdout"
};

// Extract the relevant fields from the log object
const logData = jsonData.log.LOGGER;
const { uID, pathname, payload } = logData;
const { category, country } = payload;

// Create a new object with the extracted fields
const rowData = { uID, pathname, category, eventName: logData.name, country };

// Create an array with the row data
const rows = [rowData];

// Create the table
const table = `
<table>
  <thead>
    <tr>
      <th>uID</th>
      <th>pathname</th>
      <th>category</th>
      <th>eventName</th>
      <th>country</th>
    </tr>
  </thead>
  <tbody>
    ${rows.map(row => `
      <tr>
        <td>${row.uID}</td>
        <td>${row.pathname}</td>
        <td>${row.category}</td>
        <td>${row.eventName}</td>
        <td>${row.country}</td>
      </tr>
    `).join('')}
  </tbody>
</table>
`;

console.log(table);

This code will output an HTML table with the following structure:

 
<table>
<thead>
<tr>
<th>uID</th>
<th>pathname</th>
<th>category</th>
<th>eventName</th>
<th>country</th>
</tr>
</thead>
<tbody>
<tr>
<td>0023493543</td>
<td>/some/path</td>
<td>cloths</td>
<td>some text here</td>
<td>US</td>
</tr>
</tbody>
</table>   

 

 

 

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...