topic Re: How do you retrieve the number of days from the date field? in Dashboards & Visualizations

How do you retrieve the number of days from the date field?

ramesh12345 — Wed, 20 Feb 2019 15:08:54 GMT

Hi,

index="sh" sourcetype="xml" status=* (Group="Connect" OR Group="health") AND (Section="Site" OR Section="Local") AND (Component="test" OR Component="test1")|dedup _time,Case|where Created_ON=Updated_ON|table _time,Cretaed_ON,Updated_ON

I want a bar graph based on the Group wise trend weekly wise for the last 3 months.

bar should display for sameday(current day cases creation),1-2 days(within 2 days creation),(2-5 days),(5-7 days) like this in x axis
different colors showing different groups.

How do you do this? Please help how to form the query

Re: How do you retrieve the number of days from the date field?

pkeenan87 — Tue, 29 Sep 2020 23:18:51 GMT

You will need to create a calculated field that tells you how many days ago the event occurred and then use the charts command to aggregate on the days_ago field that you have created and the field you would like to split your series. Here is an example using the internal logs where I split by the log_level field.

index=_internal sourcetype=splunkd
| eval days = (Now() - _time) /86400
| eval days_ago = case(days > 5, "5+ days", days < 5 AND days > 3, "3-5 days", days < 3 AND days > 1, "1-3 Days", days < 1, "Less than 1 Day")
| chart count by days_ago log_level

Then click on the visualization tab > format > general and select "stacked" for stack mode

Re: How do you retrieve the number of days from the date field?

ramesh12345 — Thu, 21 Feb 2019 09:33:00 GMT

It's working fine as per my requirement.Thank u very much.

Re: How do you retrieve the number of days from the date field?

ramesh12345 — Tue, 29 Sep 2020 23:24:56 GMT

Hi,

index="os" sourcetype="Service" status=* (Group="Data/Config" OR Group="Secure") AND (Section="Site Problem" OR Section="Local health") AND (Component="connectivity" OR Component="health")|dedup _time,CaseNumber|where Created_ON=Updated_ON| eval days = (Now() - _time) /86400| eval days_ago = case(days <90 AND days > 60, "2-3months",days< 60 AND days > 30, "1-2 months",days< 28 AND days > 14, "2-4 weeks",days< 14 AND days > 7, "1-2 weeks",days< 7 AND days > 5, "5-7days", days < 5 AND days > 2, "2-5 days", days < 2 AND days > 1, "2 Days", days < 1, "Less than 1 Day")| chart count by days_ago,Group|sort days_ago

most cases have both (Group="Data/Config" OR Group="Secure").
initially (Group is "Data/Config") then after some time it change to (Group="Secure").when i count by group,it is showing initial Group name for that case.it is not showing to current group.

i want to display count by group with latest group name(i.e case that have latest group that comes under particular group not previous group)

Re: How do you retrieve the number of days from the date field?

maniu1609 — Wed, 11 Mar 2020 12:46:47 GMT

reltime is the command available to get "The number of hours/days" from the _time field.

https://docs.splunk.com/Documentation/SplunkCloud/8.0.2001/SearchReference/Reltime#Examples