advanced json handling

[awmorris]

i have a simplified data set that shows users and the number of times they have been seen using a given computer. I want to use this to GUESS their primary computer. Simple, right? I think i'm missing something then... Here's my data set

{
    "user" : "user1",
    "h" : {
        "hist" : [
            [
                "computer1",
                76
            ]
        ]
    }
}
{
    "user" : "user2",
    "h" : {
        "hist" : [
            [
                "computer2",
                4
            ],
            [
                "computer3",
                80
            ]
        ]
    }
}
{
    "user" : "user3",
    "h" : {
        "hist" : [
            [
                "computer4",
                213
            ],
            [
                "computer5",
                83
            ]
        ]
    }
}

Results should be like:
user1 : computer1
user2 : computer3
user3 : computer4

[woodcock · Accepted solution]

Try this:

|makeresults
|eval raw="{\"user\":\"user1\",\"h\":{\"hist\":[[\"computer1\",76]]}} {\"user\":\"user2\",\"h\":{\"hist\":[[\"computer2\",4],[\"computer3\",80]]}} {\"user\":\"user3\",\"h\":{\"hist\":[[\"computer4\",213],[\"computer5\",83]]}}"
| makemv raw
| mvexpand raw
| rename raw AS _raw
| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"
| rex mode=sed "s/\",(\d+)/\"=\1/g s/\":\"/\"=\"/ s/\"//g"
| kv
| fields - _*
| untable user computer logins
| stats sum(logins) AS logins BY computer user
| sort 0 - logins
| stats list(*) AS * BY user

You probably should change that last line to | stats list(*) As * BY computer but that is more boring for the dataset that you provided.

[woodcock]

This also assumes that your events are true JSON (no whitespace) like my events are. If you really have all of that formatting whitespace, then have whomoever is sending it into Splunk trim out that fat and save you BIG BUCK$.

[woodcock]

You might consider the seckit utilities on Splunkbase. These do this and much more.

[FrankVl]

What does this data look like in Splunk, have you (or has splunk automatically) done any field extractions yet, or do you need help completely from scratch?
Are the separate users in separate events? Or is this all 1 big event?

[awmorris]

in the sample set above, splunk would have 3 events. It recognizes 2 fields: user AND h.hist{}{}

[FrankVl]

And h.hist{}{} is a multivalued field? What do the values in there look like?

[awmorris]

It is worth noting that the history is variable. Some users have a single machine, some have 2 machines, others have 15....