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!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...