How can I run the same search "looping" through a series of different URI group inputs?

[peters49]

Hello,

I am running Splunk 7.1.4 om AMI Linux, splunk web from Windows 10 desktop. I am trying to create a report that provides average time_taken, error count, #unique IPs, and total hits for 24 URI "Groups"

For example  cs_uri_stem=/Group1* would give cs_uri_stem=/Group1, cs_uri_stem=/Group1/subgroup1, cs_uri_stem=/Group1/subgroup2 etc

The following works for one group, that I specifically define in the search 

------------------------------------------------------------------------------------------------------------------------------------

index=index host=host sourcetype=iis  cs_uri_stem=/Group1*

| stats avg(time_taken) as avgtime count(eval(sc_status>=400)) as "Total #errors" dc(x_Forwarded_For) as "Total # Unique ClientIPs" min(cs_uri_stem) as cs_uri_stem  count
| eval avgtime=round(avgtime,0)
| eval cs_uri_stem=rtrim(cs_uri_stem,"*")

_____________________________________________________________________________________

This gets me to a table with one row that has the combined data for the group1 URIs

The question I have is how can I run this search  "looping"  through all 24  URI groups and end up with a table showing data for all 24 URI groups..  I have added the Group* names to a lookup file and tried using the lookup in a sub search but I could get that to work.  Also tried a number of different approaches without success.

I hope I have explained the issue clearly.  Any suggestions/comments greatly appreciated.

[richgalloway]

Have you tried this?

index=index host=host sourcetype=iis  cs_uri_stem=/Group*
| rex field=cs_uri_stem "\/(?<Group>[^\/]+)"
| stats avg(time_taken) as avgtime count(eval(sc_status>=400)) as "Total #errors" dc(x_Forwarded_For) as "Total # Unique ClientIPs" min(cs_uri_stem) as cs_uri_stem  count by Group
| eval avgtime=round(avgtime,0)

[peters49]

Hello,

Thank you. This did get me one row with a result.

The larger problem I am facing and maybe I haven't made this clear enough.  The user, who requested the report, gave me a csv file with all of the URI* names.  I have put the list in a lookup.  I need to run the same search for each value in the lookup.  Each value would summarize the data for its  uri group in a table row.  I have tried implementing a subsearch using the lookup but i couldn't get that to work.

[richgalloway]

How about this use of a subsearch to read the CSV?  Insert a rename command in the subsearch if the field in the CSV is not 'cs_uri_stem'.

index=index host=host sourcetype=iis  [ | inputlookup uri.csv | fields cs_uri_stem | format ]
| rex field=cs_uri_stem "\/(?<Group>[^\/]+)"
| stats avg(time_taken) as avgtime count(eval(sc_status>=400)) as "Total #errors" dc(x_Forwarded_For) as "Total # Unique ClientIPs" min(cs_uri_stem) as cs_uri_stem  count by Group
| eval avgtime=round(avgtime,0)

[peters49]

Hi richgalloway,

 I do see that the lookup subsearch expands into a series of "OR" statements that contains every uri in the csv.  However, when I run the search I just get one group returned.

[richgalloway]

Does this query show a single group or many?

index=index host=host sourcetype=iis  [ | inputlookup uri.csv | fields cs_uri_stem | format ]
| rex field=cs_uri_stem "\/(?<Group>[^\/]+)"
| table cs_uri_stem, Group

If there are multiple values of cs_uri_stem and only one Group value then we may have a problem with the rex command and we'll need some sample data to help you fix that.

If there are multiple values for the Group field then the problem lies elsewhere.

[peters49]

Hi richgalloway,

All the group names are the same, the cs_uri_stem values are mostly the same as the group value, with the exception of  2 rows which showed a different value. I can provide the results if that would be helpful.

I appreciate your assistance; I want to take another look at what I have done recently independent to your suggestions and make sure what you are thinking is going to get my user what he wants in the end. I want to make sure I am being clear in my explanation.

I have run 2 test searches.  In the first one, I manually set the value of cs_uri_stem to a value in the lookup, for example, /uri_1* and I count by cs_uri_stem. This gives me 3 rows (for this example)

"/uri_1"

"/uri_1/subA"

"/uri_1/subB"

Each row in the result gives me the parameter values specific to that row.  The rows when taken together equal a "group" for the lookup entry /url_1*

In the second search I again manually set cs_uri_stem= /uri_1* but this time I do not count by cs_uri_stem.  I just count the number of events returned.  This returns one row which contains the data for the 3 rows returned in the sample search above., which gives me the combined data values for the "group" /uri_1*.  What my user wants is a report with each row listing the Group name( in this case /uri_1*) but with the combined data for  /uri_1 plus any sub uri  returned.

So, intuitively, it seems that what is needed is to run the search with the first group name only counting the total events, then save the results of that search before executing the  next search with the second group in the lookup, etc; somehow ending up with a report where each row shows the group name and the combined data for that group. Maybe there is totally different way to do it?

Please let me know if you have any question about how I am explaining the issue.

 Appreciate your help

 peter

[acharlieh]

Hey Peter...  I might have a solution to this, with two different usages of your group listing. Now I'm assuming that your lookup called groups.csv has consists of two fields... `match` being the wildcarded string you want to match against the cs_uri_stem field, and `group` being the group label you want to assign to events that match the corresponding match field (they could be the same field of course, but having separate fields lets you combine multiple wildcard matchers into one group)

index=... sourcetype=... 
    [ inputlookup groups.csv | rename match -> cs_uri_stem | fields cs_uri_stem ]
| eval 
    [ inputlookup groups.csv 
    | eval arg="case(searchmatch(\"cs_uri_stem=".match."\"),\"".group."\")"
    | stats values(arg) as search 
    | eval search="group=mvdedup(mvappend(".mvjoin(search,",")."))" 
    ]
| stats ... by group

What's happening here is with our first subsearch, we're injecting the selector for each match in our lookup... 
Then our second subsearch builds out an eval command to label each event based on which matches qualify for that particular event... and then we do stats by the result of that eval.

I might be missing some escaping for the general case, but being uris... that's likely not an issue.

An alternative solution, (if the groupings are fairly static and not just for a particular report... You could build and share eventtypes, that are tagged following a particular naming convention... then search for the tag pattern, and filter down the list of tags on events...

What do you think?

[peters49]

Hi acharlieh, The csv file only has one column, named cs_uri_stem. The list is static. As an interim I have provided a workaround, just to get something to the user. I created 23 separate reports, one for each item in the list. they all write their results to a lookup file and the user report runs from the output lookup file. It seems to work, but it is not pretty. If you could suggest a more elegant was to do this it would be appreciated. I don't fully understand the SPL included in your message. Thanks peter

[acharlieh]

@peters49 I feel I already suggested a more elegant way, with one search to get the statistics for all elements of the lookup... we're dynamically building an eval statement in the parent search based on the given lookup to identify which urls match which groups, using a feature of subsearches, and a bunch of eval statements... 

If there is only one column, named cs_uri_stem, then what I was calling match and group both reduce to cs_uri_stem:

index=... sourcetype=... 
    [ inputlookup groups.csv ]
| eval 
    [ inputlookup groups.csv 
    | eval arg="case(searchmatch(\"cs_uri_stem=".cs_uri_stem."\"),\"".cs_uri_stem."\")"
    | stats values(arg) as search 
    | eval search="group=mvdedup(mvappend(".mvjoin(search,",")."))" 
    ]
| stats ... by group

Assuming your lookup (which I called groups.csv) you had two entries /Group1* and /Group2* then after the subsearches execute you would be left with a parent search that looks like: 

index=... sourcetype=... ( ( cs_uri_stem="/Group1*" ) OR ( cs_uri_stem="/Group2*" ) )
| eval group=mvdedup(mvappend(case(searchmatch("cs_uri_stem=/Group1*"),"/Group1*"), case(searchmatch("cs_uri_stem=/Group2*"),"/Group2*")))
| stats ... by group

 
(I'm of course using ... as a placeholder for your particular index, sourcetype, and statistics that you want to evaluate per group as it's not part of the problem.)

Returning a field named search from a subsearch is covered a little bit in the docs about changing subsearch results (but I think it was in much more depth in the Advanced Searching and Reporting class... it's admittedly been a while since I took those Splunk courses ) and all of the functions I'm using with eval can be found in the docs around CommonEvalFunctions . But if after reviewing the docs and the above you still have specific questions about things that don't make sense to you, then ask.

[peters49]

Hi richgalloway,

Thank you for your response.  I should been more clear in my example.  Each "Group" has a different name,  the * has already been added to the names of all the all the URI names in the lookup file 

Updated example:

first "Group"

  cs_uri_stem=/firstUriName* would give cs_uri_stem=/firstUriName, cs_uri_stem=/firstUriName/subsuburi1, cs_uri_stem=/firstUriName/suburi2 etc

Another" group"

cs_uri_stem=/"secondUriName"* would give cs_uri_stem=/secondUriName, cs_uri_stem=/secondUriName/suburi1, cs_uri_stem=/secondUriName/suburi2 etc

i

[richgalloway]

I modified my original answer to extract the first part of the cs_uri_stem field into a "Group" field and grouped results by that.