Splunk Search

How to create a query for below scenarios?

lawzuns
Explorer

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

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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
Explorer

Awesome, This worked...

0 Karma

lawzuns
Explorer

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

0 Karma

woodcock
Esteemed Legend

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
Explorer

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

Thanks for your response

0 Karma

damien_chillet
Builder

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
Explorer

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

0 Karma

lawzuns
Explorer

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.

0 Karma

damien_chillet
Builder

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

0 Karma

damien_chillet
Builder

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...