Getting Data In

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

jpringle03
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

sundareshr
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

somesoni2
SplunkTrust
SplunkTrust

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

jpringle03
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

somesoni2
SplunkTrust
SplunkTrust

Give the updated answer a try.

0 Karma

jpringle03
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

somesoni2
SplunkTrust
SplunkTrust

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

jpringle03
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

sundareshr
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

jpringle03
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

sundareshr
Legend

You got it! Edited original post to fix typos.

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