| Stats wont show output when search only generates one of two values

[mcg_connor]

Im attempting to show when a file was received and when it was sent out. The code below generates the table below that..

index=syslog (source=“Outbound.log" OR source="Inbound.log") 
    |stats values(User) as user, earliest(_time) as received, latest(_time) as sent by externalId
    |eval oc_received = strftime(received, "%F %T")
    |eval oc_sent = strftime(sent, "%F %T")
    |eval oc_time = round((sent - received)/3600,2)
    |fields user, externalId, oc_received, oc_sent, oc_time
    |sort - oc_time

example_user | example_id | 2019-12-24 05:32:22 | 2019-12-24 05:32:22 | 0.50

But won’t output a result when it just received the file but did not send it yet.
Is there a way I could have these show as blank or NULL in the table?
And if it is empty how would I generate the time difference as between now and when it was sent

Thanks!

[aberkow · Accepted solution]

I'm actually a little confused why this happened, given that your only "filtering" command is stats, I tested this code below to see if either fields or sort would filter out if the field was null, and it wasn't:

| windbag 
| table host 
| streamstats count
| eval potato=if(count<3, "baked", null())
| sort -potato # I would have expected this line
| fields host potato # or this line to filter out the lines to reproduce your issue

That being said, I think I can offer a few tips on how to debug and how to move forward.

First, I'd suggest going line by line on an event you know should be included in your search. You can create a slightly more custom initial search (i.e. "index=syslog source="Inbound.log" externalId={IdYouKnowShouldExist}) Make sure it passes the stats command, the evals (evals don't filter so this is very unlikely) and finally the fields and sort. Going line by line should help you in this and going forward debug which command is filtering something out, since Splunk is a pipe based language (the output of each line directly goes into the next one, making it very easy to track down what is going wrong, if not why). If you figure this out, let us know and we can more accurately diagnose, or hopefully this helps you identify your search issue or an issue outside your search (the log you thought should've existed doesn't, etc)

Second, there is a now() function of the eval command which will allow you to fill whichever time field you're looking to fill. You can include that as part of the variable initialization or as a separate line, example:

| eval oc_time=round((coalesce(sent, now()) - received)/3600,2)

You can also separately coalesce sent and now() in an eval command. You can also nullfill with the fill null command, like so:

| fillnull value=0
OR
| fillnull oc_time value=0

You cannot fillnull with the now() parameter unfortunately.

One other thing you can try is removing the "sent" value if it is the same as the "received" value, in the case that you only have one log. You can either do this by filling it with null() or now():

| eval sent=if(sent=received, null(), sent)

Hope this helps!

[mcg_connor]

After going through the logs again I see that the problem seems to be that when I only have an event in the Inbound.log the |stats command uses that for both earliest() and latest(). Is there a way that I can make it look it that specific log when I use |stats?

Thanks for the help!

[mcg_connor]

Is there a way I could possibly just remove the sent value if it is the same as received?

[aberkow]

Yeah! Wow that's good debugging, i should've thought of that. Anyway, you can overwrite the value of any field in certain cases using a simple if block within an eval statement, like so:

| eval sent=if(sent=received, null(), sent)

this basically says "check if the value of sent equals the value of received, if it does, overwrite the value of sent with null(), else, keep the value of sent". I'm also going to add this to my answer.

[aberkow]

Definitely keep those debugging tips in mind in the future, I use them always when going through a Splunk problem. Feel free to accept the answer too if you think it accurately answers the question 🙂

[to4kawa]

But won’t output a result when it just received the file but did not send it yet.

Looking at the code, if the externalId is in both Outbound.log and Inbound.log, the result will be output.

index=syslog (source=“Outbound.log" OR source="Inbound.log") 

Will you provide this sample logs?