Splunk Search

How get reults to say N/A while using the "inputlookup" command?

SubtotalAMG
Loves-to-Learn Lots

I'm not a programmer but I am trying to get the display of my graph to depict "No Results" or "N/A" when the Where command can't find the specific name within the csv. Rather what I get is all of the servers listed within the excel. Here is quick example:

This works for me

index=House sourcetype=LivingRoom
[ | inputlookup HouseInventory.csv | where Room="Bathroom" | return host=$X_Furniture ]
| timechart span=5m count by host

But what happens is if a user types "where Room="Bathr00mZ"....see below......I get a list of all the servers listed in my csv which is what I don't want. I rather have it say "No Results" or "N/A"

index=House sourcetype=LivingRoom
[ | inputlookup HouseInventory.csv | where Room="Bathr00mZ" | return host=$X_Furniture ]
| timechart span=5m count by host

I've tried this:

index=House sourcetype=LivingRoom
[ | inputlookup HouseInventory.csv | where Room="Bathr00mZ" | eval res=if(Room=="Bathroom",X_Furniture,"Null") ]
| timechart span=5m count by host

But this still comes back with the list of all the servers. 

Labels (1)
Tags (4)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

Use this construct

index=House sourcetype=LivingRoom
[ | inputlookup HouseInventory.csv 
  | where Room="Bathroom" 
  | rename X_Furniture as host
  | appendpipe [
    | stats count | where count=0
    ``` Add in what you want the default to be ```
    | eval host="*"
  ]
]
| timechart span=5m count by host

I assume the field in the lookup that corresponds to host is X_Furniture

You just need to let the subsearch return and it will effectively return host=bla

The appendpipe will make host=* if there are no values from the inputlookup - so set that value to be the default you want.

0 Karma

SubtotalAMG
Loves-to-Learn Lots

Still the same results...still displays all of them.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Exactly how it should work if you set = *

If you want the search to return NO results, you need to give the subsearch something that will make the outer search not find anything, e.g. host=_there_is_no_such_host

in which case, then the outer search (probably) won't find any results, then you get no results found.

If you are in a dashboard, you can then add some code after the search to force a count of 0, e.g.

| appendpipe [
| stats count as NoHost| where NoHost=0
| eval _time=now()
]

but then that won't give you much of a timechart, so then you need to work out what should show instead of a timechart - if you want a simple single value viz, you will have to start playing with having multiple panels, one for a timechart and one for a single value viz, where your tokens decide which one gets shown.

See this for more info

https://docs.splunk.com/Documentation/Splunk/latest/Viz/PanelreferenceforSimplifiedXML

 

0 Karma

SubtotalAMG
Loves-to-Learn Lots

No good still

0 Karma

bowesmana
SplunkTrust
SplunkTrust

So what did you try and what was the result and how do you want your timechart to look in that context?

0 Karma

SubtotalAMG
Loves-to-Learn Lots

I tried this and still it lists the same results. (Everything is still listed), Also "$X_Furniture" is a column in the csv file as well so the "$" is also needed. 

 

 

index=House sourcetype=LivingRoom
[ | inputlookup HouseInventory.csv 
  | where Room="Bathroom" 
  | rename X_Furniture as host
  | appendpipe [
    | stats count | where count=0
    ``` Add in what you want the default to be ```
    | eval host="No such Host"
  ]
]
| timechart span=5m count by host

 

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

If the column is $X_Furniture, then change the rename to

| rename "$X_Furniture" as host

You should be able to see what the subsearch returns by just running it on its own. You can add the 

| format

to the end of the search if you run it standalone, i.e.

| inputlookup HouseInventory.csv 
  | where Room="Bathroom" 
  | rename "$X_Furniture" as host
  | appendpipe [
    | stats count | where count=0
    ``` Add in what you want the default to be ```
    | eval host="No such Host"
  ]
| format

and you can see how that acts as a constraint to the main outer search.

You still haven't said how you want your timechart should look like when the Room is not found - are you showing the timechart as a graph visualisation or simply as a table?

0 Karma
Get Updates on the Splunk Community!

Changes to Splunk Instructor-Led Training Completion Criteria

We’re excited to share an update to our instructor-led training program that enhances the learning experience ...

Stay Connected: Your Guide to January Tech Talks, Office Hours, and Webinars!

❄️ Welcome the new year with our January lineup of Community Office Hours, Tech Talks, and Webinars! 🎉 ...

Preparing your Splunk Environment for OpenSSL3

The Splunk platform will transition to OpenSSL version 3 in a future release. Actions are required to prepare ...