I'm trying to create a field with values by searching for URL pattern matches so I can report on usage for certain patterns.
I believe the issue may be related to me looking for two unique things as part of one case evaluation.
For example, if I add to the end of the case statement 1==1, "Undefined" it will count them all as Undefined. If I remove the request part of the OldFromNew matching then it will display OldFromNew.
Can someone tell me how to do this correctly? I really need to figure this out quickly to provide a usage report.
Any tips appreciated!
Here is my search -
index="my_index" (request="GET /Pages/Default.aspx HTTP/1.1" AND referer="-") OR (request="GET /sites/betaae/Pages/default.aspx HTTP/1.1" AND referer="-") OR (request="GET /sites/betaae/Pages/default.aspx HTTP/1.1" AND referer="https://www.someplace.com/Pages/default.aspx")
|eval AccessTypeValue=case(request="GET /Pages/Default.aspx HTTP/1.1" AND referer="-", "DirectOld", request="GET /sites/betaae/Pages/default.aspx HTTP/1.1" AND referer="-", "DirectNew", request="GET /sites/betaae/Pages/default.aspx HTTP/1.1" AND referer="https://www.someplace.com/Pages/default.aspx", "NewFromOld")
| stats count by AccessTypeValue
I was able to discover that with this result set I could leverage the 1==1 (everything else) to get all three things. I was hoping for a way that I can call out multiple things, so if anyone knows a way to use 'AND' in eval case() statement or maybe a different way then please do comment.
Here is how I solved it -
index="my_index" | eval request=lower(request) | eval referer=lower(referer) | search (request="get /pages/default.aspx http/1.1" AND referer="-") OR (request="get /sites/betaae/pages/default.aspx http/1.1" AND referer="-") OR (request="get /sites/betaae/pages/default.aspx http/1.1" AND referer="https://www.someplace.com/pages/default.aspx") | lookup PilotLookup.csv GROUP AS Agency OUTPUT GROUP AS BetaAgency STATE AS State BRANCH AS Branch DSM AS DSM | where isnotnull(BetaAgency) | eval AccessTypeValue=case(request="get /pages/default.aspx http/1.1", "DirectOld", referer="https://www.someplace.com/pages/default.aspx", "NewFromOld", 1==1, "DirectNew") |stats count(eval(AccessTypeValue="DirectOld")) as DirectOld, count(eval(AccessTypeValue="DirectNew")) as DirectNew, count(eval(AccessTypeValue="NewFromOld")) as NewFromOld by AgentID State Branch DSM BetaAgency| table State Branch DSM BetaAgency AgentID DirectNew NewFromOld DirectOld |addcoltotals labelfield=AgentID label=TOTALS | addtotals |sort BetaAgency
I was able to discover that with this result set I could leverage the 1==1 (everything else) to get all three things. I was hoping for a way that I can call out multiple things, so if anyone knows a way to use 'AND' in eval case() statement or maybe a different way then please do comment.
Here is how I solved it -
index="my_index" | eval request=lower(request) | eval referer=lower(referer) | search (request="get /pages/default.aspx http/1.1" AND referer="-") OR (request="get /sites/betaae/pages/default.aspx http/1.1" AND referer="-") OR (request="get /sites/betaae/pages/default.aspx http/1.1" AND referer="https://www.someplace.com/pages/default.aspx") | lookup PilotLookup.csv GROUP AS Agency OUTPUT GROUP AS BetaAgency STATE AS State BRANCH AS Branch DSM AS DSM | where isnotnull(BetaAgency) | eval AccessTypeValue=case(request="get /pages/default.aspx http/1.1", "DirectOld", referer="https://www.someplace.com/pages/default.aspx", "NewFromOld", 1==1, "DirectNew") |stats count(eval(AccessTypeValue="DirectOld")) as DirectOld, count(eval(AccessTypeValue="DirectNew")) as DirectNew, count(eval(AccessTypeValue="NewFromOld")) as NewFromOld by AgentID State Branch DSM BetaAgency| table State Branch DSM BetaAgency AgentID DirectNew NewFromOld DirectOld |addcoltotals labelfield=AgentID label=TOTALS | addtotals |sort BetaAgency