topic Finding difference and percentage from 2 events withe same fields but difference index in Splunk Search

Finding difference and percentage from 2 events withe same fields but difference index

liondancer — Tue, 29 Sep 2020 19:40:09 GMT

I have this query that returns this:

Sample event in index=idx_A:

year=2018 month=04 day=10 hour=09 event_count=100 zone=zone_A

Sample event in index=idx_B:

year=2018 month=04 day=10 hour=09 event_count=110 zone=zone_B

Query:

index=idx_A OR index=idx_B | eval DateHour=year."-".month."-".day."-".hour  | chart values(event_count) over DateHour by zone

The resulting output

DateHour      | zone_A | zone_b
2018-04-10-09     100      110

I want to create another column called difference and percentage that would look something like this:

DateHour      | zone_A | zone_b | difference | percentage
2018-04-10-09     100      110      10            10%

The difference column would be the absolute value of event_count from zone_A and zone_B and percentage would be

difference / {event_count with zone=zone_A} * 100

Re: Finding difference and percentage from 2 events withe same fields but difference index

xpac — Tue, 22 May 2018 19:14:43 GMT

Try to append this:

| eval difference=abs(zone_A - zone_B)
| eval percentage=round(difference/zone_B*100), 2)

Make sure you actually want abs() (this will always return a positive value), and which zone you want to base your percentage on.

Hope that helps - if it does I'd be happy if you would upvote/accept this answer, so others could profit from it. 🙂

Re: Finding difference and percentage from 2 events withe same fields but difference index

liondancer — Tue, 29 Sep 2020 19:40:12 GMT

My query is

index=idx_A OR index=idx_B | eval DateHour=year."-".month."-".day."-".hour  | chart values(event_count) over DateHour by zone

will

eval difference=abs(zone_A - zone_B)

evaluate the difference between event_count of zone_A and event_count of zone_B?

Re: Finding difference and percentage from 2 events withe same fields but difference index

xpac — Tue, 29 Sep 2020 19:36:35 GMT

Yes, and it will make it a positive value, so if:
* zone_A is 50
* zone_B is 60
It will return 10, not -10. If you don't want this, just make it eval difference=zone_A - zone_B

Re: Finding difference and percentage from 2 events withe same fields but difference index

liondancer — Tue, 29 Sep 2020 19:40:15 GMT

zone_A and zone_B is NOT a number. event_count IS the number of interest.

year=2018 month=04 day=10 hour=09 event_count=100 zone=zone_A
year=2018 month=04 day=10 hour=09 event_count=110 zone=zone_B

How can I distinguish the event_count from the event with zone_A with the event_count with zone_B

Re: Finding difference and percentage from 2 events withe same fields but difference index

xpac — Tue, 22 May 2018 19:50:29 GMT

Ah, sorry, I overlooked that. Do you only have one event per hour and zone?

Re: Finding difference and percentage from 2 events withe same fields but difference index

liondancer — Tue, 22 May 2018 20:01:30 GMT

Good question! I might have more than 1 event per hour for the SAME ZONE. For that case, I would like to sum up all the event_count values for that HOUR

ex:

 year=2018 month=04 day=10 hour=09 event_count=110 zone=zone_B
 year=2018 month=04 day=10 hour=09 event_count=50 zone=zone_B

I would like to have event_count 160 because these two events are in the same HOUR and same ZONE

For the sample HOUR, I should have 1 event PER zone:

For example:

 year=2018 month=04 day=10 hour=09 event_count=10 zone=zone_A
 year=2018 month=04 day=10 hour=09 event_count=30 zone=zone_B
 year=2018 month=04 day=10 hour=10 event_count=50 zone=zone_A
 year=2018 month=04 day=10 hour=10 event_count=20 zone=zone_B
 year=2018 month=04 day=10 hour=11 event_count=70 zone=zone_A
 year=2018 month=04 day=10 hour=11 event_count=80 zone=zone_B
...

Re: Finding difference and percentage from 2 events withe same fields but difference index

xpac — Tue, 22 May 2018 20:05:08 GMT

I'd try this:

| chart sum(event_count) over DateHour by zone

Re: Finding difference and percentage from 2 events withe same fields but difference index

niketn — Tue, 29 Sep 2020 19:36:43 GMT

@liondancer, your query with chart ... over DateHour by zone, gets the event_count for each zone for every date_hour. Based on your sample data, I expect that for each hour you will have only one event for each zone with the total event_count. Even if you had more than one event, you should use sum(event_count) to get the total events for specific DateHour.

Following is a run anywhere search based on sample data provided. Query from makeresults till | KV generate the dummy data as per the question:

| makeresults 
| eval _raw="year=2018 month=04 day=10 hour=09 event_count=100 zone=zone_A" 
| append 
    [| makeresults 
    | eval _raw="year=2018 month=04 day=10 hour=09 event_count=110 zone=zone_B"] 
| KV 
| eval DateHour=year."-".month."-".day."-".hour 
| chart sum(event_count) over DateHour by zone 
| fillnull value=0 zone_A,zone_B
| eval percent= round((abs(zone_A-zone_B)/zone_B)*100,2)

Re: Finding difference and percentage from 2 events withe same fields but difference index

niketn — Tue, 22 May 2018 20:21:13 GMT

Up voting as I just noticed you beat me to it!

Re: Finding difference and percentage from 2 events withe same fields but difference index

somesoni2 — Tue, 29 Sep 2020 19:36:48 GMT

The chart command create fields with name zone_B and zone_B which'll have event_count as their value. This enables you to apply mathematical operations using zone names. You'll see it better if you just run the search and see the output after the chart command.