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

[liondancer]

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

[niketn]

@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)

[xpac]

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. 🙂

[liondancer]

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?

[xpac]

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

[liondancer]

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

[somesoni2]

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.

[xpac]

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

[liondancer]

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
...

[xpac]

I'd try this:

| chart sum(event_count) over DateHour by zone

[niketn]

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