Getting Data In

advanced json handling

Path Finder

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

1 Solution

Esteemed Legend

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.

View solution in original post

0 Karma

Esteemed Legend

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.

View solution in original post

0 Karma

Esteemed Legend

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

0 Karma

Esteemed Legend

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

0 Karma

Ultra Champion

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?

0 Karma

Path Finder

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

0 Karma

Ultra Champion

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

0 Karma

Path Finder

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

0 Karma