The problem
I have a log file with the following format:
2018/12/05 22:43:14 [ChestShop] User bought 1 Boat for 8.00 from Admin Shop at [...] -246, 65, 61
2019/01/02 10:09:38 [ChestShop] User sold 64 Sea Lantern for 27840.00 to Admin Shop at [...] -234, 61, 45
2019/01/02 10:09:38 [ChestShop] User sold 48 Sea Lantern for 20880.00 to Admin Shop at [...] -234, 61, 45
2019/01/02 10:09:42 [ChestShop] User sold 2 Prismarine Bricks for 248.00 to Admin Shop at [...] -233, 62, 45
from which I want to extract certain pieces of information and display them in a summarized list.
The information I want to summarize is names, quantities and sell values. Sell values are the total sell value for the listed quantity. Names (Sea Lantern, Prismarine Bricks etc.) can appear more than once in this log file along with quantities (the number to the left of names) and sell values (the number to the right of "for". Names may contain multiple spaces (never more than 4) or none at all.
... ... [...] ... ... 2 Prismarine Bricks ... 248.00 ... ... ... ... [...] ..., ..., ...
Preferably, I would like the summary to look something like:
totalQuantity1 uniqueName1 totalSellValue1
totalQuantity2 uniqueName2 totalSellValue2
sorted by totalQuantity OR totalSellValue, depending on a small change to the command.
My attemps at solving the problem
I have found out that I can use the following command to get a list of the most occuring items and the amount of times they occur in the log file, sorted by the amount of times they occur (which is not what I want):
cat ChestShop.log | grep -w sold | cut -d ' ' -f 7,8,9,10,11 | awk -F 'for' '{print $1}' | sort | uniq -c | sort -rn
The grep -w sold
command is just used to differentiate buying from selling, and as you can see from the log examples above only two words differ when comparing buying to selling.
I have also used this command to summarize the quantity of one particular item from a list containing only quantities for that item:
cat ChestShop.log | grep -w sold | grep -w 'Magma Block' | cut -d ' ' -f 6 | paste -s -d+ - | bc
I have tried countless other modifications to the above commands but have not come any closer to getting what I would like, the above commands are the closest I have gotten. Preferably the command should be as short as possible also, or if that is difficult, an explanation for each part of the command so that I can understand what is going on (especially if awk is used in any other way than I have used it), thanks.
Any help is very much appreciated.