Splunk Search

How can I show these two queries over two different time ranges in one chart

vikasT
Explorer

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:
splunk chart

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

0 Karma
1 Solution

niketn
Legend

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)

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

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)

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

vikasT
Explorer

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.

0 Karma

niketn
Legend

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

vikasT
Explorer

Just used | where (rel_url = '/mydirectory/') Works OK with it. Thanks for the help

0 Karma

niketn
Legend

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

vikasT
Explorer

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.

0 Karma

oda
Communicator

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

0 Karma

Honey0308
Explorer

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

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...