Archive

Modifying an input for dashboard. (Change a time format to fit the _time format)

Path Finder

(Sorry if this is confusing)

I want to create a dashboard to find like events that happens at a certain time. This is going to be searching a datamodel so I can see all the events that happens at a certain time. I want to be able to input in one format for the token and have it search in another but I'm running into problems to figure this out. I want to modify "1/1/18 2:00:20.000 PM" format to fit into the _time field "2018-01-01T14:00:20.000-06:00" so i can search with that.

Any ideas on how I can achieve this? I'm looking at the XML code but I'm confused on how I'd achieve that.

Much thanks,

Tags (1)
0 Karma
1 Solution

Although there are several ways to go about this, I'd convert from string format into UNIX time and then back into another string format. Here's a run-anywhere code sample that shows how I'd go from "1/1/18 2:00:20.000 PM" to "2018-01-01T14:00:20.000" Note: your sample had the desired output of a time string with "-06:00" at the end, but I wasn't sure what your intent was with that part.

| stats count
| eval timeformat1="1/1/18 2:00:20.000 PM" 
| eval timeformat2=strftime(strptime(timeformat1, "%x %I:%M:%S.%3Q %p"), "%FT%H:%M:%S.%3Q")

This will take the first field and convert it to UNIX time using the strptime command and then immediately convert it back into a string using the strftime command. The magic here is in the time format strings. You can read more about them here:
https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Commontimeformatvariables

View solution in original post

Champion

Here's XML for potentially a partial solution, which allows you to set the earliest time of a search based on a specific time format in an input:

<form>
  <label>608999</label>
  <search id="format_start_time">
    <query><![CDATA[| makeresults | eval start_time_string="$start_time_tok$" | eval start_time_epoch=strptime(start_time_string, "%x %I:%M:%S.%3Q %p") | fields start_time_epoch]]></query>
    <done>
      <condition match="'job.resultCount' == 1">
        <set token="start_time_epoch">$result.start_time_epoch$</set>
      </condition>
    </done>
  </search>
  <fieldset submitButton="true" autoRun="false">
    <input type="text" token="start_time_tok">
      <label>Time</label>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>index=_internal | stats count</query>
          <earliest>$start_time_epoch$</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
</form>

Path Finder

I don't think this is what I'm looking to do, not that I know XML well enough to tell. The search timeframe is 24 hours for this search. Since we're always working with recent data. What I'm trying to do is search with the output of another dashboard I put into a token. But that output isn't the correct format to search it to match against "_time". So I wanted to convert it over so I could search it using _time. This will pull up 2 or 3 events that explain what happened in a certain situation but the only thing tying these events together is when they where logged. ( I wanted to fix this on this side, since we already do other things with this output in the syntax it's in and I'm not looking to open that can of worms. So my token can't come through in the correct syntax)

0 Karma

Although there are several ways to go about this, I'd convert from string format into UNIX time and then back into another string format. Here's a run-anywhere code sample that shows how I'd go from "1/1/18 2:00:20.000 PM" to "2018-01-01T14:00:20.000" Note: your sample had the desired output of a time string with "-06:00" at the end, but I wasn't sure what your intent was with that part.

| stats count
| eval timeformat1="1/1/18 2:00:20.000 PM" 
| eval timeformat2=strftime(strptime(timeformat1, "%x %I:%M:%S.%3Q %p"), "%FT%H:%M:%S.%3Q")

This will take the first field and convert it to UNIX time using the strptime command and then immediately convert it back into a string using the strftime command. The magic here is in the time format strings. You can read more about them here:
https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Commontimeformatvariables

View solution in original post

I'll note here that if you are just looking to convert to the format that the Splunk field _time uses, it's sufficient to just use the strptime portion, like this: | eval timeformat2=strptime(timeformat1, "%x %I:%M:%S.%3Q %p") because Splunk stores values in _time as UNIX epoch values but displays them in a human readable format based on browser/locale settings.

Path Finder

Okay, This makes more sense now. I'm going to give this a try and i'll be back to accept your answer when I get it working. Thanks much.

0 Karma

Absolutely. If this doesn't give you what you were looking for, feel free to post some more details about how it's working (and where it's failing), and I'll try to help more.

Path Finder

What I've got, maybe I went wrong with this but I'm not quite sure on how to finish it.

My token that's passed into this is called "timevalue", which will be an input like i said above (1/1/18 2:00:20.000 PM). This is taken from another dashboard result.

| from datamodel:"Apps"."All_Apps" | search source="Access - Apps - Rule" (This is the dataset I'm searching in)
| eval timeformat1="$timevalue$"
| eval timeformat2=strftime(strptime(timeformat1, "%x %I:%M:%S.%3Q %p"), "%FT%H:%M:%S.%3Q")

What would I add onto this now that I've converted the time I had to the _time value to search it?

I want to search for the events that happened at that exact time. (This is how we're correlating two events to eachother since if they got into this datamodel they're related, but the only data we have to search for what we want is the time value.)

0 Karma

If the events you're searching for will have the exact same timestamp, and it's in a Splunk _time field, I think this should work:

| from datamodel:"Apps"."All_Apps" 
| search source="Access - Apps - Rule" (This is the dataset I'm searching in)
| eval time_match=strptime("$timevalue$" , "%x %I:%M:%S.%3Q %p")
| where time_match=_time

Path Finder

The timestamp is close but _time (2018-01-01T14:00:20.000-06:00) has the -06:00 on it which isn't getting translated over from the date the token provides (not sure what this part of the timestamp is called). I think this is what's causing me from being able to simply do this as your last suggestion shows and do a direct match.

Could I wildcard this in some way to get around that? If I could figure out a way to search for events around that time (5min) that would probably be enough for us. This is a pretty small dataset.

0 Karma

Ohh, I know what that is. It's a timezone adjuster. If it's always "-06:00", then this could work:

| from datamodel:"Apps"."All_Apps" 
| search source="Access - Apps - Rule"
| eval timestring=strftime(strptime("$timevalue$", "%x %I:%M:%S.%3Q %p"), "%FT%H:%M:%S.%3Q")."-06:00"

You should be able to use the field timestring to compare.

Now, if you have events coming from different places (so the "-06:00" isn't a static value, then it's a bit harder.

0 Karma

I'm a little out of my depth here, so forgive me for a little fumbling. Looks like %z might also be an option, since that's the timezone formatter. So how about this:

| from datamodel:"Apps"."All_Apps" 
| search source="Access - Apps - Rule"
| eval timestring=strftime(strptime("$timevalue$", "%x %I:%M:%S.%3Q %p"), "%FT%H:%M:%S.%3Q%:z")

Path Finder

It's alright, you're helping me get much farther then just googling to solve this.

So I've been able to confirm that the timestring gets listen into the fields on the search now and matches for the events that i wanted it to pull up. But when I run a | where timestring=_time I get nothing back as a match. But if I visually look at the two fields they're the same _time and timestring. So I'm not sure why that would be a failing search. I feel like this should be what I need now. Maybe I have a space or something wrong?

Edit:

timestringfield
2018-01-10T15:04:25.000-06:00

_time
2018-01-10T15:04:25.000-06:00

Why isn't the where timestringfield=_time search working? They match on the events I want.

edit 2:

Got it working. Thanks for all the help, just had to combine several of your answers together for it.

| from datamodel:"Apps"."All_Apps"
| search source="Access - Apps - Rule"
| eval timestring=strptime("1/10/18 3:04:25.000 PM" , "%x %I:%M:%S.%3Q %p")
| where timestring=_time

Thanks soo much.

0 Karma

Glad it came together for you!

The value you're seeing for _time is presented in human-readable format, but it's stored as a UNIX epoch string. So this might work:

...| eval source_time=strftime(_time, "%FT%H:%M:%S.%3Q%:z")
| where source_time=timestringfield

This will convert the value stored in _time to a string format, which will allow the where command to actually compare them as strings.

Now, I am totally certain that there's a more efficient way to do all this, but I'd have to look at some of your raw data to be sure. 🙂

0 Karma