Getting Data In

How to create a line chart without timestamp field?

Engager

Hi,
I have to compare a search and a List.csv, so I did the following search and all works well:

alt text

The problem is that I have to create a line chart about this count, but I cannot use the timechart as usual because in the result the timestamp field is not valorized.
Does anybody know if it is possible (and eventually how) to use the _time value to find out a solution?

Hoping to have been clear enough, I thank you in advance.

Regards.

0 Karma

Esteemed Legend

First of all, don't use pictures; post the text. Second, when you say linechart what are going to be on the X-axis and Y-axis? You have to spell it all out if you'd like us to be able to help.

0 Karma

Builder

I believe the issue is that when you filter out the systems that have checked in, you are left with the systems from the CSV whose "events" only have the timestamp from the file.

You need to add a current _time value to your lookup events, with a bf_value of 0.
This will ensure it gets added to the timechart, you can fillnull to fill in the hourly entries for these non-reporting systems with 0.

Now you untable your results, so you can search for the hosts with a from_base value of 0

index=serverlist | eval from_base=1
 |  append 
     [ 
     | inputlookup servers.csv | eval _time=now() 
     |  eval from_base=0  ]
 | timechart span=1h sum(from_base) as from_base by host
 | fillnull value=0
 | untable _time host from_base 
 | where from_base=0
0 Karma

Communicator

@mik990 using the search as told by solarboyz1 , | bin span=15m your_time_field
| chart count over your_time_field by hostname, in the vizualization tab you can pick line chart and you'll get it as per the _time

0 Karma

Engager

Hi @snigdhasaxena and thanks for your reply, but if I add to my search i get "No result found" , i can't understand if there is something wrong.

Thank you.

0 Karma

Champion

hi @snigdhasaxena and @mik990
No just performing a chart by _time won't work, the _time will always be the same in this case , the value of the CSV update and that is @mik990 's issue.
I still donot understand what kind of a line chart is needed, if you apply a stats or a timechart here all you will get is one single point and yet mik needs a line chart, so I am not able to understand what is needed to be honest.

0 Karma

Engager

Hi @Sukisen1981 , if I do different searches in different period i receive different results, so basically i need the trend of these results, something like a refresh of the situation but in a line chart, an "history" of results.

Thanks

0 Karma

Champion

so do you have an earlier timestamp than 9:54:30 or the latest run in the Time column?
If no, then it is difficult to fathom from where you propose to pick up the 'history' runs.
Is this doable? Yes, but then we need to delve into the saved search jobs etc. It will be complex
But first, if you run your index query all time, do you just see the current(9:54:30) timestamp or the previous runs as well?

0 Karma

Engager

Yes I have an earlier timestamp, consider that i'm working in a lab and i upload two list of hostname in two different days to test the search(as you can see in the attachement):

https://imgur.com/MSwSFuR

I run the search All time as an example, In production i will run the search about the last month or something like this.

0 Karma

Champion

then you do have a _time filed , that is 9:54:30 and 11:48:35
both timechart and bin with stats will work here, what is the issue?

0 Karma

Champion

What is the Time field that is shown your event, is it not _time?
assuming it is a field called 'x'
| makeresults
| eval x="8/27/19 9:54:30:000 AM"
| eval x=strptime(x,"%m/%d/%y %I:%M:%S")
| eval _time=x

after that you can do what you want, apply timchart or stats bin...

0 Karma

Engager

Hi @Sukisen1981 and thank you, can't i appy the timechart directly to the result?
Like this way:

my search | timechart span="my period" count by _time

or something like this?

Thanks

0 Karma

Champion

you can but you started the thread saying that you are not able to apply timechart 🙂
so i guessed that maybe for some reason you were not able to get _time
try a straightforward tiemchart first and observe the outputs, if it works then there is no need to use my query and get into epoch conversions

0 Karma

Engager

Yes I cannot use timechart with the first search because in the result the _time is not present, so i change my search this way:

![alt text][1]
https://imgur.com/bM3hlUx

i'm pretty sure that this is the right way to find the correct solution, my question is, why if i add a where clause like "| where count=1 AND isnull(from_base)" i receive no results? I'm confused, both conditions are met in the result that i have.

Sorry if I am logorroic, it is the first time that I work with splunk, I come from Arcsight experience so i'm not very well prepared.

Thanks

0 Karma

Champion

hi @mik990
No need to apologies, I don't know A or arcsight and would probably be very stupid on those forums.
I believe you are close/almost correct, but once again I can't see the image..imgur 🙂 ?

0 Karma

Engager
0 Karma

Champion

hi mik@990
Your timechart look right to me, if it is the data you expect then you are done with this issue!
please confirm

0 Karma

Engager

I need to exctract only the count of the day "where count=1 AND isnull(from_base)" but when i add this clause to the query i receive no result found, this is very strange.

Thanks

0 Karma

Champion

hi @mik990
how did you add this clause? "where count=1 AND isnull(from_base)"
Are you referring to specific counts a & base combinations?Like count centos special and from_base centos special? or are you saying the sum of ALL(or ANY) the counts=1 (that is all columns having count as part of text in header) and ALL(or ANY) the from_base is null?
you might need to untable like @solarboyz1 suggetsed, give that a try as well

0 Karma

Builder

If you have another field containing the time, you can use chart:

| chart count over your_time_field by hostname

However, this does will not group the events by the hour, day, etc.. it will create a value for each unique timestamp in that field.
the bin command can do that:

| bin span=15m your_time_field
| chart count over your_time_field by hostname

As long as your time field is recognized as a time. IF not you may need to use strptime/strftime to get it to a recognized time.

0 Karma

Engager

Thank you solarboyz,
i don't have a timestamp field in my csv file, as i told to Sukisen i need a line chart that shows the result of the query every x amount of time, rather simple as a concept but struggling to get the result.
In the following pic you can see the logs that i receive as a result, without a timestamp field:

alt text

could i use the Time field somehow to reach the goal?

Thank you.

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes and swag!