How to create a query for below scenarios?

[lawzuns]

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

[somesoni2 · Accepted solution]

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

[lawzuns]

Awesome, This worked...

[lawzuns]

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

[woodcock]

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") ]

[lawzuns]

This didn't work. I added transpose the table and It shows only 4 rows at the most.

Thanks for your response

[damien_chillet]

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"

[lawzuns]

Error in 'eval' command: The expression is malformed. An unexpected character is reached at '= 0, "Failed", true(), Passed)'.

[lawzuns]

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.

[damien_chillet]

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

[damien_chillet]

Whoops i've missed something, edited my answer (sorry was "air splunking")