DEV Community

Dmitry Romanoff
Dmitry Romanoff

Posted on • Edited on

How to parse Azure PostgreSQL Server log files using AWK?

AWK is a text-processing utility on GNU/Linux. It is very powerful and uses a simple programming language. It can solve complex text processing tasks with a few lines of code.

This post demonstrates how to parse Azurer PostgreSQL Server log files using AWK.

How to list “connection authorized” text pattern occurrences in the hour:minute db_user format?

grep "connection authorized" postgresql-2021-12-09_100000.log | awk ' { printf("%s %s\n", substr($2, 1, 5), substr($6, index($6,"user=")+5, index($6, "database=")-6)); } '

10:59 db_user_1
10:59 db_user_2
10:59 db_user_1
10:59 db_user_2
10:59 db_user_2
Enter fullscreen mode Exit fullscreen mode

How to list top DB users by “connection authorized” text pattern occurrences sorted in descending order?

grep "connection authorized" postgresql-2021-12-09_100000.log | awk ' { printf("%s %s\n", substr($2, 1, 5), substr($6, index($6,"user=")+5, index($6, "database=")-6)); } ' | awk '{count[$2]++} END {for (word in count) print word, count[word]}' | sort -k 2,2 -n -r

db_user_1 1821
db_user_2 196
db_user_3 136
db_user_4 136
db_user_5 130
Enter fullscreen mode Exit fullscreen mode

How to list the distribution of “connection authorized” text pattern occurrences per hour:minute (HH24:MI) sorted in descending order by the number of occurrences?

grep "connection authorized" postgresql-2021-12-09_100000.log | awk ' { printf("%s %s\n", substr($2, 1, 5), substr($6, index($6,"user=")+5, index($6, "database=")-6)); } ' | awk '{count[$1]++} END {for (word in count) print word, count[word]}' | sort -k 2,2 -n -r

10:53 644
10:23 509
10:29 502
10:22 479
10:52 463
Enter fullscreen mode Exit fullscreen mode

How to list the distribution of “connection authorized” text pattern occurrences per hour:minute:second (HH24:MI:SS) sorted in descending order by the number of occurrences?

grep "connection authorized" postgresql-2021-12-09_100000.log | awk ' { printf("%s,%s\n", substr($2, 1, 7), $6) } '

10:29:44  90
10:28:15  67
10:35:02  61
10:05:51  57
10:30:36  56
Enter fullscreen mode Exit fullscreen mode

Sentry image

Hands-on debugging session: instrument, monitor, and fix

Join Lazar for a hands-on session where you’ll build it, break it, debug it, and fix it. You’ll set up Sentry, track errors, use Session Replay and Tracing, and leverage some good ol’ AI to find and fix issues fast.

RSVP here →

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more