Plot graph using lookup file

[shreyasbsharma]

Hi Team, 

I have a got a request to plot graph of previous 30 days. But the org has a retention period of 7days set on the data set. 

As a solution, I am pushing data from query having HTTP status captured to a lookup file. The CSV file consists of following fields:

1. _time
2. 2xx
3. 4xx
4. 5xx

Also, I have created a time-based lookup definition.

But when I try to plot the graph, "_time" field is not coming up in x-axis.  Can you please help with how this can be achieved? 

[ITWhisperer]

How is the _time field stored in your lookup? If it is a string, then you may need to use the strptime() function to parse it into an epoch time for use in the chart.

[shreyasbsharma]

_time field looks something like "2023-09-06T18:30:00.000+00:00" in the lookup CSV. Whereas in the results generated by the query it looks like "2023-09-06 18:30:00"

I tried converting the _time field as suggested with help of one of solutions provided earlier by you (Solved: Re: convert date to epoch - Splunk Community). But no luck. Can you please help with the query?

[yuanliu]

---

I tried converting the _time field as suggested with help of one of solutions provided earlier by you (Solved: Re: convert date to epoch - Splunk Community). But no luck. Can you please help with the query?

---

Did you consult Date and time format variables when you try that solution?  The solution is provided for that particular format.  In your case, it would be something like 

strptime(_time, "%FT%H:%M:%S.%Q%:z")

_time field looks something like "2023-09-06T18:30:00.000+00:00" in the lookup CSV. Whereas in the results generated by the query it looks like "2023-09-06 18:30:00"

If you have control over this lookup file, rename the _time field to something else like "time" instead.  Splunk does some funny things when it sees _ as the first character of a field name.  This causes more confusion than it is worth.  In your case, Splunk is trying to interpret the field as an internal field and gives its best shot at presentation, but internally, it is still represented as string.  This causes your chart command to not have time axis.  It is best to reserve _fieldname for Splunk's internal use.

[ITWhisperer]

Please share your full search (anonymised as necessary) preferably in  as code block </> to preserve formatting.

[shreyasbsharma]

Thank @yuanliu . I had missed "%FT%" & ":z" when i tried. 

@ITWhisperer Here are few things i have tried till now : 

1. 

| inputlookup 7days_Trail.csv
| eval time=strptime(_time, "%FT%H:%M:%S.%Q:%z")
| table time 2xx 4xx 5xx

After using the above query, the data looks like below:

and the graph looks like where time was not getting updated/populated. 

2. Since _time was not getting populated even after formatting, i used table command directly. Looks like its working. Can you please confirm if I can use this approach ? 

[yuanliu]

You got it backwards.  strptime can get you _time into the real time value so you can use timechart.  Why name the variable time instead of _time?  timechart command only works with field _time.

| inputlookup 7days_Trail.csv
| eval _time=strptime(_time, "%FT%H:%M:%S.%Q:%z")
| timechart avg(*) as *

You can replace avg with any stats function that suits your need. 

[smurf]

Hi,

This might not be the answer you are looking for, but a better practice for your use case would be to use Summary Indexing. You would do basically the same as you do with the lookup but use an index instead. With this, you would be able to search your data as you would any other indexes. 

smurf