Summarizing FileMaker Server Access.log data

{"protected":false,"rendered":"\n

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.

\n\n\n\n\n\n\n\n

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.

\n\n\n\n

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\n

\n\n\n\n

…\nEnriqoa da le Hoalge (Enriqoa's Macbook)        pappar-putts                   Enriqoa da le Hoalge\nEnriqoa Girun (Enriqoa Girun's Air)             vurld vida rertnar             Enriqoa Girun\nEnriqoa Girun-Gotiarraz (mutaedfinenca)         vurld vida Data                Enriqoa Girun\nErice Oessenu (Erice oessenu)                   Go Finenca                     rm\nErice Oessenu (Erice oessenu)                   rrujacts                       rm\nErice Heims (Erice Heims' Macbook)              pappar-putts                   Erice Heims\nEone Kwun (Eone Kwun's macbook)                 pappar-putts                   Eone Kwun\n…\n

\n\n\n\n

What versions of FileMaker are users using? This will give us the versions used and how often they connected:

\n\n\n\n

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\n

\n\n\n\n

   1 Pro 15.0.3\n 310 Pro 16.0.1\n  18 Pro 16.0.2\n  28 ProAdvanced 16.0.1\n 380 ProAdvanced 16.0.2\n

\n\n\n\n

A more typical situation here where we want to summarize which accounts are accessing the databases:

\n\n\n\n

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

\n\n\n\n

checkIt                                   Admin\nPAMS                                      alex\nhub_v0                                    Admin\nCrew                                      developer\nCrew                                      entry\n

\n\n\n\n

Finally, a nice summary of the total times each file has been accessed (does not included files never accessed):

\n\n\n\n

grep \"opening database\" \"/Library/FileMaker Server/Logs/Access.log\" | grep -v \"(FileMaker Script)\" | cut -d \"\\\"\" -f4 | sort | uniq -c

\n\n\n\n

 690 Finance\n  42 MarCom Training\n 100 Licensing Tracks\n  19 jam-potts\n1446 Projects\n1584 WW Adv\n

\n\n\n\n

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.

\n\n\n\n

Postscript

\n\n\n\n

Here’s a version, using Ubuntu paths, that will summarize the date each file was last accessed:

\n\n\n\n

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

\n\n\n\n

And this will distill access logs to just unique account and IP addresses:

\n\n\n\n

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 -u

\n\n\n\n

Simon

\n"}