Hi guys,
I'm currently facing an issue. I have csv logs being ingested every 1 min with the status of some services - either "Running" or "Stopped".
What I want is to show the trend on timeseries chart.
The query I am using is:
index="main" host="xyz_hostname" | where(Service_Name="xyz") | sort by Timestamp desc | dedup Timestamp | dedup Service_Status |reverse | table Timestamp, Service_Status | eval Service_Status=if(Service_Status=="Running", 1, Service_Status), Service_Status=if(Service_Status=="Stopped", 0, Service_Status)
Timeseries visualization works fine when I select a short time range but when I select a range like "last 30 days", I get "These results may be truncated. This visualization is configured to display a maximum of 10000 results per series, and that limit has been reached"
I know I can modify the chart config to accomodate the output put I want to modify my search so that it doesn't takes the points where the status is "Stopped" repeatedly for hours. Inspite if it can only take the timestamp when the status changed from "Running" to "Stopped" or vice-versa.
Can someone help me write such search query?
If you set '_time' as your timestamp, you can then use the timechart which allows you to use span, (or you could use 'bin _time span' but try this first)
index="main" host="xyz_hostname" | where(Service_Name="xyz") | dedup Timestamp |eval _time=strptime(Timestamp, "%Y-%m-%d %H:%M:%S") | eval Service_Status=if(Service_Status=="Running", 1, 0)|timechart count by Service_Status span=5m
You also don't need two if statements. If there are more options than running/stopped then use 'case'
index="main" host="xyz_hostname" | where(Service_Name="xyz") | dedup Timestamp |eval _time=strptime(Timestamp, "%Y-%m-%d %H:%M:%S") | eval Service_Status=case(Service_Status=="Running",1,Service_Status=="Stopped",0, Service_Status="SOMETHINGELSE",3)|timechart count by Service_Status span=5m
Thanks, this does help me but I wanted to have a graph showing just 0 or 1, meaning if the service was running or not. May be I wasn't clear enough in the original question.
Anyway your search query led me to refine more and I am now able to achieve it with:
index="main" host="abc" | where(Service_Name="$clickedServiceName$") | dedup Timestamp
| reverse |eval _time=strptime(Timestamp, "%Y-%m-%d %H:%M:%S") | eval run=if(Service_Status=="Running", 1, 0)
| timechart span=5m min(run) as Min max(run) as Max | eval serviceRunning=(Min+Max)/2 | filldown serviceRunning | fields _time serviceRunning| where serviceRunning != 0.5
is 'Timestamp' an Epoch time? If not, can you post the time format it uses?
The format is "2019-03-26 15:14:02"