# Client Identification Process ## Overview Our specific goal is to generate a list of clients connected to a p4d server. For this purpose we'll use the log2sql tool. Select the correct version using the green 'latest' link from the right side fo this page: https://github.com/rcowham/go-libp4dlog ## Sample Procedure Do the following as `perforce` user on a p4d server that users connect to (commit, edge, etc.). For most comprehensive ressults, run it on a set of servers that users connect to, to include build edges. STEP 1: Get SQLite ``` $ sudo yum install sqlite3 ``` STEP 2: Acquire the log analyzer. ``` $ mkdir -p /p4/common/site/lscan $ cd /p4/common/site/lscan $ curl -L -O https://github.com/rcowham/go-libp4dlog/releases/download/v0.13.2/log2sql-linux-amd64.gz $ gunzip log2sql-linux-amd64.gz $ chmod +x log2sql-linux-amd64 ``` STEP 3: Generate a database from a representative P4D server log, selecting perhaps a juicy big one: ``` $ ./log2sql-linux-amd64 /p4/1/logs/log.2024-09-27_02-05-02.gz INFO[0000] log2sql, version v0.13.2 (branch: master, revision: 9659e4c) build user: rcowham@perforce.com build date: 2024-08-23T14:10:47+1000 go version: go1.21.5 INFO[0000] Starting 2024-09-27 12:37:50.259999321 -0400 EDT m=+0.001667752, Logfiles: [/p4/1/logs/log.2024-09-27_02-05-02.gz] INFO[0000] Flags: debug 0, json/file false/, sql/file false/, dbName , noMetrics/file false/ INFO[0000] serverID , sdpInstance , updateInterval 10s, noOutputCmdsByUser false, outputCmdsByUserRegex caseInsensitve false, noCompletionRecords false, debugPID/cmd 0/ INFO[0000] Creating metrics output: /p4/1/logs/log.2024-09-27_02-05-02.metrics, config: &{Debug:0 ServerID: SDPInstance: UpdateInterval:10s OutputCmdsByUser:true OutputCmdsByUserRegex: OutputCmdsByIP:true CaseSensitiveServer:true} INFO[0000] Creating database: /p4/1/logs/log.2024-09-27_02-05-02.db INFO[0000] Processing: /p4/1/logs/log.2024-09-27_02-05-02.gz INFO[0000] Progress reporting frequency: 1s INFO[0000] Encountered server running threads (11) message INFO[0000] Finished all log files INFO[0000] Encountered server running threads (12) message INFO[0000] Encountered server running threads (13) message INFO[0000] Encountered server running threads (14) message INFO[0000] Encountered server running threads (15) message INFO[0000] Encountered server running threads (16) message INFO[0000] Encountered server running threads (17) message INFO[0000] Encountered server running threads (18) message INFO[0000] Encountered server running threads (19) message INFO[0000] Encountered server running threads (20) message INFO[0000] Encountered server running threads (21) message INFO[0000] Encountered server running threads (22) message INFO[0000] Encountered server running threads (23) message INFO[0000] Encountered server running threads (9) message INFO[0000] Encountered server running threads (10) message INFO[0000] Encountered server running threads (11) message INFO[0000] Encountered server running threads (12) message INFO[0000] Encountered server running threads (13) message INFO[0000] Encountered server running threads (14) message INFO[0000] Main: metrics closed INFO[0000] Completed 2024-09-27 12:37:51.033979518 -0400 EDT m=+0.775647952, elapsed 773.980344ms ``` STEP 4: Get a list of clients: ``` $ echo -e ".mode column\\n select count(*), app, user from process group by app order by count(*) asc;" | sqlite3 -header /p4/1/logs/log.2024-09-27_02-05-02.db count(*) app user ---------- -------------------------------- ---------------- 5 p4d/2024.1/LINUX26X86_64/2655224 svc_p4d_edge_syd 20 Helix P4V/LINUX26X86_64/2024.1/2 bruno 313 p4/2024.1/LINUX26X86_64/2655224 perforce 435 Helix Admin/LINUX26X86_64/2024.1 bruno 11723 p4/2024.1/LINUX26X86_64/2655224 perforce ``` Alternately, for a minimalist approach: ``` $ echo Client List:; echo -e ".mode column\\n select app from process group by app order by count(*) asc;" | sqlite3 -header /p4/1/logs/log.2024-09-27_02-05-02.db | grep -v ^app | sort -u Client List: -------------------------------- Helix Admin/LINUX26X86_64/2024.1 Helix P4V/LINUX26X86_64/2024.1/2 p4/2024.1/LINUX26X86_64/2655224 p4d/2024.1/LINUX26X86_64/2655224 ``` ## More Fun with SQL If you want to have a play with other things you can dig for in the logs, do something like this sample: ``` echo -e ".mode column\\n select * from process group by app order by count(*) asc;" | sqlite3 -header /p4/1/logs/log.2024-09-27_02-05-02.db | head -1 ``` That will display a list of fields, e.g. `app`, `user`, and `workspace` that you can use after the `select` call in the SQL line. The p4d server log is stuff into the SQL database, so you can run lots of general queries on it. ## Look, Mom, No SQL! You can also get useful results form some snazzy grepping without SQL, like this sample: ``` $ zgrep "\[" /p4/1/logs/log.2024-09-27_02-05-02.gz | cut -f2 -d "[" | cut -f1 -d"]" | sort | uniq -c | sort -n 5 p4d/2024.1/LINUX26X86_64/2655224 20 Helix P4V/LINUX26X86_64/2024.1/2573667/v95 (brokered) 438 Helix Admin/LINUX26X86_64/2024.1/2573667/v95 (brokered) 625 p4/2024.1/LINUX26X86_64/2655224 (brokered) 11723 p4/2024.1/LINUX26X86_64/2655224 ```