help to display a subsearch result after a tostring function

[jip31]

HI

I use the search below which works fine

[| inputlookup host.csv 
    | table host] index="x" sourcetype="winhostmon" Type=Service Name=SplunkForwarder 
| stats latest(_time) as _time by host 
| appendcols 
    [| inputlookup host.csv 
    | stats count as NbIndHost] 
| eval DiffInSeconds = (now() - _time) 
| eval DiffInMinutes=DiffInSeconds/60 
| eval Status=if(DiffInSeconds<90000, "Online", "Offline") 
| eval EventCreatedTime=strftime(_time,"%d-%b-%Y %H:%M:%S %p %Z" ) 
| table host EventCreatedTime DiffInMinutes Status 
| sort +EventCreatedTime 
| dedup host 
| eval Code = if(like(Status,"Online"), "Online", "Offline") 
| stats dc(host) AS OnlineCount by Code 
| where Code = "Online" 
| fields OnlineCount 
| appendpipe 
    [ stats count 
    | where count=0] 
| eval OnlineCount = if(OnlineCount> 0, tostring(OnlineCount) + " host Online","no host online") 

I need to add informations in | eval OnlineCount from another subsearch:
| appendcols
[| inputlookup host.csv
| stats count as NbIndHost]

So after | eval OnlineCount = if(OnlineCount> 0, tostring(OnlineCount) + " host Online","no host online")
I add
+ " / " + NbIndHost + " machines " but it doesnt works
the entire search is

[| inputlookup host.csv 
    | table host] index="x" sourcetype="winhostmon" Type=Service Name=SplunkForwarder 
| stats latest(_time) as _time by host 
| appendcols 
    [| inputlookup host.csv 
    | stats count as NbIndHost] 
| eval DiffInSeconds = (now() - _time) 
| eval DiffInMinutes=DiffInSeconds/60 
| eval Status=if(DiffInSeconds<900, "Online", "Offline") 
| eval EventCreatedTime=strftime(_time,"%d-%b-%Y %H:%M:%S %p %Z" ) 
| table host EventCreatedTime DiffInMinutes Status 
| sort +EventCreatedTime 
| dedup host 
| eval Code = if(like(Status,"Online"), "Online", "Offline") 
| stats dc(host) AS OfflineCount by Code 
| where Code = "Offline" 
| fields OfflineCount 
| appendpipe 
    [ stats count 
    | where count=0] 
| eval OfflineCount = if(OfflineCount> 0, tostring(OfflineCount) + " host Offline","no host offline") + " / " + NbIndHost + " machines "

what is the problem please??

[FrankVl]

You first do the appendcols... to get the NbIndHost and then you do a | stats dc(host) AS OfflineCount by Code. After that stats command you will only have 2 fields: OfflineCount and Code. All other fields are gone when you do a transforming stats command like that. Edit: I now also see that you even do a table command where you don't include the NbIndHost field, so it is already gone even before you do the stats command.

Probably moving the appendcols... bit to after the stats will solve your issue.

Also a general remark: "it doesnt works" is not very helpful information for others to help you find the issue. Be as specific as you can in what exactly doesn't work. What results do you get, how are those different from what you need.

And another tip: if your search does not get you what you expect: break it down into pieces. Execute the first few lines, see if that still looks good, then step by step add the additional logic. That would have easily helped you spot that the field you need disappears after doing that stats command.

[jip31]

thanks for your complete explanations
do you mean something like this? (I have no results)

| stats dc(host) AS OnlineCount by Code 
| appendcols 
    [| inputlookup host.csv 
    | stats count as NbIndHost] 
| where Code = "Online" 
| fields OnlineCount NbIndHost
| appendpipe 
    [ stats count 
    | where count=0] 
| eval OnlineCount = if(OnlineCount> 0, tostring(OnlineCount) + " host Online","no host online") + " / " + NbIndHost + " machines "

[FrankVl]

Something like that, yes (I'm assuming you're only posting the relevant part of the search here).

And you are very sure that there should be results, there are online hosts?

As I mentioned: break down the search, run it step by step and see where it goes wrong.

[jip31]

yes sure...
I have run the search by step but I doesnt find solution (I am splunk dev rookie ;-)...

I dont understand why I cant catch the field NbIndHost in :
| eval OnlineCount = if(OnlineCount> 0, tostring(OnlineCount) + " host Online","no host online") + " / " + NbIndHost + " machines "

[FrankVl]

Well, I don't see anything wrong with that specific line and when I build a simple test case:

| makeresults | eval OnlineCount = 4 | eval NbIndHost = 40 
| eval OnlineCount = if(OnlineCount> 0, tostring(OnlineCount) + " host Online","no host online") + " / " + NbIndHost + " machines "

I get the expected "4 host Online / 40 machines "

So somewhere in your search there must be an issue that breaks it for you. If you start with your full search and then remove one line from the bottom, execute, check results, remove one more line, check again etc. it should be possible to find which part of the search works and at which point it breaks.

[jip31]

hello vranck

I found
its due to :
| appendpipe
[ stats count
| where count=0]

so I have done this :

| stats dc(host) AS OfflineCount by Code 
| appendcols 
    [| inputlookup host.csv 
    | stats count as NbIndHost] 
| where Code = "Offline" 
| fields OfflineCount NbIndHost 
| eval OfflineCount = if(OfflineCount> 0, tostring(OfflineCount) + " host Offline","no host offline") + " / " + NbIndHost + " machines " 
| fields - NbIndHost

does it seems correct?

[jip31]

there is just "no host offline" which doesnt display when result is 0
its 0 which is displayed