Splunk Search

Compare multivalues from different rows

farhad
Engager

In my search i have 2 rows, column specifying the week and the other column a multi-value field of EventIDs. I need to compare one row multievalue field to another row multi value field. And Output the different event values from these 2 rows.

______________________________

my search
| stats values(EventID) by week

Result:

weekvalues(EventID)
This week
4624
4625
4627
4634
4647
4648
4656
4658
4661
4663
4664
4670
4672
4673
4674
4688
4689
4690
4692
4693
4698
Previous Week
4624
4625
4627
4634
4647
4648
4656
4658
4661
4663
4664
4670
4672
4673
4674
4688
4689
4690
4692
4693
4698
4702
4720
4722
4724
4725

 

Now i desire to compare "this week" event values with "previous week" event values and table values not seen in both weeks. Any suggestions is much appreciated, thanks!

Labels (1)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@farhad 

Can you please try this?

YOUR_SEARCH
| stats values(week) as weeks by EventID 
| eval status = case(mvcount(weeks)==2,"Available In Both",weeks="This week","Available In This Week",weeks="Previous Week","Available In Previous Week") 
| stats values(EventID) as EventIDs by status

 

My Sample Search :

 

| makeresults 
| eval _raw="week	EventID
This week	4624,4625,4627,4634,4647,4648,4656,4658,4661,4663,4664,4670,4672,4673,4674,4688,4689,4690,4692,4693,4698,100,102,103
Previous Week	4624,4625,4627,4634,4647,4648,4656,4658,4661,4663,4664,4670,4672,4673,4674,4688,4689,4690,4692,4693,4698,4702,4720,4722,4724,4725
" 
| multikv forceheader=1 
| eval EventID=split(EventID,",") 
| mvexpand EventID 
| table week EventID 
| rename comment as "Upto now is for sample data only"
| stats values(week) as weeks by EventID 
| eval status = case(mvcount(weeks)==2,"Available In Both",weeks="This week","Available In This Week",weeks="Previous Week","Available In Previous Week") 
| stats values(EventID) as EventIDs by status

 

Screenshot 2023-07-04 at 8.58.03 PM.png

 

I hope this will help you.

 

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

View solution in original post

kamlesh_vaghela
SplunkTrust
SplunkTrust

@farhad 

Can you please try this?

YOUR_SEARCH
| stats values(week) as weeks by EventID 
| eval status = case(mvcount(weeks)==2,"Available In Both",weeks="This week","Available In This Week",weeks="Previous Week","Available In Previous Week") 
| stats values(EventID) as EventIDs by status

 

My Sample Search :

 

| makeresults 
| eval _raw="week	EventID
This week	4624,4625,4627,4634,4647,4648,4656,4658,4661,4663,4664,4670,4672,4673,4674,4688,4689,4690,4692,4693,4698,100,102,103
Previous Week	4624,4625,4627,4634,4647,4648,4656,4658,4661,4663,4664,4670,4672,4673,4674,4688,4689,4690,4692,4693,4698,4702,4720,4722,4724,4725
" 
| multikv forceheader=1 
| eval EventID=split(EventID,",") 
| mvexpand EventID 
| table week EventID 
| rename comment as "Upto now is for sample data only"
| stats values(week) as weeks by EventID 
| eval status = case(mvcount(weeks)==2,"Available In Both",weeks="This week","Available In This Week",weeks="Previous Week","Available In Previous Week") 
| stats values(EventID) as EventIDs by status

 

Screenshot 2023-07-04 at 8.58.03 PM.png

 

I hope this will help you.

 

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

farhad
Engager

Work as intended, great thanks!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You have to use transpose to get the values into the same event so you can compare them; can then transpose again to return them to different event (if that's what you require).

| transpose 0 header_field=week column_name=events
| foreach mode=multivalue "Previous Week"
    [| eval missing=if(in(<<ITEM>>,'This week'),missing,mvappend(missing,<<ITEM>>))]
| foreach mode=multivalue "This week"
    [| eval new=if(in(<<ITEM>>,'Previous Week'),new,mvappend(new,<<ITEM>>))]
| transpose 0 header_field=events column_name=week
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 ...