How to calculate difference in dropped packets and show per day output

[atulitm]

I have logs in following format with fields Device, Applied_Interface, Class_Map for multiple devices

13th sept(Monday) Device="router" Applied_Interface="Gigabit0/0/0" Class_Map="DATA" Packet_Drops="17302"
14th sept(Tuesday) Device="router" Applied_Interface="Gigabit/0/0" Class_Map="DATA" Packet_Drops="17402"
15th sept(Wednesday) Device="router" Applied_Interface="Gigabit0/0/0" Class_Map="DATA" Packet_Drops="17502"

I would like to calculate difference in Dropped Packets and show per day output where Date is Column header with packet drop details on that day for all devices. For example for logs above following output is expected

Device Applied_Interface Class_Map Tuesday(Packet_drops) Wednesday(Packet_drops)
router Gigabit0/0/0 DATA 100 100

I tried doing this via following query but it shows me data with date as row and other fields as column like

index= |  bucket span=1d _time | stats earliest(Packet_Drops) as Packet_Drops_yesterday, latest(Packet_Drops) as Packet_Drops_today by Device,Class_Map,Applied_Interface | eval Packet_Drops= Packet_Drops_today - Packet_Drops_yesterday | table _time,Device,Class_Map,Applied_Interface,Packet_Drops_yesterday,Packet_Drops_today,Packet_Drops

Device Applied_Interface Class_Map Packet Drops
Tuesday router Gigabit0/0/0 DATA 100
Wednesday router Gigabit0/0/0 DATA 100

Please help with same

[atulitm · Accepted solution]

Finally I got it working with following query , Thanks for help !!

index= | bucket span=1d _time| eval Day=strftime(_time,"%d.%m %A") | sort 0 _time Device Applied_Interface Class_Map | streamstats current=false last(Packet_Drops) as prev_Packet_Drops by Device Applied_Interface Class_Map | eval Dropped_Packets=Packet_Drops-prev_Packet_Drops | eventstats avg(Dropped_Packets) as Avg_Dropped_Packets by Device Applied_Interface Class_Map | eval merged=Device."#".Applied_Interface."#".Avg_Dropped_Packets."#".Class_Map | chart values(Dropped_Packets) over merged by Day | rex field=merged "(?[^#]+)#(?[^#]+)#(?[^#]+)#(?.+)" | fields - merged | table Device Applied_Interface Class_Map * | sort - Avg_Dropped_Packets

[richgalloway]

If you problem is resolved, please accept an answer (you can accept your own answer) to help other readers.

[somesoni2]

Give this a try (your _time field value is set correctly for event)

index=YourIndex
| eval Day=strftime(_time,"%w. %A")
| delta Packet_Drops as "Dropped_Packets" 
| where isnotnull(Dropped_Packets)
| eval merged=Device."#".Applied_Interface."#".Class_Map
| chart values(Dropped_Packets) over merged by Day
| rex field=merged "(?<Device>[^#]+)#(?<Applied_Interface>[^#]+)#(?<Class_Map>.+)"
| fields - merged
| table Device Applied_Interface Class_Map *

[atulitm]

Hello ,That query doesn't give right results for Packet drops although format is exactly same i needed like for one of the device following is logs with other fields like device,interface etc
9/14/17 3:33:50.000 AM Packet_Drops="1744230"
9/13/17 9:45:31.000 AM Packet_Drops="1732173"

Output of packet drops after query is :

(Wednesday) 1215903 Thursday (1232717)
but it should be 1744230-1732173=12057 for 9/14/17

[somesoni2]

How about this

index=YourIndex
 | eval Day=strftime(_time,"%w. %A")
 | sort 0 _time Device Applied_Interface Class_Map
 | streamstats values(Packet_Drops) as prev_Packet_Drops by Device Applied_Interface Class_Map
 | where isnotnull(prev_Packet_Drops) | eval Dropped_Packets=Packet_Drops-prev_Packet_Drops 
 | eval merged=Device."#".Applied_Interface."#".Class_Map
 | chart values(Dropped_Packets) over merged by Day
 | rex field=merged "(?<Device>[^#]+)#(?<Applied_Interface>[^#]+)#(?<Class_Map>.+)"
 | fields - merged
 | table Device Applied_Interface Class_Map *

[atulitm]

Not sure what this query is exactly doing in terms of algorithm but now values i get for packet drops are 0
In Last query for few devices packet drops were equal to there actual packet drops on that day if not difference and for few devices it was some value which was not at all relevent .

[atulitm]

one thing query which i used and pasted in initial problem description was giving me correct difference of packetdrops and query which you gave was giving out in format i expected .

[atulitm]

index=YourIndex
| eval Day=strftime(_time,"%w. %A")
| delta Packet_Drops as "Dropped_Packets"
| where isnotnull(Dropped_Packets)
| eval merged=Device."#".Applied_Interface."#".Class_Map
| chart values(Dropped_Packets) over merged by Day
| rex field=merged "(?[^#]+)#(?[^#]+)#(?.+)"
| fields - merged
| table Device Applied_Interface Class_Map *

I tested If i use this query for individual Devices with ( sort 0 _time ) , it gives me correct results and as soon as for all devices in one go . It starts giving unexpected output so looks like it doesnt work like for loop for all devices due to which we get unexpected output .