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
@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)
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. 🙂
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?
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
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
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.
Ah, sorry, I overlooked that. Do you only have one event per hour and zone?
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
...
I'd try this:
| chart sum(event_count) over DateHour by zone
Up voting as I just noticed you beat me to it!