Getting Data In

Search help - how to use "group by"?

ramesharavinth
New Member

Hi, I have two JSON files like below,
File Name: report_15_8_2017_json.json

{
  "reportSummary": {
    "testMethodsSummary": {
      "passed": 450,
      "failed": 32,
      "skipped": 0
    },
    "authorTestSummary": [
      {
        "authorName": "PONNI NATARAJAN",
        "passed": 12,
        "failed": 0,
        "skipped": 0,
        "total": 12,    
        "passRate": "100"
      },

..............

FileName:  **report_22_8_2017_json.json**
{
  "reportSummary": {
    "testMethodsSummary": {
      "passed": 448,
      "failed": 32,
      "skipped": 0
    },
    "authorTestSummary": [
      {
        "authorName": "PONNI NATARAJAN",
        "passed": 8,
        "failed": 2,
        "skipped": 0,
        "total": 12,    
        "passRate": "66.6"
      },

..........
I want to group by the "authorTestSummary.authorName" and count the "passed" field from both the files and create a chart for the

timeline(filename)         Author Name                 Passed(count)
15_8_2017                    PONNI NATARAJAN                    8
22_8_2017                    PONNI NATARAJAN                   12

15_8_2017                    MICHAEL CARD                           4
22_8_2017                    MICHAEL CARD                           6

......

0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

|makeresults | eval raw="File Name: report_15_8_2017_json.json

{
\"reportSummary\": {
\"testMethodsSummary\": {
\"passed\": 450,
\"failed\": 32,
\"skipped\": 0
},
\"authorTestSummary\": [
{
\"authorName\": \"PONNI NATARAJAN\",
\"passed\": 12,
\"failed\": 0,
\"skipped\": 0,
\"total\": 12, 
\"passRate\": \"100\"
}:::File Name: report_22_8_2017_json.json
{
\"reportSummary\": {
\"testMethodsSummary\": {
\"passed\": 448,
\"failed\": 32,
\"skipped\": 0
},
\"authorTestSummary\": [
{
\"authorName\": \"PONNI NATARAJAN\",
\"passed\": 8,
\"failed\": 2,
\"skipped\": 0,
\"total\": 12, 
\"passRate\": \"66.6\"
}"
| makemv delim=":::" raw
| mvexpand raw
| rename raw AS _raw
| rex "File Name: (?<source>\S+)\s"
| rex mode=sed "s/^File Name: \S+\s//"

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

| rex field=source "report_(?<_time>\d+_\d+_\d+)_json.json"
| eval _time = strptime(_time, "%d_%m_%Y")
| spath
| rename reportSummary.* AS *
| rename *{}* AS **
| timechart first(authorTestSummary.passRate) AS authorTestSummary.passRate BY authorTestSummary.authorName

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this:

|makeresults | eval raw="File Name: report_15_8_2017_json.json

{
\"reportSummary\": {
\"testMethodsSummary\": {
\"passed\": 450,
\"failed\": 32,
\"skipped\": 0
},
\"authorTestSummary\": [
{
\"authorName\": \"PONNI NATARAJAN\",
\"passed\": 12,
\"failed\": 0,
\"skipped\": 0,
\"total\": 12, 
\"passRate\": \"100\"
}:::File Name: report_22_8_2017_json.json
{
\"reportSummary\": {
\"testMethodsSummary\": {
\"passed\": 448,
\"failed\": 32,
\"skipped\": 0
},
\"authorTestSummary\": [
{
\"authorName\": \"PONNI NATARAJAN\",
\"passed\": 8,
\"failed\": 2,
\"skipped\": 0,
\"total\": 12, 
\"passRate\": \"66.6\"
}"
| makemv delim=":::" raw
| mvexpand raw
| rename raw AS _raw
| rex "File Name: (?<source>\S+)\s"
| rex mode=sed "s/^File Name: \S+\s//"

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

| rex field=source "report_(?<_time>\d+_\d+_\d+)_json.json"
| eval _time = strptime(_time, "%d_%m_%Y")
| spath
| rename reportSummary.* AS *
| rename *{}* AS **
| timechart first(authorTestSummary.passRate) AS authorTestSummary.passRate BY authorTestSummary.authorName
0 Karma

ramesharavinth
New Member

Thanks woodcock it works for single authorName with raw json data.

How to do this using the source(eg; source="report_*"), so that it will pick up all the source files. Also if how to do the create the time chart when i have multiple author name, and not just one name.

0 Karma

woodcock
Esteemed Legend

As long as each author is in a separate event, it should work as-is.

0 Karma
Get Updates on the Splunk Community!

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...