topic Re: How do I get unique values of different types of events without duplicates? in Splunk Search

How do I get unique values of different types of events without duplicates?

bollam — Tue, 29 Sep 2020 22:18:17 GMT

Hello,

I have got events with two different types: Type=First and type=Second

I would like to get the consolidated(with unique tags) from both the types based on the following conditions.

If both types have a values of tag, then on priority, it should consider value of tag from the type=First and ignore the tag value from type=Second, even if it has value or not.

ii) If tag has no value — i.e., "" in type=First and tag of type=Second has the value — then it considers the value from type=Second for the same id of type=First.

iii) if both the types have no value in tag, then we can ignore this.

Note: the values of id_first and id_second has same values but the naming convention is different.

    time=1544071583425 type="First" version="2.0.5" status="OK"  id_first="210468"  user="abc" tag="some"
    time=1544071583425 type="First" version="2.0.5" status="OK"  id_first="210469"  user="xyz" tag=""
    time=1544071583424 type="First" version="2.0.5" status="OK"  id_first="210470"  user="wow" tag=""
    time=1544071583424 type="First" version="2.0.5" status="OK"  id_first="210483"  user="rez" 
    time=1544071583424 type="First" version="2.0.5" status="OK"  id_first="210487"  user="yov" tag="wrong"
    time=1544071583424 type="First" version="2.0.5" status="OK"  id_first="210491"  user="boh" tag=""


time=1544071583425 type="Second" version="2.0.5" status="OK"  id_second="210468"  user="abc" tag="some"
time=1544071583425 type="Second" version="2.0.5" status="OK"  id_second="210469"  user="xyz" tag="where"
time=1544071583424 type="Second" version="2.0.5" status="OK"  id_second="210470"  user="wow" tag="fishy
time=1544071583424 type="Second" version="2.0.5" status="OK"  id_second="210483"  user="rez" 
time=1544071583424 type="Second" version="2.0.5" status="OK"  id_second="210487"  user="yov" tag="wrong"
time=1544071583424 type="Second" version="2.0.5" status="OK"  id_second="210491"  user="boh" tag="great"

output:

tag              id
some         210469
where       210469
fishy       210470
wrong       210483
great       210487

Re: How do I get unique values of different types of events without duplicates?

renjith_nair — Thu, 06 Dec 2018 11:05:23 GMT

@bollam,

According to your description, there is a mismatch in your output. For e.g. 210468 should have tag as some and 210491 should have great.
Based on this assumption , try this

"your search"
|eval ID=coalesce(id_first,id_second)
|stats values(eval(if(type="First",tag,null()))) as _tag1,values(eval(if(type="Second",tag,null()))) as _tag2 by ID
|eval tag=if(_tag1!="",_tag1,_tag2)

Re: How do I get unique values of different types of events without duplicates?

bollam — Tue, 29 Sep 2020 22:18:36 GMT

@renjith, No, value of 210491 does not have in tag in the type=First but for id_second=210491 has the value in tag for type=Second. In this case tag should be considered as great since it's not present in the type=First.

When the ran the query you provided, I'm not getting the values of tags but it's just displaying values as okay to all the events of tag.

index=sai_core sourcetype=firstsecond_log
| eval ID=coalesce(id_first,id_second)
| stats values(eval(if(type="First",tag,null()))) as _tag1,values(eval(if(type="Second",tag,null()))) as _tag2 by ID
| eval tag=if(_tag1!="", _tag1, _tag2)

I do not understand where is the value "okay" is coming. There is no word "okay" exists in the event.
How can I view the values of _tag1 and _tag2?

ID tag
210468 okay
210469 okay
210470 okay
210483 okay
210487 okay
210491 okay

Re: How do I get unique values of different types of events without duplicates?

renjith_nair — Thu, 06 Dec 2018 18:20:16 GMT

@bollam,
Thats strange, can you try this and verify the values of of tag1 and tag2? If this is fine, then remove tag1 & tag2 by using |fields - tag1,tag2

index=sai_core sourcetype=firstsecond_log 
| eval ID=coalesce(id_first,id_second) 
| stats values(eval(if(type="First",tag,null()))) as tag1,values(eval(if(type="Second",tag,null()))) as tag2 by ID
| eval tag=if(tag1!="", tag1, tag2)