How to extract JSON data based on a specific value, and why Splunk reordering my JSON data generated from a script at index-time?

[Arismore]

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

[javiergn · Accepted solution]

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

[Arismore]

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

[Arismore]

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.