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.
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
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
"*/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.
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 (serverstatus), 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 serverstatus 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.
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.
@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.
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")
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