Getting Data In

Can you help me with my search results visualization column chart issue?

haoban
Path Finder

The original data is json format
alt text

Search Language is as follows:

I successfully extracted the data and displayed as a table. But why did the visualization fail? I just want for each "group" to have a bar stacked. Seems that the issue is with the "Statistics": (1), How do I split it into 7 lines?

> sourcetype="cisco:opendns:api" |
> rename organizations{}.name AS group,
> organizations{}.requests.allSecurity
> AS allSecurity,
> organizations{}.requests.blocked AS
> blocked,
> organizations{}.requests.contained AS
> contained,
> organizations{}.requests.prevented AS
> prevented,
> organizations{}.requests.security AS
> security,
> organizations{}.requests.total AS
> total | table group, allSecurity,
> blocked, contained, prevented,
> security, total | head 1

alt text

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

I'm guessing the charting fails due to multivalued fields you've in your event (there are multiple groups in a single event, the numerical values are treated as string in multivalued field and thus can't be plotted). You'd need expand them into single value fields/row and then plot. Give this a try

sourcetype="cisco:opendns:api" 
|  rename organizations{}.name AS group,  organizations{}.requests.*  AS *
| table group, allSecurity,  blocked, contained, prevented,  security, total | head 1
| eval temp=mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(group,allSecurity,"#"), blocked,"#"),contained,"#"),prevented,"#"),security,"#"),total,"#")
| table temp 
| mvexpand temp
| rex field=temp "(?<group>[^#]+)#(?<allSecurity>[^#]+)#(?<blocked>[^#]+)#(?<contained>[^#]+)#(?<prevented>[^#]+)#(?<security>[^#]+)#(?<total>[^#]+)"
| fields -temp

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

I'm guessing the charting fails due to multivalued fields you've in your event (there are multiple groups in a single event, the numerical values are treated as string in multivalued field and thus can't be plotted). You'd need expand them into single value fields/row and then plot. Give this a try

sourcetype="cisco:opendns:api" 
|  rename organizations{}.name AS group,  organizations{}.requests.*  AS *
| table group, allSecurity,  blocked, contained, prevented,  security, total | head 1
| eval temp=mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(group,allSecurity,"#"), blocked,"#"),contained,"#"),prevented,"#"),security,"#"),total,"#")
| table temp 
| mvexpand temp
| rex field=temp "(?<group>[^#]+)#(?<allSecurity>[^#]+)#(?<blocked>[^#]+)#(?<contained>[^#]+)#(?<prevented>[^#]+)#(?<security>[^#]+)#(?<total>[^#]+)"
| fields -temp
0 Karma

haoban
Path Finder

Thanks somesoni2. It works, but why it's so complicated!
following is the final search language:

sourcetype="cisco:opendns:api" |
rename organizations{}.name AS group,
organizations{}.requests.allSecurity
AS allSecurity,
organizations{}.requests.blocked AS
blocked,
organizations{}.requests.contained AS
contained,
organizations{}.requests.prevented AS
prevented,
organizations{}.requests.security AS
security,
organizations{}.requests.total AS
total | table group, allSecurity,
blocked, contained, prevented,
security, total | head 1 | eval
temp=mvzip(mvzip(mvzip(mvzip(mvzip(mvzip(group,allSecurity,"#"),
blocked,"#"),contained,"#"),prevented,"#"),security,"#"),total,"#")
| table temp | mvexpand temp | rex
field=temp
"(?[^#]+)#(?[^#]+)#(?[^#]+)#(?[^#]+)#(?[^#]+)#(?[^#]+)#(?[^#]+)" | fields group, allSecurity, blocked,
contained, prevented, security

0 Karma

somesoni2
SplunkTrust
SplunkTrust

It's because of how your data is logged. Ideally all data points (a unique combination of fields group, allSecurity, blocked, contained, prevented, security) should be available as separate set, e.g. in individual rows. That way you'd be able to plot them better. Since you've json array with your data, we need additional code to split them into separate rows.

0 Karma

haoban
Path Finder

You are correct! I reviewed my other data also in json-format but not as json-array, only use "rename" can makes it work fine. Seems I need spend more time on json and splunk extract data. Thanks again!

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...