topic Re: How to write a query that will separate browser sections in a JSON array into separate events? in Getting Data In

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

jpringle03 — Wed, 12 Oct 2016 14:35:59 GMT

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?

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

sundareshr — Wed, 12 Oct 2016 16:48:28 GMT

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

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

somesoni2 — Wed, 12 Oct 2016 17:44:21 GMT

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)

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

jpringle03 — Wed, 12 Oct 2016 18:00:59 GMT

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

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

jpringle03 — Wed, 12 Oct 2016 18:06:47 GMT

Thanks for the suggestion!

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

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

somesoni2 — Wed, 12 Oct 2016 18:41:51 GMT

Give the updated answer a try.

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

sundareshr — Wed, 12 Oct 2016 18:51:37 GMT

You got it! Edited original post to fix typos.

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

jpringle03 — Wed, 12 Oct 2016 18:57:20 GMT

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)

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

somesoni2 — Wed, 12 Oct 2016 19:00:26 GMT

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)

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

jpringle03 — Wed, 12 Oct 2016 19:18:26 GMT

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.