Stats command returning no results if field does not exist.

[mcrawford44]

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?

[MuS · Accepted solution]

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

[anthonyhall]

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.

[MuS]

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

[anthonyhall]

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.

[MuS]

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)

[anthonyhall]

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.

[MuS]

Answer updated, feel free to accept the answer 😉

[mcrawford44]

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

[mcrawford44]

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.