# 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:       [email protected]
  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

```