topic Re: How to create a query for below scenarios? in Splunk Search

How to create a query for below scenarios?

lawzuns — Fri, 20 Apr 2018 14:54:13 GMT

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

Re: How to create a query for below scenarios?

damien_chillet — Fri, 20 Apr 2018 16:10:14 GMT

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"

Re: How to create a query for below scenarios?

lawzuns — Fri, 20 Apr 2018 16:40:54 GMT

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

Re: How to create a query for below scenarios?

damien_chillet — Fri, 20 Apr 2018 16:45:49 GMT

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

Re: How to create a query for below scenarios?

lawzuns — Fri, 20 Apr 2018 16:47:43 GMT

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.

Re: How to create a query for below scenarios?

woodcock — Sun, 22 Apr 2018 19:50:11 GMT

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

Re: How to create a query for below scenarios?

damien_chillet — Mon, 23 Apr 2018 09:08:11 GMT

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

Re: How to create a query for below scenarios?

lawzuns — Mon, 23 Apr 2018 19:19:46 GMT

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

Thanks for your response

Re: How to create a query for below scenarios?

lawzuns — Mon, 23 Apr 2018 20:18:58 GMT

This is my current query

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

Re: How to create a query for below scenarios?

somesoni2 — Mon, 23 Apr 2018 20:33:23 GMT

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

Re: How to create a query for below scenarios?

lawzuns — Tue, 24 Apr 2018 03:24:47 GMT

Awesome, This worked...