Splunk Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question
Solved! Jump to solution

sPath command Missing random few values from JSON Array

premrajvs
Explorer
‎08-23-2024 11:48 AM

In the data, there is an array of 5 commit IDs. For some reason, it is only returning 3 values. Not sure why  2 values are missing. Would like a fresh set of eyes to take a look please.

Query

index=XXXXX source="http:github-dev-token" eventtype="GitHub::Push" sourcetype="json_ae_git-webhook"
| spath output=commit_id path=commits.id

sourcetype definition

[ json_ae_git-webhook ]
AUTO_KV_JSON=false
CHARSET=UTF-8
KV_MODE=json
LINE_BREAKER=([\r\n]+)
NO_BINARY_CHECK=true
SHOULD_LINEMERGE=true
TRUNCATE=100000
category=Structured
description=JavaScript Object Notation format. For more information, visit http://json.org/
disabled=false
pulldown_type=true

Raw JSON data

{
"ref":"refs/heads/Dev",
"before":"d53e9b3cb6cde4253e05019295a840d394a7bcb0",
"after":"34c07bcbf557413cf42b601c1794c87db8c321d1",
"commits":[
{
"id":"a5c816a817d06e592d2b70cd8a088d1519f2d720",
"tree_id":"15e930e14d4c62aae47a3c02c47eb24c65d11807",
"distinct":false,
"message":"rrrrrrrrrrrrrrrrrrrrrr",
"timestamp":"2024-08-12T12:00:04-05:00",
"url":"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/aaaaaaaaaaaa",
"author":{
"name":"aaaaaa aaaaaa",
"email":"101218171+aaaaaa@users.noreply.github.com",
"username":"aaaaaa"
},
"committer":{
"name":"aaaaaa aaaaaa",
"email":"101218171+aaaaaa@users.noreply.github.com",
"username":"aaaaaa"
},
"added":[

],
"removed":[

],
"modified":[
"asdafasdad.json"
]
},
{
"id":"a3b3b6f728ccc0eb9113e7db723fbfc4ad220882",
"tree_id":"3586aeb0a33dc5e236cb266c948f83ff01320a9a",
"distinct":false,
"message":"xxxxxxxxxxxxxxxxxxx",
"timestamp":"2024-08-12T12:05:40-05:00",
"url":"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/a3b3b6f728ccc0eb9113e7db723fbfc4ad220882",
"author":{
"name":"aaaaaa aaaaaa",
"email":"101218171+aaaaaa@users.noreply.github.com",
"username":"aaaaaa"
},
"committer":{
"name":"aaaaaa aaaaaa",
"email":"101218171+aaaaaa@users.noreply.github.com",
"username":"aaaaaa"
},
"added":[

],
"removed":[

],
"modified":[
"sddddddf.json"
]
},
{
"id":"bdcd242d6854365ddfeae6b4f86cf7bc1766e028",
"tree_id":"8286c537f7dee57395f44875ddb8b2cdb7dd48b2",
"distinct":false,
"message":"Updating pipeline: pl_gwp_file_landing_check. Adding Sylvan Performance",
"timestamp":"2024-08-12T12:06:10-05:00",
"url":"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/bdcd242d6854365ddfeae6b4f86cf7bc1766e028",
"author":{
"name":"aaaaaa aaaaaa",
"email":"101218171+aaaaaa@users.noreply.github.com",
"username":"aaaaaa"
},
"committer":{
"name":"aaaaaa aaaaaa",
"email":"101218171+aaaaaa@users.noreply.github.com",
"username":"aaaaaa"
},
"added":[

],
"removed":[

],
"modified":[
"asadwefvdx.json"
]
},
{
"id":"108ebd4ff8ae9dd70e669e2ca49e293684d5c37a",
"tree_id":"5a6d71393611718b8576f8a63cdd34ce619f17dd",
"distinct":false,
"message":"asdrwerwq",
"timestamp":"2024-08-12T10:09:33-07:00",
"url":"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/108ebd4ff8ae9dd70e669e2ca49e293684d5c37a",
"author":{
"name":"dfsd",
"email":"l.llllllllllll@aaaaaa.com",
"username":"aaaaaa"
},
"committer":{
"name":"lllllllllllll",
"email":"l.llllllllllll@abc.com",
"username":"aaaaaa"
},
"added":[

],
"removed":[

],
"modified":[
"A.json",
"A.json",
"A.json"
]
},
{
"id":"34c07bcbf557413cf42b601c1794c87db8c321d1",
"tree_id":"5a6d71393611718b8576f8a63cdd34ce619f17dd",
"distinct":true,
"message":"asadasd",
"timestamp":"2024-08-12T13:32:45-05:00",
"url":"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/34c07bcbf557413cf42b601c1794c87db8c321d1",
"author":{
"name":"aaaaaa aaaaaa",
"email":"101218171+aaaaaa@users.noreply.github.com",
"username":"aaaaaa"
},
"committer":{
"name":"GitasdjwqaikHubasdqw",
"email":"noreply@gitskcaskadahuqwdqbqwdqaw.com",
"username":"wdkcszjkcsebwdqwdfqwdawsldqodqw"
},
"added":[

],
"removed":[

],
"modified":[
"a.json",
"A1.json",
"A1.json"
]
}
],
"head_commit":{
"id":"34c07bcbf557413cf42b601c1794c87db8c321d1",
"tree_id":"5a6d71393611718b8576f8a63cdd34ce619f17dd",
"distinct":true,
"message":"sadwad from xxxxxxxxxxxxxxx/IH-5942-Pipeline-Change\n\nIh 5asdsazdapeline change",
"timestamp":"2024-08-12T13:32:45-05:00",
"url":"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/3weweeeeeeeee,
"author":{
"name":"askjas",
"email":"101218171+asfsfgwsrsd@users.noreply.github.com",
"username":"asdwasdcqwasfdc-qwgbhvcfawdqxaiwdaszxc"
},
"committer":{
"name":"GsdzvcweditHuscwsab",
"email":"noreply@gitasdcwedhub.com",
"username":"wefczeb-fwefvdszlow"
},
"added":[

],
"removed":[

],
"modified":[
"zzzzzzz.json",
"Azzzzz.json",
"zzzz.json"
]
}
}
Labels (1)
Labels
0 Karma
Reply
1 Solution
Solved! Jump to solution
Solution

premrajvs
Explorer
‎08-26-2024 07:26 AM

Thank you for all the inputs. Here is the final query

index=Github_Webhook source="http:github-dev-token" eventtype="GitHub::Push" sourcetype="json_ae_git-webhook"
| rename repository.name as RepoName
| spath path=commits{} output=commitscollection
| mvexpand commitscollection
| fields _time RepoName commitscollection
| spath input=commitscollection
| table RepoName id added{} modified{} removed{} author.name author.email message



| spath path=commits{} output=commitscollection --> Thanks to all the responders. This helps in getting the commits from array

Next challenge is, if you pull the data for all the other fields in the same approach, each of those values cannot be mapped with each other. To address this, we should use mvexpand to split them into separate array events

Once the array is split into separate events, now, we will use the same logic to split the data into events.

Hope this helps.

View solution in original post

0 Karma
Reply
Solved! Jump to solution
Solution

premrajvs
Explorer
‎08-26-2024 07:26 AM

Thank you for all the inputs. Here is the final query

index=Github_Webhook source="http:github-dev-token" eventtype="GitHub::Push" sourcetype="json_ae_git-webhook"
| rename repository.name as RepoName
| spath path=commits{} output=commitscollection
| mvexpand commitscollection
| fields _time RepoName commitscollection
| spath input=commitscollection
| table RepoName id added{} modified{} removed{} author.name author.email message



| spath path=commits{} output=commitscollection --> Thanks to all the responders. This helps in getting the commits from array

Next challenge is, if you pull the data for all the other fields in the same approach, each of those values cannot be mapped with each other. To address this, we should use mvexpand to split them into separate array events

Once the array is split into separate events, now, we will use the same logic to split the data into events.

Hope this helps.

0 Karma
Reply
Solved! Jump to solution

yuanliu
SplunkTrust
SplunkTrust
‎08-23-2024 08:03 PM

In addition to mistaken path notation ({} for array) as @PickleRick , you also do not need an extra spath if all you want is a multivalued field named commit_id.  Splunk should have taken care of extraction.

 

index=XXXXX source="http:github-dev-token" eventtype="GitHub::Push" sourcetype="json_ae_git-webhook"
| rename commits{}.id as commit_id

 

This is a full emulation

 

| makeresults format=json data="[{
\"ref\":\"refs/heads/Dev\",
\"before\":\"d53e9b3cb6cde4253e05019295a840d394a7bcb0\",
\"after\":\"34c07bcbf557413cf42b601c1794c87db8c321d1\",
\"commits\":[
{
\"id\":\"a5c816a817d06e592d2b70cd8a088d1519f2d720\",
\"tree_id\":\"15e930e14d4c62aae47a3c02c47eb24c65d11807\",
\"distinct\":false,
\"message\":\"rrrrrrrrrrrrrrrrrrrrrr\",
\"timestamp\":\"2024-08-12T12:00:04-05:00\",
\"url\":\"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/aaaaaaaaaaaa\",
\"author\":{
\"name\":\"aaaaaa aaaaaa\",
\"email\":\"101218171+aaaaaa@users.noreply.github.com\",
\"username\":\"aaaaaa\"},
\"committer\":{
\"name\":\"aaaaaa aaaaaa\",
\"email\":\"101218171+aaaaaa@users.noreply.github.com\",
\"username\":\"aaaaaa\"},
\"added\":[],
\"removed\":[],
\"modified\":[\"asdafasdad.json\"]},
{
\"id\":\"a3b3b6f728ccc0eb9113e7db723fbfc4ad220882\",
\"tree_id\":\"3586aeb0a33dc5e236cb266c948f83ff01320a9a\",
\"distinct\":false,
\"message\":\"xxxxxxxxxxxxxxxxxxx\",
\"timestamp\":\"2024-08-12T12:05:40-05:00\",
\"url\":\"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/a3b3b6f728ccc0eb9113e7db723fbfc4ad220...\",
\"author\":{
\"name\":\"aaaaaa aaaaaa\",
\"email\":\"101218171+aaaaaa@users.noreply.github.com\",
\"username\":\"aaaaaa\"},
\"committer\":{
\"name\":\"aaaaaa aaaaaa\",
\"email\":\"101218171+aaaaaa@users.noreply.github.com\",
\"username\":\"aaaaaa\"},
\"added\":[],
\"removed\":[],
\"modified\":[ \"sddddddf.json\"]},
{
\"id\":\"bdcd242d6854365ddfeae6b4f86cf7bc1766e028\",
\"tree_id\":\"8286c537f7dee57395f44875ddb8b2cdb7dd48b2\",
\"distinct\":false,
\"message\":\"Updating pipeline: pl_gwp_file_landing_check. Adding Sylvan Performance\",
\"timestamp\":\"2024-08-12T12:06:10-05:00\",
\"url\":\"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/bdcd242d6854365ddfeae6b4f86cf7bc1766e...\",
\"author\":{
\"name\":\"aaaaaa aaaaaa\",
\"email\":\"101218171+aaaaaa@users.noreply.github.com\",
\"username\":\"aaaaaa\"},
\"committer\":{
\"name\":\"aaaaaa aaaaaa\",
\"email\":\"101218171+aaaaaa@users.noreply.github.com\",
\"username\":\"aaaaaa\"},
\"added\":[],
\"removed\":[],
\"modified\":[ \"asadwefvdx.json\"]},
{
\"id\":\"108ebd4ff8ae9dd70e669e2ca49e293684d5c37a\",
\"tree_id\":\"5a6d71393611718b8576f8a63cdd34ce619f17dd\",
\"distinct\":false,
\"message\":\"asdrwerwq\",
\"timestamp\":\"2024-08-12T10:09:33-07:00\",
\"url\":\"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/108ebd4ff8ae9dd70e669e2ca49e293684d5c...\",
\"author\":{
\"name\":\"dfsd\",
\"email\":\"l.llllllllllll@aaaaaa.com\",
\"username\":\"aaaaaa\"},
\"committer\":{
\"name\":\"lllllllllllll\",
\"email\":\"l.llllllllllll@abc.com\",
\"username\":\"aaaaaa\"},
\"added\":[],
\"removed\":[],
\"modified\":[\"A.json\",\"A.json\",\"A.json\"]},{
\"id\":\"34c07bcbf557413cf42b601c1794c87db8c321d1\",
\"tree_id\":\"5a6d71393611718b8576f8a63cdd34ce619f17dd\",
\"distinct\":true,
\"message\":\"asadasd\",
\"timestamp\":\"2024-08-12T13:32:45-05:00\",
\"url\":\"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/34c07bcbf557413cf42b601c1794c87db8c32...\",
\"author\":{
\"name\":\"aaaaaa aaaaaa\",
\"email\":\"101218171+aaaaaa@users.noreply.github.com\",
\"username\":\"aaaaaa\"},
\"committer\":{
\"name\":\"GitasdjwqaikHubasdqw\",
\"email\":\"noreply@gitskcaskadahuqwdqbqwdqaw.com\",
\"username\":\"wdkcszjkcsebwdqwdfqwdawsldqodqw\"},
\"added\":[],
\"removed\":[],
\"modified\":[ \"a.json\", \"A1.json\", \"A1.json\"]}],
\"head_commit\":{
\"id\":\"34c07bcbf557413cf42b601c1794c87db8c321d1\",
\"tree_id\":\"5a6d71393611718b8576f8a63cdd34ce619f17dd\",
\"distinct\":true,
\"message\":\"sadwad from xxxxxxxxxxxxxxx/IH-5942-Pipeline-Change\n\nIh 5asdsazdapeline change\",
\"timestamp\":\"2024-08-12T13:32:45-05:00\",
\"url\":\"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/3weweeeeeeeee\",
\"author\":{
\"name\":\"askjas\",
\"email\":\"101218171+asfsfgwsrsd@users.noreply.github.com\",
\"username\":\"asdwasdcqwasfdc-qwgbhvcfawdqxaiwdaszxc\" },
\"committer\":{
\"name\":\"GsdzvcweditHuscwsab\",
\"email\":\"noreply@gitasdcwedhub.com\",
\"username\":\"wefczeb-fwefvdszlow\"},
\"added\":[],
\"removed\":[],
\"modified\":[\"zzzzzzz.json\",\"Azzzzz.json\",\"zzzz.json\" ]}}]"
| spath
``` the above emulates
index=XXXXX source="http:github-dev-token" eventtype="GitHub::Push" sourcetype="json_ae_git-webhook"
```
| rename commits{}.id as commit_id
| table commit_id

 

The output is

commit_id
a5c816a817d06e592d2b70cd8a088d1519f2d720
a3b3b6f728ccc0eb9113e7db723fbfc4ad220882
bdcd242d6854365ddfeae6b4f86cf7bc1766e028
108ebd4ff8ae9dd70e669e2ca49e293684d5c37a
34c07bcbf557413cf42b601c1794c87db8c321d1
Tags (1)
Reply
Solved! Jump to solution

PickleRick
SplunkTrust
SplunkTrust
‎08-23-2024 03:15 PM

Assuming you wanted to say

path=commits{}.id

it seems to work for me.

| makeresults 
| eval _raw="{
\"ref\":\"refs/heads/Dev\",
\"before\":\"d53e9b3cb6cde4253e05019295a840d394a7bcb0\",
\"after\":\"34c07bcbf557413cf42b601c1794c87db8c321d1\",
\"commits\":[
{
\"id\":\"a5c816a817d06e592d2b70cd8a088d1519f2d720\",
\"tree_id\":\"15e930e14d4c62aae47a3c02c47eb24c65d11807\",
\"distinct\":false,
\"message\":\"rrrrrrrrrrrrrrrrrrrrrr\",
\"timestamp\":\"2024-08-12T12:00:04-05:00\",
\"url\":\"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/aaaaaaaaaaaa\",
\"author\":{
\"name\":\"aaaaaa aaaaaa\",
\"email\":\"101218171+aaaaaa@users.noreply.github.com\",
\"username\":\"aaaaaa\"},
\"committer\":{
\"name\":\"aaaaaa aaaaaa\",
\"email\":\"101218171+aaaaaa@users.noreply.github.com\",
\"username\":\"aaaaaa\"},
\"added\":[],
\"removed\":[],
\"modified\":[\"asdafasdad.json\"]},
{
\"id\":\"a3b3b6f728ccc0eb9113e7db723fbfc4ad220882\",
\"tree_id\":\"3586aeb0a33dc5e236cb266c948f83ff01320a9a\",
\"distinct\":false,
\"message\":\"xxxxxxxxxxxxxxxxxxx\",
\"timestamp\":\"2024-08-12T12:05:40-05:00\",
\"url\":\"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/a3b3b6f728ccc0eb9113e7db723fbfc4ad220...\",
\"author\":{
\"name\":\"aaaaaa aaaaaa\",
\"email\":\"101218171+aaaaaa@users.noreply.github.com\",
\"username\":\"aaaaaa\"},
\"committer\":{
\"name\":\"aaaaaa aaaaaa\",
\"email\":\"101218171+aaaaaa@users.noreply.github.com\",
\"username\":\"aaaaaa\"},
\"added\":[],
\"removed\":[],
\"modified\":[ \"sddddddf.json\"]},
{
\"id\":\"bdcd242d6854365ddfeae6b4f86cf7bc1766e028\",
\"tree_id\":\"8286c537f7dee57395f44875ddb8b2cdb7dd48b2\",
\"distinct\":false,
\"message\":\"Updating pipeline: pl_gwp_file_landing_check. Adding Sylvan Performance\",
\"timestamp\":\"2024-08-12T12:06:10-05:00\",
\"url\":\"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/bdcd242d6854365ddfeae6b4f86cf7bc1766e...\",
\"author\":{
\"name\":\"aaaaaa aaaaaa\",
\"email\":\"101218171+aaaaaa@users.noreply.github.com\",
\"username\":\"aaaaaa\"},
\"committer\":{
\"name\":\"aaaaaa aaaaaa\",
\"email\":\"101218171+aaaaaa@users.noreply.github.com\",
\"username\":\"aaaaaa\"},
\"added\":[],
\"removed\":[],
\"modified\":[ \"asadwefvdx.json\"]},
{
\"id\":\"108ebd4ff8ae9dd70e669e2ca49e293684d5c37a\",
\"tree_id\":\"5a6d71393611718b8576f8a63cdd34ce619f17dd\",
\"distinct\":false,
\"message\":\"asdrwerwq\",
\"timestamp\":\"2024-08-12T10:09:33-07:00\",
\"url\":\"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/108ebd4ff8ae9dd70e669e2ca49e293684d5c...\",
\"author\":{
\"name\":\"dfsd\",
\"email\":\"l.llllllllllll@aaaaaa.com\",
\"username\":\"aaaaaa\"},
\"committer\":{
\"name\":\"lllllllllllll\",
\"email\":\"l.llllllllllll@abc.com\",
\"username\":\"aaaaaa\"},
\"added\":[],
\"removed\":[],
\"modified\":[\"A.json\",\"A.json\",\"A.json\"]},{
\"id\":\"34c07bcbf557413cf42b601c1794c87db8c321d1\",
\"tree_id\":\"5a6d71393611718b8576f8a63cdd34ce619f17dd\",
\"distinct\":true,
\"message\":\"asadasd\",
\"timestamp\":\"2024-08-12T13:32:45-05:00\",
\"url\":\"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/34c07bcbf557413cf42b601c1794c87db8c32...\",
\"author\":{
\"name\":\"aaaaaa aaaaaa\",
\"email\":\"101218171+aaaaaa@users.noreply.github.com\",
\"username\":\"aaaaaa\"},
\"committer\":{
\"name\":\"GitasdjwqaikHubasdqw\",
\"email\":\"noreply@gitskcaskadahuqwdqbqwdqaw.com\",
\"username\":\"wdkcszjkcsebwdqwdfqwdawsldqodqw\"},
\"added\":[],
\"removed\":[],
\"modified\":[ \"a.json\", \"A1.json\", \"A1.json\"]}],
\"head_commit\":{
\"id\":\"34c07bcbf557413cf42b601c1794c87db8c321d1\",
\"tree_id\":\"5a6d71393611718b8576f8a63cdd34ce619f17dd\",
\"distinct\":true,
\"message\":\"sadwad from xxxxxxxxxxxxxxx/IH-5942-Pipeline-Change\n\nIh 5asdsazdapeline change\",
\"timestamp\":\"2024-08-12T13:32:45-05:00\",
\"url\":\"https://github.com/xxxxxxxxxxxxxxx/AzureWorkload_A00008/commit/3weweeeeeeeee\",
\"author\":{
\"name\":\"askjas\",
\"email\":\"101218171+asfsfgwsrsd@users.noreply.github.com\",
\"username\":\"asdwasdcqwasfdc-qwgbhvcfawdqxaiwdaszxc\" },
\"committer\":{
\"name\":\"GsdzvcweditHuscwsab\",
\"email\":\"noreply@gitasdcwedhub.com\",
\"username\":\"wefczeb-fwefvdszlow\"},
\"added\":[],
\"removed\":[],
\"modified\":[\"zzzzzzz.json\",\"Azzzzz.json\",\"zzzz.json\" ]}}" 
| spath output=commit_id path=commits{}.id | table commit_id

 shows 5 values

Splunk 9.3.0

Reply
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

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

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...