I would like to show the comparison of our website's apache log as a chart in my panel. I am able to run the queries independently but can't show the data on one chart. I tried following this:
https://www.splunk.com/blog/2012/02/19/compare-two-time-ranges-in-one-report.html but wasn't able to replicate as there were a few errors. Here's what I've got so far:
Query I have used for chart 1 is:
host="termsandconditionstemplate.com*"
earliest=-24h@h latest=now status = 200
(rel_url="/generate/") OR
(rel_url="/privacy-policy/")
|timechart span=1h count by status
|rename _time as Date
| convert timeformat="%I:%M %p" ctime(Date)
|rename 200 as "Today"
for second chart:
host="termsandconditionstemplate.com*"
earliest=-168h@h latest=-144h@h status = 200
(rel_url="/generate/") OR
(rel_url="/privacy-policy/")
|timechart span=1h count by status
|rename _time as Date
|convert timeformat="%I:%M %p" ctime(Date)
|rename 200 as "Same day, previous week"
Can this be done with append as shown in the
Following is using appendcols. You need to adjust your time range for x axis so that two series align perfectly.
I have used today until current hour (earliest=-0d@d latest=@h) vs Last Week Same Day until the same hour(earliest=-7d@d latest=-7d@h).
index=_internal sourcetype="*access" status=200 earliest=-0d@d latest=@h
| timechart span=1h count as "Today"
| fieldformat _time=strftime(_time,"%I:%M %p")
| table _time Today
| appendcols
[search index=_internal sourcetype="*access" status=200 earliest=-7d@d latest=-7d@h
| timechart span=1h count as "LastWeekSameDay"
| fieldformat _time=strftime(_time,"%I:%M %p")
| table _time LastWeekSameDay]
It is better to use fieldformat on your _time field since you this will retain _time as epoch time while formatting as string time as per your requirement. Appendcol should perform better than append since both search queries are returning similar columns which needs to be appended.
PS: No need to adjust x-axis time with appendcols as far as both time ranges are the same.
You can also explore newly introduced timewarp command (6.5 onward) which allows you to overlay results with timechart
index=_internal sourcetype="*access" status=200 earliest=-7d@d latest=@h
| timechart span=1h count
| timewrap 1d align=now series=exact time_format="%m/%d"
However, in order to apply timewrap command for your use case you would need to create a search in your dashboard to pass on tokens to filter date_mday and date_hour to only 7 days prior and current days (otherwise as you can see this will overlay all days for last 7 days)
Following is using appendcols. You need to adjust your time range for x axis so that two series align perfectly.
I have used today until current hour (earliest=-0d@d latest=@h) vs Last Week Same Day until the same hour(earliest=-7d@d latest=-7d@h).
index=_internal sourcetype="*access" status=200 earliest=-0d@d latest=@h
| timechart span=1h count as "Today"
| fieldformat _time=strftime(_time,"%I:%M %p")
| table _time Today
| appendcols
[search index=_internal sourcetype="*access" status=200 earliest=-7d@d latest=-7d@h
| timechart span=1h count as "LastWeekSameDay"
| fieldformat _time=strftime(_time,"%I:%M %p")
| table _time LastWeekSameDay]
It is better to use fieldformat on your _time field since you this will retain _time as epoch time while formatting as string time as per your requirement. Appendcol should perform better than append since both search queries are returning similar columns which needs to be appended.
PS: No need to adjust x-axis time with appendcols as far as both time ranges are the same.
You can also explore newly introduced timewarp command (6.5 onward) which allows you to overlay results with timechart
index=_internal sourcetype="*access" status=200 earliest=-7d@d latest=@h
| timechart span=1h count
| timewrap 1d align=now series=exact time_format="%m/%d"
However, in order to apply timewrap command for your use case you would need to create a search in your dashboard to pass on tokens to filter date_mday and date_hour to only 7 days prior and current days (otherwise as you can see this will overlay all days for last 7 days)
Thanks for the answer. Works very well without the conditions for rel_url? I only want the results for the two pages: http://termsandconditionstemplate.com/generate/ and http://termsandconditionstemplate.com/privacy-policy-generator/ . Where can I add these conditions? I tried adding these after status but doesn't seem to work that way.
Are the values available in host itself or in some other field like URL? You should always try to filter the results as early as possible for writing a better query. In this case it would be in the base search.
Replace the following in base search host="termsandconditionstemplate.com*"
host="http://termsandconditionstemplate.com/generate/" OR host="http://termsandconditionstemplate.com/privacy-policy-generator/"
Also it would be better if you add index and sourcetype to your base search (even though they are the same for all event). It is like telling Splunk upfront as to where to look for data and what type of data to pull.
Just used | where (rel_url = '/mydirectory/')
Works OK with it. Thanks for the help
is rel_url a derived field or field extracted for your sourcetype data? If it is present in raw event and you have/or can create a field extraction you should move your where condition to base search i.e. besides host you should add rel_url filter as well. or simply mention "/mydirectory/" in your base search to make your search run faster.
You should also remember that sub queries like append and appendcol will tend to drop events beyond limit while correlating, so it is best practice to filter as much as possible in the base search.
hosts is just a machine called termsandconditionstemplate.com, there's another host we switch with sometimes called termsandconditionstemplate.comm. the results can not be filtered by hosts. In my original query, I posted in my question, I am using rel_url field in the log to filtere these two pages.
Is it OK to add it to the same figure?
I think that it will work if you create it with reference to the sample below.
[sample]
index=_internal earliest=-1m@m latest=now | append [ search index=_internal earliest=-3m@m latest=-1m@m | replace with test | eval _time=_time+60 | table _time host] | timechart count by host
Hi Vikas,
Yes, the above task can be achieved using append.
Query:
host="termsandconditionstemplate.com*" earliest=-24h@h latest=now status = 200 (rel_url="/generate/") OR (rel_url="/privacy-policy/")
| eval Myfield="Today"
| append [search host="termsandconditionstemplate.com*" earliest=-168h@h latest=-144h status = 200 (rel_url="/generate/") OR (rel_url="/privacy-policy/")
| eval _time=_time+(604800)
| eval Myfield="Same Data, Last Week" ]
|timechart span=1h count by Myfield
|rename _time as Date
| convert timeformat="%I:%M %p" ctime(Date)
In the Query we are shifting the timestamp of week old data ahead to match the current data. Therefore I have added 604800(3600*24*7) to time of old data.
Also, kindly ensure to use same time duration for both searches to avoid data discrepancy. For eg:
If using -24h@h to now for first query, use -168h@h to -144h(not 144h@h).
Hope This works,
Thanks