Splunk Search

How to create unique string values tied to a field for URL patterns matches?

jgbricker
Contributor

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
0 Karma
1 Solution

jgbricker
Contributor

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

View solution in original post

jgbricker
Contributor

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
Get Updates on the Splunk Community!

AppDynamics Summer Webinars

This summer, our mighty AppDynamics team is cooking up some delicious content on YouTube Live to satiate your ...

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...