Getting Data In

How to write a query that will separate browser sections in a JSON array into separate events?

Path Finder

I'm currently trying to write a query that will let me separate the follow "browser" sections in this JSON array into separate events, preferably with the rest of the data in the output still being included.

The problem I'm running into at the moment is when I try to write a query for returning only the count for different versions of Chrome, in a situation where there is another browser present in the record, I'm getting "browser_version" field of the other browser being included as well.

An example of this query is:

index=security browser_family "browsers{}.browser_family"=Chrome*| spath "browsers{}.browser_version" | chart count by browsers{}.browser_version | sort browsers{}.browser_version`


 {
            "browsers": [{
                "browser_family": "Chrome",
                "browser_version": "51.0.2704.103",
                "flash_version": "22.0.0.0",
                "java_version": "uninstalled"
            },
            {
                "browser_family": "Safari",
                "browser_version": "9.1.1",
                "flash_version": "uninstalled",
                "java_version": "1.8.0.45",
                "last_used": 1474483713
            }],
            "email": "ejennings@example.com",
            "epkey": "EP18JX1A10AB102M2T2X",
            "model": "",
            "os_family": "Mac OS X",
            "os_version": "10.11.5",
            "type": "",
            "username": "ejennings"
        }

Any ideas of how I could accomplish this?

0 Karma
1 Solution

Legend

See if this helps

 index=security browser_family "browsers{}.browser_family"=Chrome*| spath "browsers{}.browser_version" | rename "browsers{}.*" AS * | eval z=mvzip(browser_family, mvzip(browser_version, mvzip(flash_version,  java_version))) | mvexpand z | rex field=z "(?<family>[^,]+),(?<version>[^,]+),(?<flash_version>[^,]+),(?<java_version>.*)" | fields family version flash_version java_version | where match(family, "Chrome") |  chart count values(*) as * by version | sort version

View solution in original post

Revered Legend

Give this a try

index=security browser_family "browsers{}.browser_family"=Chrome*
| spath "browsers{}.browser_version" 
| eval Browser_Version=mvzip('browsers{}.browser_family','browsers{}.browser_version',"#")
| stats count by Browser_Version | where match(Browser_Version,"Chrome") | replace "Chrome#*" with "*"  in Browser_Version

Updated

index=security browser_family "browsers{}.browser_family"=Chrome*
| spath "browsers{}.browser_version" 
| eval Browser_Version=mvzip('browsers{}.browser_family','browsers{}.browser_version',"#")
| stats count by Browser_Version | where match(Browser_Version,".*Chrome.*") 
| eval Browser_Version=mvindex(split(Browser_Version,"#"),-1)
0 Karma

Path Finder

Thanks for the suggestion!

I gave this a try but it still gave an output that contained over browsers:

alt text

0 Karma

Revered Legend

Give the updated answer a try.

0 Karma

Path Finder

Hey somesoni2,

Just tried and it stripped out the names from the browser_version field which is a win but is still showing the other browser versions (like Safari and AppleMail)

alt text

0 Karma

Revered Legend

Strange, the match was able to filter on my sample data. Give this a try then

 index=security browser_family "browsers{}.browser_family"=Chrome*
 | spath "browsers{}.browser_version" 
 | eval Browser_Version=mvzip('browsers{}.browser_family','browsers{}.browser_version',"#")
 | stats count by Browser_Version | where like(lower(Browser_Version),"%chrome%") 
 | eval Browser_Version=mvindex(split(Browser_Version,"#"),-1)
0 Karma

Path Finder

Still seems to be outputting the other browsers in the data. This was the issue I was having, it seems like it doesn't want to separate out those non-Chrome browsers.

0 Karma

Legend

See if this helps

 index=security browser_family "browsers{}.browser_family"=Chrome*| spath "browsers{}.browser_version" | rename "browsers{}.*" AS * | eval z=mvzip(browser_family, mvzip(browser_version, mvzip(flash_version,  java_version))) | mvexpand z | rex field=z "(?<family>[^,]+),(?<version>[^,]+),(?<flash_version>[^,]+),(?<java_version>.*)" | fields family version flash_version java_version | where match(family, "Chrome") |  chart count values(*) as * by version | sort version

View solution in original post

Path Finder

When trying this it it gave me:

Error in 'eval' command: The expression is malformed. An unexpected character is reached at ', mvzip(flash_version, java_version))))'.

I changed it a little and I think it might be doing what I want, but I need to look into it more. Here is what I have now:

index=security browser_family "browsers{}.browser_family"=Chrome*| spath "browsers{}.browser_version" | rename "browsers{}.*" AS * | eval z=mvzip(browser_family, mvzip(browser_version, mvzip(flash_version,  java_version))) | mvexpand z | rex field=z "(?<family>[^,]+),(?<version>[^,]+),(?<flash_version>[^,]+),(?<java_version>.*)" | fields family version flash_version java_version | where match(family, "Chrome") |  chart count values(*) as * by version | sort version

Legend

You got it! Edited original post to fix typos.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!