Splunk Search

Plot graph using lookup file

shreyasbsharma
Engager

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? 


Labels (4)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma

shreyasbsharma
Engager

_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?

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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

0 Karma

shreyasbsharma
Engager

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:

shreyasbsharma_0-1696401811443.png

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

shreyasbsharma_1-1696401880597.png

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 ? 

shreyasbsharma_2-1696402051243.png

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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. 

0 Karma

smurf
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...