topic Re: multivalue in a sub search not working as expected. in Splunk Search

multivalue in a sub search not working as expected.

sukundur — Sat, 09 Dec 2017 15:35:34 GMT

I am trying to return multi value from a subsearch and use that value in a field (server_status) as "OUT" in the main search and rest as "IN". Instead, it displaying all the values as IN.

index=*  sourcetype="rock:access" "/status/*"  | stats count(eval(rock_status>=200 AND rock_status<=599)) AS TOTAL by host | eval  server_status=if(rock_status=404,[search index=* host=server*  sourcetype="rock:access" "*/livelive.test"  | search rock_status=404 | return $host ],IN) | table host,server_status, TOTAL

I am trying to get something like below

 host   server_status   total
server1     IN          64434
server2     IN          63517
server3     IN          25536
server4     IN          7090
server5     OUT         9
server6     OUT         2
server7     OUT         7

can you please help.

We marked code and data as code to ease reading.

Re: multivalue in a sub search not working as expected.

elliotproebstel — Sun, 10 Dec 2017 19:05:24 GMT

Ok, I am going to try to untangle this, because the structure of your search has left me somewhat confused. Let me see if I understand your intention:

You have some events with sourcetype="rock:access". Each of those events contains a field called host and another field called rock_status. You want to count the number of events for each host that match some criteria and also, based on those criteria, create a field called server_status. It appears to me that if an event contains "/status/" and the field rock_status is between 200 and 599 (inclusive) then you want the field server_status to be IN. If the event contains "*/livelive.test" and the field rock_status is 404, then you want the field server_status to be OUT. Then you want to count the number of events for each host and assign the count to a field called total. Finally, you want to make a table showing host, server_status, and total.

If that's all correct, then I think this should do the trick for you:

sourcetype="rock:access" "status" OR "livelive.test" rock_status=* 
| eval server_status=case
  (rock_status>=200 AND rock_status<=599 AND match(_raw, "\/status\/"), "IN", 
  rock_status=404 AND match(_raw, "\/livelive\.test"), "OUT", 
  1=1, "Not Categorized") 
| stats count AS total BY host server_status

Note: If "/status/" and "*/livelive.test" are captured in a field, it would be best to replace the instances of _raw in that code with the name of the field.

Please be aware that the code above does not guarantee that every host will be assigned only one server_status code. If some events for a given host match the conditions for IN and some match the conditions for OUT, both will be reflected in the final table. I also left an optional catchall at the end of the case statement to capture events that don't match the category assignments. If you want to silently discard those events, you can remove the 1=1, "Not Categorized" from the end of the case statement and add | where isnotnull(server_status) immediately before | stats count AS total BY host server_status.

If my understanding was incorrect, please feel free to clarify, and I'll try to help again.

Re: multivalue in a sub search not working as expected.

sukundur — Tue, 29 Sep 2020 17:11:31 GMT

Thanks for your response and sorry for not being clear. Below is what I am trying to acheive.

1) for rock_status between 200 and 599, I am counting as TOTAL requests received on the server. this also includes 404's if the server is out.

2) In another field (server_status), I am trying to see if the server is out of rotation by checking the log file for "livelive.test" for 404's with and mark the the server_status field value as OUT and if "livelive.test" is 200's, I update the server_status field value with 200.. for corresponding server.

apologize for not being clear earlier.

Re: multivalue in a sub search not working as expected.

elliotproebstel — Tue, 12 Dec 2017 18:20:35 GMT

So does the code above give you the correct output? If not, can you give some examples of the raw data that's being incorrectly categorized and how you'd prefer it to be categorized? I'm not totally certain what is different between what I documented/proposed and what you've said here. Thanks.

Re: multivalue in a sub search not working as expected.

DalJeanis — Tue, 12 Dec 2017 19:54:49 GMT

@elliot - Looks like a good attempt. I'd probably try to present the results on one line per server.

I believe the OP may be looking for the most recent status code being 404 to mean "out"... in which case you could get latest(rock_status) as xxx when you do your stats count, and then use xxx to calculate the IN/OUT.

Re: multivalue in a sub search not working as expected.

elliotproebstel — Tue, 12 Dec 2017 21:09:43 GMT

Thanks @dal - if you're right, then perhaps this code would work?

 sourcetype="rock:access" "status" OR "livelive.test" rock_status=* 
| stats latest(rock_status) AS  server_status count AS total BY host 
| eval server_status=if(server_status=404, "OUT", "IN")

Re: multivalue in a sub search not working as expected.

maraman_splunk — Fri, 18 Jan 2019 16:57:49 GMT

Hi,

I think you need to remake the fields multivalue after the subsearch

| makeresults | eval testx="test1 test3", index="1" | makemv testx | join type=outer index [ | makeresults | eval test="test1 test2", index="1" | makemv test] | makemv test