field="URL1 OR URL2 OR URL3"
I need to search each URL in . If the search is returns values, count >0 then it's Passed. If it doesn't, count ==0 then it's Failed. I will need to display a table with below format
List of URL Status
URL1 Passed
URL2 Failed
URL3 Passed
Kindly let me know how to create query for this use case. Thanks in advance
Give this a try
index=xyz | rex "(?i)find url (?P[^ ]+)" | search [|gentimes start=-1 | eval URI=split("URL1 OR URL2 OR URL3 OR URL4 OR URL5"," OR ") | table URI| mvexpand URI] | stats count by URI
| append [|gentimes start=-1 | eval URI=split("URL1 OR URL2 OR URL3 OR URL4 OR URL5"," OR ") | table URI| mvexpand URI | eval count=0]
| stats max(count) as count by URI
| eval Status=if(count=0,"Failed","Passed")
| table URI Status
Give this a try
index=xyz | rex "(?i)find url (?P[^ ]+)" | search [|gentimes start=-1 | eval URI=split("URL1 OR URL2 OR URL3 OR URL4 OR URL5"," OR ") | table URI| mvexpand URI] | stats count by URI
| append [|gentimes start=-1 | eval URI=split("URL1 OR URL2 OR URL3 OR URL4 OR URL5"," OR ") | table URI| mvexpand URI | eval count=0]
| stats max(count) as count by URI
| eval Status=if(count=0,"Failed","Passed")
| table URI Status
Awesome, This worked...
This is my current query
|gentimes start=-1 | eval field2=split("URL1 OR URL2 OR URL3 OR URL4 OR URL5"," OR ") | eval field3="" | mvexpand field2 | eval field3 =[search index=xyz | rex "(?i)find url (?P[^ ]+)" | search URI="\"".field2."\"" | stats count as mycount | return $mycount] | table field2 , field3
Note: I didn't added Pass/fail if condition, When i added it say failed always.
Field2 Field3
URL1 0
URL2 0
URL3 0
URL4 0
URL5 0
Try this:
index=YourIndexHere AND field IN("URL1", "URL2", "URL3")
| stats count(eval(field="URL1")) AS URL1 count(eval(field="URL2")) AS URL2 count(eval(field="URL3")) AS URL3
| foreach * [ eval <<FIELD>>=if((<<FIELD>> >0), "Passed", "Failed") ]
This didn't work. I added transpose the table and It shows only 4 rows at the most.
Thanks for your response
Could you try the following?
index=<your_index> field=URL1 OR field=URL2 OR field=URL3
| stats count as Status by field
| eval Status=case( Status == 0, "Failed",
true(), "Passed")
| rename field as "List of URL"
Error in 'eval' command: The expression is malformed. An unexpected character is reached at '= 0, "Failed", true(), Passed)'.
I replaces Case with If statement.
eval Status=if(Status == 0, "Failed","Passed")
Now the result is
URL1 Passed
URL3 Passed
All Failed status are skipped. I am looking for a solution to display all the URLs with both Passed and Failed.
Well actually, if the URL2 is not in any event returned by the search it is normal it does not appear.
Is it only 3 URLs you are searching for? If so, try
index=<your_index> field=URL1 OR field=URL2 OR field=URL3
| stats count(eval(field="URL1")) AS URL1 count(eval(field="URL2")) AS URL2 count(eval(field="URL3")) AS URL3
| transpose column_name="List of URL"
| rename "row 1" as Status
| eval Status=case( Status == 0, "Failed",
true(), "Passed")
Inspired from @woodcock answer below
Whoops i've missed something, edited my answer (sorry was "air splunking")