Hey guys,
I need some quick help creating a nested stats table and grouping by multiple values within that table. My data contains the following data points that I am trying to correlate / visualize: Client IP Address, Unique Hash ID, Unique Document ID, and the count that shows the number of times an IP Address accessed a Unique Hash ID, and Doc ID.
An example data set is:
192.168.1.1 (client IP), abcdefg1 (hash 1), 12948(DocID1), 129584(DocID2), 1029384(DocID3)
192.168.1.1(Client IP), abcdefg2 (hash 2), 10294 (DocID 1),
192.168.1.5(Client IP), abcdefg1 (hash1), 12948(DocID1), 1029484(DocID2)
I'm looking to create the following table to help visualize these relationships
|
Client IP | Unique Hash | Document ID | Count | ||
192.168.1.1 | abcdefg1 | 12948 | 5 | ||
129584 | 10 | ||||
1029384 | 15 | ||||
abcdefg2 | 12948 | 2 | |||
1029484 | 3 | ||||
192.168.1.5 | abcdefg1 | 12948 | 1 | ||
1029484 | 4 | ||||
I've created nested tables before but I'm really stumping myself on this one. Any advice?
Figured it out, pretty simple but I was doing the operations in the wrong order originally.
index="my_custom_index" "properties.requestUri"="http*://my.customwebpage.com:443/api/NotARealEndpoint/*/CoolCars/*" AND NOT "properties.clientIp"="127.0.*.*" AND NOT properties.httpStatusCode=401 |rex field="properties.requestUri" "http(.):\/\/my.customwebpage.com:(\d+)\/api\/NotARealEndpoint\/(?<uniqueHash>[a-zA-z0-9].+[^\/])\/CoolCars\/(?<CarID>[\d].+)"
| stats count by properties.clientIp, uniqueHash, CarID
| stats list(uniqueHash) as UniqueHash, list(CarID) as CarID, list(count) as Count by properties.clientIp
| append [
search index="my_custom_index" "properties.requestUri"="http*://my.customwebpage.com:443/api/NotARealEndpoint/*/CoolCars/*" AND NOT "properties.clientIp"="127.0.*.*" |rex field="properties.requestUri" "http(.):\/\/my.customwebpage.com:(\d+)\/api\/NotARealEndpoint\/(?<uniqueHash>[a-zA-z0-9].+[^\/])\/CoolCars\/(?<CarID>[\d].+)"
| stats count by uniqueHash,CarID
] | table properties.clientIp, UniqueHash, CarID, Count