Bash: An Example of Optimizing Filtering Commands with grep and awk

I will tell you about a task I solved the other day🧑💻 I slightly changed the input data, but the essence remains the same.

There are two CSV files:

📄 ping_status.csv - contains diagnostic information about host availability by ping.

Example:

ID|DNS_NAME|IP_ADDRESS|PING_STATUS
1|host1.corp.local|10.66.163.62|success
2|host2.corp.local|10.20.202.136|fail
3|host3.corp.local|10.251.60.85|success
4|host4.corp.local|10.137.229.129|success
5|host5.corp.local|10.161.196.50|success
6|host6.corp.local|10.214.27.115|fail
7|host7.corp.local|10.188.87.205|fail
8|host8.corp.local|10.2.203.4|success
9|host9.corp.local|10.8.74.100|fail
10|host10.corp.local|10.21.116.174|success

📃 device_list.csv - a list of devices with department and type specified.

Example:

DEPARTMENT;IP_ADDRESS;DEVICE_TYPE
QA;10.92.104.3;workstation
HR;10.25.101.192;server
DevOps;10.242.234.48;workstation
DevOps;10.35.10.218;switch
Legal;10.196.97.105;printer
DevOps;10.105.56.232;router
Support;10.34.3.56;printer
IT;10.40.93.15;router
IT;10.72.223.200;laptop
Legal;10.60.206.98;switch

Task: select hosts with an unsuccessful diagnostic status (PING_STATUS field) from the ping_status.csv file and check whether they are present in the device_list.csv file for the DevOps department (DEPARTMENT field):

After thinking a little, I put together this one-liner:

while IFS= read -r line; do [ -n "$line" ] && grep -F -w -- "$line" /tmp/device_list.csv | awk -F ';' '$1 == "DevOps"'; done < <(awk -F '|' '$4 != "success" {print $3}' /tmp/ping_status.csv) | sort -u

Ideally, I should have written a full script, but I needed an operational solution, and it became interesting to compose a one-liner.

The command is a while loop that reads a pseudo-file line by line (the <(..) construct), which is the output of another command - awk; awk, in turn, sets | as the delimiter, selects devices whose availability status is not equal to success, and outputs only the 3rd column - the IP address from the ping_status.csv file. Then grep searches for matches in the device_list.csv file.

An experienced shell developer, unlike me, will immediately see bad code. The problem is that grep is executed for each matching line from ping_status.csv in device_list.csv. This is not a big deal if you have a few dozen or hundreds of devices. It is a working way to solve the task. But if we are talking about hundreds of thousands (my case), running grep that many times is an unaffordable luxury. Processing will take a very long time.

For example: device_list.csv - 300000 lines ping_status.csv - 1000000 lines

The runtime of the command above under such conditions will be: 29 minutes, 17 seconds 😵

Not an option.

Since I needed to perform this procedure more than once and with different parameters, I decided to optimize the command, including with the help of a neural network. As a result, I managed to form 2 variants: using grep and awk:

1) Fast variant with grep (in one pass) + awk:

grep -F -w -f <(awk -F'|' '$4 != "success" && $4 ~ /[^[:space:]]/ {print $3}' /tmp/ping_status.csv) /tmp/device_list.csv | awk -F ';' '$1 == "DevOps"' | sort -u

The command completed in: 1.8 seconds🤯

The whole point here is using the -f option, which allows using the specified file (in my case a pseudo-file) as the input pattern, processing it line by line.

And [^[:space:]] is a regex that allows excluding empty lines.

2) Fast variant using only awk:

awk -F'|' '
    FNR==NR && $4 != "success" && $4 ~ /[^[:space:]]/ {ips[$3]; next}
    FNR!=NR && $1 == "DevOps" && $2 in ips {seen[$0]++}
    END {for (line in seen) print line}
    ' /tmp/ping_status.csv FS=';' /tmp/device_list.csv

The command completed in: 1.7 seconds🤔

☝️awk syntax for reptilians.

This is purely the syntax and capabilities of awk, in which you can write full-fledged data processing programs. I recommend asking a neural network to explain the syntax.

In general, the result is obvious😒

If you suddenly have ideas on how to solve the task more elegantly, write your variant in the comments👍

And take care of your time👨💻 Have a good day!

👨‍💻And…

Don’t forget about our Telegram channel 📱 and chat

Or maybe you want to become a co-author? Then click here🔗

💬 All the best ✌️

That should be it. If not, check the logs 🙂