# N2T log analytics

GET filebeat-uc3-ezid-unit-prd*/_search 
{
  "_source": false,
  "fields":["@timestamp","request"],
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "cdl.fqsn": "uc3-ezid-n2t-prd"
          }
        },
        {
          "query_string": {
            "query": "/\\\"(GET|POST) \\/ark:\\/?(.*)/"
          }
        }
      ]
    }
  }
}

Find arks with periods:

GET filebeat-uc3-ezid-unit-prd*/_search 
{
  "_source": false,
  "fields":["@timestamp","request"],
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "cdl.fqsn": "uc3-ezid-n2t-prd"
          }
        },
        {
          "terms": {
            "response": [302, 303]
          }
        },
        {
          "query_string": {
            "query": "request.keyword: /\\\"[A-Z]{3,6} \\/ark:\\/?[0-9]{5}\\/(.*)\\.(.*) HTTP.*/"
          }
        }
      ]
    }
  }
}

POST _plugins/_sql 
{
  "query": "SELECT @timestamp, request, substring(request, locate(' /', request)+2, locate(' H', request)-7) as pid FROM filebeat-uc3-ezid-unit-prd* WHERE cdl.fqsn='uc3-ezid-n2t-prd' AND response IN (302, 303) and request LIKE '\"GET /%ark:/20775%'LIMIT 100"
}

older below. Note that date functions have been diabled...

POST _plugins/_sql 
{
  "query": "SELECT @timestamp, http.response.status_code as status, url.original as url, source.address as source FROM filebeat-uc3-ezid-unit-prd* WHERE @timestamp >= adddate(current_date(), INTERVAL -1 day) AND http.response.status_code IN (302, 303) AND host.name IN ('uc3-ezidn2t-prd01.cdlib.org', 'uc3-ezidn2t-prd02.cdlib.org') LIMIT 10"
}

POST _plugins/_sql 
{
  "query": "SELECT @timestamp, http.response.status_code as status, url.original as url, source.address as source FROM filebeat-uc3-ezid-unit-prd* WHERE @timestamp >= '2024-07-10 00:00:00' AND http.response.status_code IN (302, 303) AND host.name IN ('uc3-ezidn2t-prd01.cdlib.org', 'uc3-ezidn2t-prd02.cdlib.org') LIMIT 10"
}

POST _plugins/_sql 
{
  "query": "SELECT @timestamp, http.response.status_code as status, url.original as url, substring(url.original, 2, locate(':', url.original)-2) as scheme, source.address as source FROM filebeat-uc3-ezid-unit-prd* WHERE @timestamp >= '2024-07-10 00:00:00' AND http.response.status_code IN (302, 303) AND host.name IN ('uc3-ezidn2t-prd01.cdlib.org', 'uc3-ezidn2t-prd02.cdlib.org') LIMIT 10"
}

POST _plugins/_sql 
{
  "query": "SELECT count(*) as n, left(url.original, locate(':', url.original)) as scheme FROM filebeat-uc3-ezid-unit-prd* WHERE @timestamp >= '2024-07-10 00:00:00' AND http.response.status_code IN (302, 303) AND host.name IN ('uc3-ezidn2t-prd01.cdlib.org', 'uc3-ezidn2t-prd02.cdlib.org') GROUP BY scheme"
}