Various times I’ve needed to do some quick summaries of how a given server and its databases were being used. On macOS and Linux we can use shell commands such as grep to get a quick summary of what’s happening on a server.
Here’s one that came up just recently. Client wanted to know what accounts had been used to access databases, but they were largely using generic account names.
grep "opening database" "/Library/FileMaker Server/Logs/Access.log" | grep -v "(FileMaker Script)" | tr "\"[]" "\t" | cut -f6,10,12 | LC_ALL=C sort --ignore-case | uniq -i | expand -t50,90,110
… Enriqoa da le Hoalge (Enriqoa’s Macbook) pappar-putts Enriqoa da le Hoalge Enriqoa Girun (Enriqoa Girun's Air) vurld vida rertnar Enriqoa Girun Enriqoa Girun-Gotiarraz (mutaedfinenca) vurld vida Data Enriqoa Girun Erice Oessenu (Erice oessenu) Go Finenca rm Erice Oessenu (Erice oessenu) rrujacts rm Erice Heims (Erice Heims' Macbook) pappar-putts Erice Heims Eone Kwun (Eone Kwun's macbook) pappar-putts Eone Kwun …
What versions of FileMaker are users using? This will give us the versions used and how often they connected:
grep "opening a connection" "/Library/FileMaker Server/Logs/Access.log" | grep -v "Server " | egrep -o 'using ".* ([12][0-9][.][0-9].[0-9])' | cut -c8- | sort | uniq -c
1 Pro 15.0.3 310 Pro 16.0.1 18 Pro 16.0.2 28 ProAdvanced 16.0.1 380 ProAdvanced 16.0.2
A more typical situation here where we want to summarize which accounts are accessing the databases:
grep "opening database" "/Library/FileMaker Server/Logs/Access.log" | grep -v "(FileMaker Script)" | tr "\"[]" "\t" | cut -f12,10 | LC_ALL=C sort -f | uniq -i | expand -t50,90
checkIt Admin PAMS alex hub_v0 Admin Crew developer Crew entry
Finally, a nice summary of the total times each file has been accessed (does not included files never accessed):
grep "opening database" "/Library/FileMaker Server/Logs/Access.log" | grep -v "(FileMaker Script)" | cut -d "\"" -f4 | sort | uniq -c
690 Finance 42 MarCom Training 100 Licensing Tracks 19 jam-potts 1446 Projects 1584 WW Adv
If you want tab delimited data, just drop the expand command that’s at end. The date range used depends on what’s in your Access.log file. You might want to add a head, tail or yet another grep command to get a particular range.
Postscript
Here’s a version, using Ubuntu paths, that will summarize the date each file was last accessed:
grep "opening database" "/opt/FileMaker/FileMaker Server/Logs/Access.log" | grep -v "(FileMaker Script)" | tr "\"[]" "\t" | cut -f1,10 | sort -r -k1 | sort -k3 -u
And this will distill access logs to just unique account and IP addresses:
grep "opening database" "/opt/FileMaker/FileMaker Server/Logs/Access.log" | grep -v '(FileMaker Script)"' | cut -d\t -f3- | cut --output-delimiter=" " -d\" -f2,4,6 | sort -uSimon