Hello everybody,
I have JSON data that I generate from a Python script.
It looks like this:
{
"leaderboard": [
{
"action": {
"name": "total_sparxea_newdiagram",
"ranking": [
{
"id": "pierre@dupont.fr",
"name": "Pierre dupont",
"points": 7,
"position": 1
},
{
"id": "NouvelUtilisateur2",
"name": "C'est son nom!",
"points": 3,
"position": 2
},
]
}
},
{
"action": {
"name": "total_click",
"ranking": [
{
"id": "allo",
"name": "Mr Allo",
"points": 3,
"position": 1
},
{
"id": "pierre@dupont.fr",
"name": "pierre@dupont.fr",
"points": 0,
"position": 2
},
]
}
},
],
"timestamp": "2016-04-12 14:41:40.173000"
}
So I use it to make a kind of ranking of users per actions on a website.
My first question is:
How to extract some ranking information for a specific action? For example, I need to extract all ranking data for action.name = total_click
I tried to use spath, and used index in my spath to move to specific action. Here is my search :
source=source | spath output=action path=leaderboard{1}.action{}.name | spath output=player path=leaderboard{1}.action{}.ranking{}.id| spath output=position path=leaderboard{1}.action{}.ranking{}.position| spath output=points path=leaderboard{1}.action{}.ranking{}.points |replace "total_gsites_comment" with "Meilleur commentateur gsite" in action | table action,player,position,points
So you can see, I used index 1 to get data ranking from action 1. But my problem is that Splunk never indexes my JSON object in the same order, so I can't use indexes. Sometimes spath output=action path=leaderboard{1}.action{}.name
will be total_sparxea_newdiagram
and sometimes not. I checked my script output and my JSON is always the same.
So second question:
Why is Splunk reordering my json object at index-time?
I really need help and I don't understand. Thanks
I can't answer why Splunk is reordering your JSON, but I think I can help with your first question and hopefully solve your problem.
The following code should give you all the stats you need for action.name = "total_click":
source=source
| spath path=leaderboard{}
| mvexpand leaderboard{}
| spath input=leaderboard{}
| search action.name = "total_click"
EDIT to include final query by @Arismore:
source = "D:\\Logiciels\\Splunk\\etc\\apps\\gamification\\bin\\CupRanking.py"
| head 1
|spath path=leaderboard{}
| mvexpand leaderboard{}
| spath input=leaderboard{}
| search action.name = "total_gsites_comment"
| rename action.name as action_name,action.ranking{}.position as position,action.ranking{}.name as name,action.ranking{}.points as points
| table position,name,points
I can't answer why Splunk is reordering your JSON, but I think I can help with your first question and hopefully solve your problem.
The following code should give you all the stats you need for action.name = "total_click":
source=source
| spath path=leaderboard{}
| mvexpand leaderboard{}
| spath input=leaderboard{}
| search action.name = "total_click"
EDIT to include final query by @Arismore:
source = "D:\\Logiciels\\Splunk\\etc\\apps\\gamification\\bin\\CupRanking.py"
| head 1
|spath path=leaderboard{}
| mvexpand leaderboard{}
| spath input=leaderboard{}
| search action.name = "total_gsites_comment"
| rename action.name as action_name,action.ranking{}.position as position,action.ranking{}.name as name,action.ranking{}.points as points
| table position,name,points
Nvm, thanks Javiergn it works ! I accept your answer because it's more general, but here is my full query.
source = "D:\\Logiciels\\Splunk\\etc\\apps\\gamification\\bin\\CupRanking.py"
| head 1
|spath path=leaderboard{}
| mvexpand leaderboard{}
| spath input=leaderboard{}
| search action.name = "total_gsites_comment"
| rename action.name as action_name,action.ranking{}.position as position,action.ranking{}.name as name,action.ranking{}.points as points
| table position,name,points
Hello, thanks for your answer.
I tried your solution Javiergn, but it doesn't work.
I read the doc, mvexpand is a good idea. So i tried
source=source
| spath path=leaderboard{}
| mvexpand leaderboard{}
But the mvexpand return me 2 events ( for all my actions ), but my event it's still complete, it just duplicate my first event.