Splunk date not displaying in table correctly

[mrclayton]

Have an event that includes an object's due date in the format Fri Jul 06 00:00:00 PDT 2018. When I view the event via a search the due date is displayed correctly. However, when creating a table report via a dashboard, the date only displays as Fri so it appears it's being stripped by Splunk during the table render. I tried to use the function strptime on the date using format %a %b %d %T %Z %Y but further tests using eval revealed that the due date value is sent to the function as Fri before I can even manipulate it.

Kind Regards,

[niketn · Accepted solution]

@mrclayton, the reason why Search Time Field Discovery is not working by default is that the Key Value pair are both present within same Quotes. So space after Week abbreviation i.e. Fri, Sat etc will be treated as breakers for default Field Extraction. Following run anywhere example Mimics the issue behavior as to what is happening with your _raw data during Search Time Field Discovery:

| makeresults
| eval _raw="\"dueDate=Fri Jul 06 00:00:00 PDT 2018\" \"invoiceDate=Wed Jul 04 00:00:00 PDT 2018\""
| KV

Ideal way would be to correct the data to have key="value" format so that default Search Time Field Discovery works for all the fields by default. Following run anywhere example shows behavior with data fixed:

| makeresults 
| eval _raw="dueDate=\"Fri Jul 06 00:00:00 PDT 2018\" invoiceDate=\"Wed Jul 04 00:00:00 PDT 2018\"" 
| KV

Other option would be create your own field extraction using correct regular expression. Add the following rex command after your base search | rex "\"dueDate=(?<dueDate>[^\"]+)\"\s"

Following is a run anywhere example with rex command (regular expression based field extraction):

| makeresults
| eval _raw="\"dueDate=Fri Jul 06 00:00:00 PDT 2018\" \"invoiceDate="
| rex "\"dueDate=(?<dueDate>[^\"]+)\"\s"

Once you have tested the extraction to work with your sample data, you should move the same to Fields Knowledge Object using Interactive Field Extraction or props.conf directly using regular expression above.

[mrclayton]

@niketnilay, thank you for such a thorough response. I will try your suggestions and provide an update along with an accepted answer if applicable.

What's interesting about the key and value being in the same quotes is that's how the JAVA library outputs the values. The following code sample is taken directly from the Java class that outputs the event data.

private static final Pattern DOUBLE_QUOTE = Pattern.compile("\"");
    @Override
    public String toString() {
        StringBuilder output = new StringBuilder();

        boolean first = true;
        for (String key : entries.keySet()) {
            if (!first) {
                output.append(PAIRDELIM);
            } else {
                first = false;
            }
            String value = entries.get(key).toString();

            // Escape any " that appear in the key or value.
            key = DOUBLE_QUOTE.matcher(key).replaceAll("\\\\\"");
            value = DOUBLE_QUOTE.matcher(value).replaceAll("\\\\\"");

            output.append(QUOTE).append(key).append(KVDELIM).append(value).append(QUOTE);
        }

        return output.toString();
    }

[niketn]

@mrclayton, Seems like you are using Post Processing. While using Post-processing in your dashboard you should ensure that you aggregate the results using transforming commands to restrict the total number of search results being passed on from one search to another. Ideally they should not end with table, rather stats or other aggregating transforming command.

In case you you want to pass on all the fields from raw events, you can use | fields * as your final pipe in your search. However, they still may have performance issues and may not pass on all the results. You should refer to documentation for post-processing best practices to ensure that it handles generating stats in your base search and then re-use them in other panels. In case you can not do so, you would be better off running independent searches instead of post-processing. Refer to Post Processing Best Practices Documentation.

Refer to one of my older answers: https://answers.splunk.com/answers/607583/why-doesnt-my-xml-base-search-work.html

[mrclayton]

Hi @niketnilay thanks for the reply. Unfortunately this does not seem to explain why the dueDate field is being displayed as Fri.

For example, if I perform the following search:

index="test" sourcetype="my:sourcetype" name="payBill" host="example.com" userId="*" dueDate="Fri Jul 06 00:00:00 PDT 2018"

I don't get back any results. But if I perform:

index="test" sourcetype="my:sourcetype" name="payBill" host="example.com" userId="*" dueDate="Fri"

I get the expected result. So, it appears the dueDate field is being stripped even before I have access to it.

Kind Regards,

[niketn]

@mrclayton I am sorry I had confused your question with someone else's after reading the last comment. You would need to create your own field extraction on raw data to extract dueDate correctly.

[mrclayton]

Hi All,

Thank you for the replies. I have included the base event search which renders the dueDate correctly, along with the dashboard search which renders the dueDate incorrectly.

index="my_index" sourcetype="my:logging:system" name="payBill" host="$host$" userId="$userId$" | eval date=strftime(timestamp/1000, "%c")|table name host userId clientId date legacyAccount partialAmount invoiceId vbeln xblnr dueDate invoiceDate| rename name as "Event", host as "Server", userId as "User ID", clientId as "Client ID", date as "Date", vbeln as "Billing Document", xblnr as "Reference Document", legacyAccount as "Legacy Account", dueDate as "Due Date", invoiceDate as "Invoice Date", invoiceId as "Invoice ID", dueDate as "Due Date", partialAmount as "Payment Amount"

[woodcock]

This should be trivial to fix if you show us your exact search.

[niketn]

@mrclayton, if you are seeing the date field dueDate with values like Fri Jul 06 00:00:00 PDT 2018 in your raw events, you can directly use them in your table without using strftime() as that will be in String Date format already by default. If in your search you need to convert it to epoch strptime(), then for displaying epoch time as string time in table you can use fieldformat in the end. Following is run anywhere search based on sample field/data provided as per the question.

| makeresults
| eval dueDate="Fri Jul 06 00:00:00 PDT 2018"
| eval "Due Date"=strptime(dueDate,"%a %b %d %T %Z %Y")
| fieldformat "Due Date"=strftime('Due Date',"%a %b %d %T %Z %Y")

If your issue is something else, please add more details from your current search as to what modifications are being done on the Due Date field.

[kmorris_splunk]

To renjith.nair's point, please share your search. It's always helpful to share any data samples or searches.

[renjith_nair]

Hi @mrclayton, is it possible to share the search (you shall sanitize confidential data if any) ? It looks like one of your eval is stripping the week day. Try adding the strptime just after the base search

[mrclayton]

Base Search (works):

index="my_index" sourcetype="my:logging:system" name="payBill" host="$host$" userId="$userId$" | eval date=strftime(timestamp/1000, "%c")|table name host userId clientId date legacyAccount partialAmount invoiceId vbeln xblnr dueDate invoiceDate| rename name as "Event", host as "Server", userId as "User ID", clientId as "Client ID", date as "Date", vbeln as "Billing Document", xblnr as "Reference Document", legacyAccount as "Legacy Account", dueDate as "Due Date", invoiceDate as "Invoice Date", invoiceId as "Invoice ID", dueDate as "Due Date", partialAmount as "Payment Amount"

Dashboard Search (doesn't work):

index="my_index" sourcetype="my:logging:system" name="payBill" host="$host$" userId="$userId$" | eval date=strftime(timestamp/1000, "%c")|table name host userId clientId date legacyAccount partialAmount invoiceId vbeln xblnr dueDate invoiceDate| rename name as "Event", host as "Server", userId as "User ID", clientId as "Client ID", date as "Date", vbeln as "Billing Document", xblnr as "Reference Document", legacyAccount as "Legacy Account", dueDate as "Due Date", invoiceDate as "Invoice Date", invoiceId as "Invoice ID", dueDate as "Due Date", partialAmount as "Payment Amount"