Lookup Table and Regex

[aksampat81]

Hi,
I need some help with lookup table combined with regular expressions.

I have the an apache log file which looks like:

*25/Jul/2018:10:17:30.999;2.2.2.255;temp-prod.nl;https://finance.host.nl;GET /api/balances/balanceviewer/ HTTP/1.0;200;111;12421;15yjLFielsdx424u6S7Y136sHTvfpFWP;-;+dF15erUTMPMyqlLYEUugh7j7eJjtKtub5pRai/Z3ckGx0AkbJnDI3PPwHrwzRdk+bVwDNNU0ducGu0IZ8cKVA==;host/195 CFNetwork/651.5.4 Darwin/11.7.1;1493;"-";
25/Jul/2018:10:17:30.999;2.2.2.255;temp-prod.nl;https://finance.host.nl;GET /api/balances/balanceviewer/ HTTP/1.0;200;111;12421;18y3LF87lsdx4643AS7Y13ZsHT4bpFWP;-;+dF15erUTMPMyqlLYEUugh7j7eJjtKtub5pRai/Z3ckGx0AkbJnDI3PPwHrwzRdk+bVwDNNU0ducGu0IZ8cKVA==;host/195 CFNetwork/651.5.4 Darwin/11.7.1;1493;"-";
25/Jul/2018:10:17:30.999;2.2.2.255;temp-prod.nl;https://finance.host.nl;GET /api/balances/balanceviewer/ HTTP/1.0;200;111;12421;17yh6F9tlsd34A43AS7Y19ZseevbpFWP;-;+dF15erUTMPMyqlLYEUugh7j7eJjtKtub5pRai/Z3ckGx0AkbJnDI3PPwHrwzRdk+bVwDNNU0ducGu0IZ8cKVA==;host/195 CFNetwork/651.5.4 Darwin/11.7.1;1493;"-";
25/Jul/2018:10:17:30.997;2.2.2.255;temp-prod.nl;https://finance.host.nl;GET /api/balances/getyoursettings/N1vAO8zgb38oYh6MogfhwQ HTTP/1.0;200;111;1111;155eUDXOjDT/G2QzEf2WvZulbirv4Fw2;https://host.temp.nl/finance/us/pay/transactions.html?account=A1vAO8zgbR8oghPMogfhwQ;n0hJVF5icTKeT+zO/5Xhs+Ul/QHwyqsrngxzEC6hPmU1RNpc5zF/51fSh07kfpHzp96YMt0bZiwZj76ze+N9GA==;Mozilla/5.0 (Linux; Android 7.0; HUAWEI NXT-L29 Build/HUAWEINXT-L29) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.112 Crosswalk/19.49.514.5 Mobile Safari/537.36;2362;"-";
25/Jul/2018:10:17:30.997;2.2.2.255;temp-prod.nl;https://finance.host.nl;GET /api/balances/getyoursettings/Q2vAO8zg5R8oY4PMogfhwQ HTTP/1.0;200;111;1111;155gUDX4jDT/G2rzEf2WvZulbirv4Fwt;https://host.temp.nl/finance/us/pay/transactions.html?account=s1vAO8zgbs8oYhPMogfhwQ;n0hJVF5icTKeT+zO/5Xhs+Ul/QHwyqsrngxzEC6hPmU1RNpc5zF/51fSh07kfpHzp96YMt0bZiwZj76ze+N9GA==;Mozilla/5.0 (Linux; Android 7.0; HUAWEI NXT-L29 Build/HUAWEINXT-L29) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.112 Crosswalk/19.49.514.5 Mobile Safari/537.36;2362;"-";
25/Jul/2018:10:17:30.997;2.2.2.255;temp-prod.nl;https://finance.host.nl;GET /api/balances/getyoursettings/A3vAO8zg5R8o4hPMogfhwQ HTTP/1.0;200;111;1111;155gUDXOfDT/G2QzEf2WvZulbirv4Ftv;https://host.temp.nl/finance/us/pay/transactions.html?account=f1vAO8zdbR8oYhPMogfhwQ;n0hJVF5icTKeT+zO/5Xhs+Ul/QHwyqsrngxzEC6hPmU1RNpc5zF/51fSh07kfpHzp96YMt0bZiwZj76ze+N9GA==;Mozilla/5.0 (Linux; Android 7.0; HUAWEI NXT-L29 Build/HUAWEINXT-L29) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.112 Crosswalk/19.49.514.5 Mobile Safari/537.36;2362;"-";*

I want to use the lookup and regex command to get an output based on the URL hit:

URL,TransactionName, avg_Responsetime,Hits
 /api/balances/balanceviewer/,BALANCEVIEWER,1493,3
/api/balances/getyoursettings,GETYOURSETTINGS,2362,3

Does anyone know how I can make this kind of a table/report with splunk?

Thanks in advance.

Amit

[sudosplunk]

As stated by @DalJeanis, if the assumptions are correct, then your search would look something like this:

Your_basic_search
| rex field = _raw ".+finance\S+\;GET\s(?<URL>\/\w+\/\w+\/\w+\/)"
| rex field = URL "\/\w+\/\w+\/(?<TransactionName>\w+)
| rex field = _raw ".+\;(?<avg_Responsetime>\d+)\;\"\-\""
|  eval TransactionName=upper(TransactionName)
| stats count by TransactionName as Hits
| table URL TransactionName avg_Responsetime Hits
| outputlookup foo.csv

Note: If your search heads are clustered, then use outputlookup command, otherwise use outputcsv command.

[DalJeanis]

We've made some assumptions here that you will need to review with your real data. See the second second for your code and a discussion.

The first section is just a test data generator:

| makeresults | eval myRaw="25/Jul/2018:10:17:30.999;2.2.2.255;temp-prod.nl;https://finance.host.nl;GET /api/balances/balanceviewer/ HTTP/1.0;200;111;12421;15yjLFielsdx424u6S7Y136sHTvfpFWP;-;+dF15erUTMPMyqlLYEUugh7j7eJjtKtub5pRai/Z3ckGx0AkbJnDI3PPwHrwzRdk+bVwDNNU0ducGu0IZ8cKVA==;host/195 CFNetwork/651.5.4 Darwin/11.7.1;1493;\"-\";!!!!25/Jul/2018:10:17:30.999;2.2.2.255;temp-prod.nl;https://finance.host.nl;GET /api/balances/balanceviewer/ HTTP/1.0;200;111;12421;18y3LF87lsdx4643AS7Y13ZsHT4bpFWP;-;+dF15erUTMPMyqlLYEUugh7j7eJjtKtub5pRai/Z3ckGx0AkbJnDI3PPwHrwzRdk+bVwDNNU0ducGu0IZ8cKVA==;host/195 CFNetwork/651.5.4 Darwin/11.7.1;1493;\"-\";!!!!25/Jul/2018:10:17:30.999;2.2.2.255;temp-prod.nl;https://finance.host.nl;GET /api/balances/balanceviewer/ HTTP/1.0;200;111;12421;17yh6F9tlsd34A43AS7Y19ZseevbpFWP;-;+dF15erUTMPMyqlLYEUugh7j7eJjtKtub5pRai/Z3ckGx0AkbJnDI3PPwHrwzRdk+bVwDNNU0ducGu0IZ8cKVA==;host/195 CFNetwork/651.5.4 Darwin/11.7.1;1493;\"-\";!!!!25/Jul/2018:10:17:30.997;2.2.2.255;temp-prod.nl;https://finance.host.nl;GET /api/balances/getyoursettings/N1vAO8zgb38oYh6MogfhwQ HTTP/1.0;200;111;1111;155eUDXOjDT/G2QzEf2WvZulbirv4Fw2;https://host.temp.nl/finance/us/pay/transactions.html?account=A1vAO8zgbR8oghPMogfhwQ;n0hJVF5icTKeT+zO/5Xhs+Ul/QHwyqsrngxzEC6hPmU1RNpc5zF/51fSh07kfpHzp96YMt0bZiwZj76ze+N9GA==;Mozilla/5.0 (Linux; Android 7.0; HUAWEI NXT-L29 Build/HUAWEINXT-L29) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.112 Crosswalk/19.49.514.5 Mobile Safari/537.36;2362;\"-\";!!!!25/Jul/2018:10:17:30.997;2.2.2.255;temp-prod.nl;https://finance.host.nl;GET /api/balances/getyoursettings/Q2vAO8zg5R8oY4PMogfhwQ HTTP/1.0;200;111;1111;155gUDX4jDT/G2rzEf2WvZulbirv4Fwt;https://host.temp.nl/finance/us/pay/transactions.html?account=s1vAO8zgbs8oYhPMogfhwQ;n0hJVF5icTKeT+zO/5Xhs+Ul/QHwyqsrngxzEC6hPmU1RNpc5zF/51fSh07kfpHzp96YMt0bZiwZj76ze+N9GA==;Mozilla/5.0 (Linux; Android 7.0; HUAWEI NXT-L29 Build/HUAWEINXT-L29) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.112 Crosswalk/19.49.514.5 Mobile Safari/537.36;2362;\"-\";!!!!25/Jul/2018:10:17:30.997;2.2.2.255;temp-prod.nl;https://finance.host.nl;GET /api/balances/getyoursettings/A3vAO8zg5R8o4hPMogfhwQ HTTP/1.0;200;111;1111;155gUDXOfDT/G2QzEf2WvZulbirv4Ftv;https://host.temp.nl/finance/us/pay/transactions.html?account=f1vAO8zdbR8oYhPMogfhwQ;n0hJVF5icTKeT+zO/5Xhs+Ul/QHwyqsrngxzEC6hPmU1RNpc5zF/51fSh07kfpHzp96YMt0bZiwZj76ze+N9GA==;Mozilla/5.0 (Linux; Android 7.0; HUAWEI NXT-L29 Build/HUAWEINXT-L29) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.112 Crosswalk/19.49.514.5 Mobile Safari/537.36;2362;\"-\";"
| makemv delim="!!!!" myRaw
| mvexpand myRaw
| rename myRaw as _raw
| rename COMMENT as "The above just enters your data"

Now, here's the actual code and assumptions made:

| rename COMMENT as "Here we assume the URL must start with /api and is all lower case plus slashes."
| rex "(?<URL>/api[a-z0-9//]*)" 

| rename COMMENT as "Here we assume the transaction name is whatever is between the last two slashes, and must be converted to upper case."
| rex field=URL "[//](?<TransactionName>[a-z0-9]*)[//]$"
| eval TransactionName=upper(TransactionName)

| rename COMMENT as "Here we assume the response time is a numeric entry between the two semicolons that are third and second from the end of each record."
| rex ";(?<Resp>\d+);[^;]*;$"

| rename COMMENT as "Now we calculate the count and average for each combination."
| stats avg(Resp) as AvgResp count as Hits by URL TransactionName
| eval AvgResp=round(AvgResp)

---

If you need help chatting about regular expressions, please get connected up to our Slack channel. There is a #regex subchannel where you can get quick iterations on minor changes to assumptions like the above.