Splunk Search

Prevent mvexpand to show duplicate events when searching all events

panulpet
Loves-to-Learn

Related to this question: https://answers.splunk.com/answers/807988/splunk-search-show-results-from-json.html

I basically got the search working when I search field "yyy" and it's corresponding value "yy-564" from JSON. That was solved and Splunk finds the correct event. But now my clients are complaining when they are searching for all events (field="" value=""), they see duplicate events due to that mvexpand command. They think it's confusing to see several events generated from one. Is there any solutions for preventing mvexpand not to show "dublicate" events on table?

Thanks
-Pete

0 Karma

to4kawa
Ultra Champion
<search>
<query>$systems$ $status$| rex "(?&lt;json&gt;\{.+)" 
| spath input=json 
| fields - _* json
| rename parameters{}.* as mv_* 
| rename error{}.* as * 
| eval counter = mvrange(0,mvcount(mv_field)) 
| streamstats count as session 
| stats values(*) as * by counter session 
| foreach mv_* 
[ eval &lt;&lt;FIELD&gt;&gt; = mvindex('&lt;&lt;FIELD&gt;&gt;',counter)] 
| rename mv_* as * 
| fields - counter search securityProhibition session 
| search field="$dest$" value="$dest_value$" service_id="$service$" request_id="$reqid$" 
| lookup omatrafi_qa.csv service_id OUTPUT Palvelut 
| table event_timestamp request_id service_id Palvelut system_id successful code message session_id 
| rename event_timestamp as "Tapahtuman aikaleima" request_id as "Kutsutunnus" service_id as "Palvelutunnus" Palvelut as "Palvelutunnuksen selite" system_id as "Järjestelmätunnus" successful as "Kutsu onnistunut" code as "Error_Koodi" message as "Error_Viesti" session_id as "sessio"</query>

I remove _raw and json(kohteet)

0 Karma

panulpet
Loves-to-Learn

The result of that query is so confusing 🙂 I would like to have one event per row in table so that users can see the results...

0 Karma

panulpet
Loves-to-Learn

successful comes from Calculated Fields: if(isnotnull(ErrorCode), 0, 1) - so it's 1 or 0

0 Karma

to4kawa
Ultra Champion

event? that's field, isn't it?
do you see the events?

0 Karma

panulpet
Loves-to-Learn

I meant event as whole JSON message what is seen.

0 Karma

panulpet
Loves-to-Learn

As "mvexpand" Expands the values of a multivalue field into separate events.. When users fill the input search fields only matching event(s) is seen, but when searching with the asterisk we can see as many duplicate events as there is different fields. That's confusing...

0 Karma

to4kawa
Ultra Champion

OK.
What's your query OR dashboard?
If you want not to use mvexpand, please provide logs and query.

0 Karma

panulpet
Loves-to-Learn

Example JSON can be found from my earlier post - url is above:

     <search>
          <query>$systems$ | rex "(?&lt;json&gt;\{.+)" 
                  | spath input=json  
                  | fields - json | rename parameters{}.* as *
                  | eval fieldValue=mvzip(field,value) |eval kohteet=fieldValue
                  | mvexpand fieldValue 
                  | eval fieldValue=split(fieldValue,",") 
                  | eval field=mvindex(fieldValue,0) 
                  | eval value=mvindex(fieldValue,1) 
                  | fields - fieldValue search securityProhibition   |  search field="$dest$" value="$dest_value$" $status$ service_id="$service$" request_id="$reqid$" |eval myservice_id=service_id | lookup omatrafi_qa.csv service_id AS service_id OUTPUT service_id Palvelut | table event_timestamp request_id myservice_id Palvelut system_id kohteet successful error{}.code error{}.message _raw session_id  | rename event_timestamp as "Tapahtuman aikaleima" request_id as "Kutsutunnus" myservice_id as "Palvelutunnus" Palvelut as "Palvelutunnuksen selite" system_id as "Järjestelmätunnus" kohteet as "Kohteet" successful as "Kutsu onnistunut" error{}.code as "Error_Koodi" error{}.message as "Error_Viesti" _raw as "Raaka_data" session_id as "sessio"
          </query>
0 Karma

to4kawa
Ultra Champion

Is _$systems$_dropdown?
Do you need | eval kohteet = fieldValue ?
With kohteet, there is a problem with mvexpand.

please provide sample log. I make query.

0 Karma

panulpet
Loves-to-Learn

"Do you need | eval kohteet = fieldValue ?" Maybe not, but I just saved that value for later use.. I got it working like that 🙂

0 Karma

panulpet
Loves-to-Learn

Even with a bit simpler query I get the same results...

index=<my_index> | rex "(?<json>\{.+)" 
                  | spath input=json  
                  | fields - json | rename parameters{}.* as *
                  | eval fieldValue=mvzip(field,value)
                  | mvexpand fieldValue 
                  | eval fieldValue=split(fieldValue,",") 
                  | eval field=mvindex(fieldValue,0) 
                  | eval value=mvindex(fieldValue,1) 
                  | fields - fieldValue search securityProhibition   |  search field="*" value="*" service_id="*" request_id="*" | table index event_timestamp request_id myservice_id Palvelut system_id  successful error{}.code error{}.message _raw
0 Karma

panulpet
Loves-to-Learn

JSON is here:

{
"event_timestamp" : "2020-03-03 T 12:56:54 +0200",
"file_timestamp" : "",
"username" : "xxxx",
"session_id" : "F23AA957F1A494C12F2B21B5A7533FF3",
"request_id" : "74b9cf97-934c-41cb-b81e-1152f51e28b7",
"register_id" : [ ],
"system_id" : "ASDFG",
"environment" : "LINUX",
"service_id" : "12355",
"parameters" : [ {
"field" : "xxx",
"value" : "xx-123",
"search" : false,
"securityProhibition" : false
}, {
"field" : "yyy",
"value" : "yy-564",
"search" : false,
"securityProhibition" : false
}, {
"field" : "zzz",
"value" : "1234433222",
"search" : false,
"securityProhibition" : false
}, {
"field" : "vvv",
"value" : "www.google.com",
"search" : false,
"securityProhibition" : false
}, {
"field" : "qqq",
"value" : "qwert",
"search" : false,
"securityProhibition" : false
} ],
"info" : null,
"error" : [ {
"code" : "202",
"message" : "General Error"
} ],
"schema_version" : "1.0"
};

0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...