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!

Splunk is Nurturing Tomorrow’s Cybersecurity Leaders Today

Meet Carol Wright. She leads the Splunk Academic Alliance program at Splunk. The Splunk Academic Alliance ...

Part 2: A Guide to Maximizing Splunk IT Service Intelligence

Welcome to the second segment of our guide. In Part 1, we covered the essentials of getting started with ITSI ...

Part 1: A Guide to Maximizing Splunk IT Service Intelligence

As modern IT environments continue to grow in complexity and speed, the ability to efficiently manage and ...