Getting Data In

advanced json handling

awmorris
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

woodcock
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

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

0 Karma

woodcock
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

woodcock
Esteemed Legend

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

0 Karma

FrankVl
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

awmorris
Path Finder

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

0 Karma

FrankVl
Ultra Champion

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

0 Karma

awmorris
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...