topic Re: advanced json handling in Getting Data In

advanced json handling

awmorris — Wed, 19 Jun 2019 18:23:40 GMT

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

Re: advanced json handling

awmorris — Wed, 19 Jun 2019 18:32:21 GMT

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

Re: advanced json handling

FrankVl — Wed, 19 Jun 2019 18:36:58 GMT

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?

Re: advanced json handling

awmorris — Wed, 19 Jun 2019 18:42:10 GMT

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

Re: advanced json handling

FrankVl — Wed, 19 Jun 2019 18:50:51 GMT

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

Re: advanced json handling

woodcock — Wed, 19 Jun 2019 21:58:27 GMT

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

Re: advanced json handling

woodcock — Wed, 19 Jun 2019 22:42:40 GMT

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.

Re: advanced json handling

woodcock — Wed, 19 Jun 2019 22:44:01 GMT

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$.