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

AWS GenAI LIVE image

Real challenges. Real solutions. Real talk.

From technical discussions to philosophical debates, AWS and AWS Partners examine the impact and evolution of gen AI.

Learn more

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay