topic Re: Stats command returning no results if field does not exist. in Splunk Search

Stats command returning no results if field does not exist.

mcrawford44 — Fri, 21 Feb 2014 14:29:02 GMT

My specific example is regarding an Active Directory index. This is my basic query;

index="ad_test" objectClass="*computer*" cn="workstation" | dedup cn | stats count by name lastLogonTimestamp distinguishedName

This returns no results. However when manually searching in Active Directory; The object is there. I ran the following to verify the event was in the index;

index="ad_test" objectClass="*computer*" cn="workstation" | dedup cn

The event exists in the index. However, the event does not have a 'lastLogonTiemstamp' because the object was created manually in Active Directory and the workstation itself never authenticated, or logged on to the domain.

Active Directory aside; The stats command does not return events when a field in the stats query does not exist. How do I compensate for this?

Re: Stats command returning no results if field does not exist.

MuS — Fri, 21 Feb 2014 14:49:46 GMT

Hi mcrawford44,

you could create dummy values for the field if the field does not exists, something like this should work:

 ... | eval foo="N/A" | eval lastLogonTimestamp=coalesce(lastLogonTimestamp,foo) | ..

this will take lastLogonTimestamp if it exists or foo if lastLogonTimestamp does not exist.

hope this helps ...

cheers, MuS

Re: Stats command returning no results if field does not exist.

mcrawford44 — Fri, 21 Feb 2014 15:13:43 GMT

Using;

index="ad_test" objectClass="computer" cn="workstation" | dedup cn | eval name=upper(cn) | eval lastLogonTimestamp=if(isnull(lastLogonTimestamp), "N/A" , lastLogonTimestamp) | stats count by name lastLogonTimestamp distinguishedName

No results returned unfortunately. I'll mess with it a bit unless you see something glaringly wrong. TO clarify, the field is not empty or NULL. It simply has not been instantiated on the object. The field literally does not exist.

Re: Stats command returning no results if field does not exist.

mcrawford44 — Fri, 21 Feb 2014 18:41:03 GMT

Perfect! This worked. You may want to update the parent answer as reference for other viewers.

Re: Stats command returning no results if field does not exist.

MuS — Fri, 21 Feb 2014 21:42:26 GMT

Answer updated, feel free to accept the answer 😉

Re: Stats command returning no results if field does not exist.

anthonyhall — Tue, 16 Jun 2015 02:17:56 GMT

I am stumped by this problem too. The coalesce method doesn't seem to work for me.

I have deleted my index and recreated. At the most fundamental level, this search
index=summarytimingsindex SqlTime > 0
Returns results. SqlTime is a numeric field. But as soon as I attempt any stats (or chart etc), including by adding them from the pop up on the selected field, I get no results returned. I am trying to get avg(SqlTime) but nothing I can do seems to work for this.

Re: Stats command returning no results if field does not exist.

MuS — Tue, 16 Jun 2015 02:49:38 GMT

could it be it is a multi value field? Are you sure about the numeric field?

Re: Stats command returning no results if field does not exist.

anthonyhall — Tue, 16 Jun 2015 02:52:19 GMT

It has a # next to its name in the list of selected fields. How else would I tell? Sorry, I am very new to Splunk, and trying to run a canned app which comes with no support and just dreadful documentation, so I am trying to work out why nothing works.

Re: Stats command returning no results if field does not exist.

MuS — Tue, 16 Jun 2015 02:58:59 GMT

Okay, best thing to do in this case: contact the author of the app over the app page at splunkbase.
But to test a value you can use the isnum() function with eval http://docs.splunk.com/Documentation/Splunk/6.2.3/SearchReference/CommonEvalFunctions

isnum(X)    
This function takes one argument X and returns TRUE if X is a number.   

... | eval n=if(isnum(field),"yes","no")
or
... | where isnum(field)

Re: Stats command returning no results if field does not exist.

anthonyhall — Tue, 16 Jun 2015 03:05:31 GMT

Hi MuS, thanks for taking the time to help. The field is definitely a number based on the result of the above.

The app didn't come from the splunkbase. It came from an enterprise software vendor, and it explicitly comes with no support from them. Very unprofessional of them, I know.