topic Re: How to calculate the difference between two fields that contain a list in Splunk Search

How to calculate the difference between two fields that contain a list

mahbs — Tue, 29 Sep 2020 17:43:11 GMT

Hi,

I have a question regarding calculating the difference between two fields that are of a list type.

Here is my query:

index=indexsourcetype=csv source=src1 host=host1 | stats list(field1) AS F1list(field2) AS F2 list(field3) AS F3 list(field4) AS F4 | join F1 [search index=index sourcetype=csv source=src2 host=host2| stats list(f_1) AS F1 list(f_2) AS f2 list(f_3) AS f3 list(f_4) AS f4 | rename f_1 as F1] |eval DIFF= f2- f3 | sort by F1asc

Now, if i have a list field that has 2 values:
0
0

and I want to subtract each of those values with values form another field, how would I do that? Or is it possible to do so?

Thanks

Re: How to calculate the difference between two fields that contain a list

mayurr98 — Sun, 14 Jan 2018 18:43:10 GMT

hey @mahbs

If you have f1,f2,f3, and f4 from source1 in one event and same for source2 then you can use stats

index=index sourcetype=csv source=src1 host=host1 | stats count by field1 field2 field3 field4 | join field1 [search index=index sourcetype=csv source=src2 host=host2| stats count by f_1 f_2 f_3 f_4 | rename f_1 as field1 ] | eval difference=f2-f_3 | sort limit=0 field1 | table <the_fields_you_want_to_Show>

If you want to stick to the list then try this

index=index sourcetype=csv source=src1 host=host1 
| stats list(field1) AS F1 list(field2) AS F2 list(field3) AS F3 list(field4) AS F4 
| eval source1= mvzip(F1,mvzip(F2,mvzip(F3,F4))) 
| mvexpand source1 
| rex field=source1 "(?<F1>\d+),(?<F2>\d+),(?<F3>\d+),(?<F4>\d+)" 
| join F1 
    [ search index=index sourcetype=csv source=src2 host=host2 
    | stats list(f_1) AS f_1 list(f_2) AS f_2 list(f_3) AS f_3 list(f_4) AS f_4 
    | eval source2=mvzip(f_1,mvzip(f_2,mvzip(f_3,f_4))) 
    | mvexpand source2 
    | rex field=source2 "(?<f_1>\d+),(?<f_2>\d+),(?<f_3>\d+),(?<f_4>\d+)" 
    | rename f_1 as F1] 
| eval diff=F2-f_3 
| sort limit=0 F1 
| table <the_fields_you_want_to_Show>

Refer this doc for more :
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Mvexpand

Let me know if this helps you!

Re: How to calculate the difference between two fields that contain a list

mahbs — Mon, 15 Jan 2018 10:03:03 GMT

Hi, there's a slight issue, the mvzip command doesn't accept more than 2 arguments

Re: How to calculate the difference between two fields that contain a list

mahbs — Mon, 15 Jan 2018 10:12:26 GMT

Also, why do we limit it to 0? Should we not create new events for all F1 data ?

Re: How to calculate the difference between two fields that contain a list

mayurr98 — Mon, 15 Jan 2018 10:23:07 GMT

hey sorry I have changed my query so that mvzip can accept all the arguments.

for limit=0 here is a description
Specify the number of results to sort. If no count is specified, the default limit of 10000 is used. If "0" is specified, all results are returned.

I hope this solves your query!

Re: How to calculate the difference between two fields that contain a list

mahbs — Mon, 15 Jan 2018 10:37:30 GMT

Oh I see, and just finally, will this query basically do what's mentioned in the link you provided? I.e. create new events with the same F1 number but different data for the other fields right?

Re: How to calculate the difference between two fields that contain a list

mahbs — Mon, 15 Jan 2018 10:51:36 GMT

@mayurr98,

Unfortunately, it's still not working. It's not creating new events with the same value for f1 where there are multiple values for a field.

Re: How to calculate the difference between two fields that contain a list

mayurr98 — Mon, 15 Jan 2018 12:00:32 GMT

check the final query. I have updated my answer pls have a look at it and let me know .
I think this query should work now!

 index=index sourcetype=csv source=src1 host=host1 
 | stats list(field1) AS F1 list(field2) AS F2 list(field3) AS F3 list(field4) AS F4 
 | eval source1= mvzip(F1,mvzip(F2,mvzip(F3,F4))) 
 | mvexpand source1 
 | rex field=source1 "(?<F1>\d+),(?<F2>\d+),(?<F3>\d+),(?<F4>\d+)" 
 | join F1 
     [ search index=index sourcetype=csv source=src2 host=host2 
     | stats list(f_1) AS f_1 list(f_2) AS f_2 list(f_3) AS f_3 list(f_4) AS f_4 
     | eval source2=mvzip(f_1,mvzip(f_2,mvzip(f_3,f_4))) 
     | mvexpand source2 
     | rex field=source2 "(?<f_1>\d+),(?<f_2>\d+),(?<f_3>\d+),(?<f_4>\d+)" 
     | rename f_1 as F1] 
 | eval diff=F2-f_3 
 | sort limit=0 F1 
 | table <the_fields_you_want_to_Show>

Re: How to calculate the difference between two fields that contain a list

mahbs — Mon, 15 Jan 2018 12:40:27 GMT

Hi,
Unfortunately, its still not working, It's not seperating multivalue fields into new records. I'm still getting fields with multiple values. Are you sure we're not missing this bit: rex field=fields "(?\d+),(?\d+)" | table _time alpha beta, i got it from http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Mvexpand.

Dont we need to create new fields?

Re: How to calculate the difference between two fields that contain a list

mayurr98 — Mon, 15 Jan 2018 13:47:40 GMT

we are creating new fields using rex command
so try this running the above search in pieces and see where you do not get results.
Try this run anywhere search

 | makeresults | eval F1="10 10 20 20 30 40 50" | makemv F1 | mvexpand F2 | appendcols [| makeresults | eval F2="2 5 5 10 15 20 25" | makemv F2  ] | appendcols [| makeresults | eval F3="2 5 5 10 15 20 25" | makemv F3 ] |  eval src1=mvzip(F1,mvzip(F2,F3)) | mvexpand src1 |  rex field=src1 "(?<f1>\d+),(?<f2>\d+),(?<f3>\d+)"

I have already tested this query and it is working fine!
Try running this query and see if you are getting results in a single row?

  index=index sourcetype=csv source=src1 host=host1 
      | stats list(field1) AS F1 list(field2) AS F2 list(field3) AS F3 list(field4) AS F4 
      | eval source1= mvzip(F1,mvzip(F2,mvzip(F3,F4))) 
      | mvexpand source1 
      | rex field=source1 "(?<F1>\d+),(?<F2>\d+),(?<F3>\d+),(?<F4>\d+)"

Thereafter try the second one and then join and see

I am sure you will get where you are going wrong. I think join is not joining fields as there is nothing common. Or rex field is not working as the rex i have written is only for numberical values do you have strings in your fields? then you need to written (?<F1>\w+) at appropriate place