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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...