How to edit my search to create a weekly trend chart for our data?

[amoldesai]

Hi,

We have the following requirement for a weekly trend chart for the data that we get on daily basis (mostly).

1) We need to show end of the weekly period date for labels (Week range is from Sunday to Saturday). That is , we need to have Saturday's date on the label for each historical point
But if today we are on Wednesday, then for the current week, we show Wednesday data as well as Wednesday's date on the label.

2) We need to use the latest data for that week. I have a solution for this using tstats as in the example search below.

To elaborate with an example, consider we receive data on daily basis for the last 3 weeks till today (July 😎 .

Following will be the labels on the chart (ignore the format of date): 18th June, 25th of June, 2nd of July and 8th of July. 8th July is considered since it is the latest in this week.

3) Data to be considered for the 18th June label will be the latest data received between 12th June to 18th June and so on for other dates.

I tried using the following search, but it does not give expected results:

index ="xyz"   earliest=-3w@w6  latest=now sourcetype = cache-v2  [| tstats max(_time) as maxTime WHERE index=xyz source="*xyz_details*" by source _time span=1w | sort -maxTime | stats first(source) as source by _time | fields source]   |   eval Time=_time  | timechart  span=1w dc(psirtColdId)   by matchConfidence |  eval Time=strftime(_time,"%Y:%m:%d") | table Time,"Potentially Vulnerable",Vulnerable

Please let me know how this can be achieved.

Thanks,

-Amol

[amoldesai]

Thanks Somesh for your answer . Somesh/Gregg, here is the output that I get for data uploaded for last 3 weeks till yesterday (11 July) using the above query provided by Somesh

2016:06:25 87 89
2016:07:02 87 89
2016:07:09 88 89
2016:07:16 90 89.

The last date is the problem here. I want the last date to be the date of the last upload data(11 July) and not end of the current week (16th July). The output I need should be as follows :

2016:06:25 87 89
2016:07:02 87 89
2016:07:09 88 89
2016:07:11 90 89.

Thanks

[somesoni2]

Try the updated answer

[woodcock]

I do not understand, will you please elaborate on your 3-weeks example?

What EXACTLY should be the output (give a mockup)?

[somesoni2 · Accepted solution]

How about this

index ="xyz"   earliest=-3w@w6  latest=now sourcetype = cache-v2  [| tstats max(_time) as maxTime WHERE index=xyz source="*xyz_details*" by source _time span=1w | sort -maxTime | stats first(source) as source by _time | fields source]   |   eval Time=_time  | timechart  span=1w dc(psirtColdId)   by matchConfidence  |  eval _time=if(_time=relative_time(now(),"@w"), relative_time(now(),"@d"),_time + (86400*7))  |  eval Time=strftime(_time,"%Y:%m:%d") | table Time,"Potentially Vulnerable",Vulnerable

Also, when you say you don't get the expected result from your search, could you explain what is wrong with the output?

UPdated

index ="xyz"   earliest=-3w@w6  latest=now sourcetype = cache-v2  [| tstats max(_time) as maxTime WHERE index=xyz source="*xyz_details*" by source _time span=1w | sort -maxTime | stats first(source) as source by _time | fields source]   | timechart  span=1w dc(psirtColdId)   by matchConfidence  |  eval _time=_time + (86400*7) | eval _time=if(_time>now(),elative_time(now(),"@d"),_time) |  eval Time=strftime(_time,"%Y:%m:%d") | table Time,"Potentially Vulnerable",Vulnerable

[amoldesai]

Thanks Somesh again for your answer. The output(dates) are as expected now . But we still have an issue here.The source data files referred for calculation in each of the last three weeks is incorrect. Given that data is available for all days in the last three weeks , we need latest source file for that particular week to be used .

Here is the source data file that is presently been referred For following weeks :
June 19th-June 25 : Source file referred is data of 22nd June
June 26th -July 2 : Source file referred is data of 29th June
July3 -July 9 : Source file referred is data of 4th July

It should have referred and used the source data file of June 25,July 2nd and July 9th as we have data for all these days.Do you see an issue with above TSTATS command. Please let me know.

Thanks

[somesoni2]

Try the tstats subsearch below (just run as independent search) and see if it gives correct source

| tstats max(_time) as maxTime WHERE index=xyz source="*xyz_details*" by source _time span=1w | sort -maxTime | dedup _time | fields source

Also, could you provide some sample source values, how they are formatted?

[amoldesai]

Hi Somesh,

Here is the output of the above command:

source _time
/793491/psirt_details.2016071301.gz 2016-07-07
/793491/psirt_details.2016070401.gz 2016-06-30
/793491/psirt_details.2016062901.gz 2016-06-23
/793491/psirt_details.2016062202.gz 2016-06-16

Please note the file name contains the data generated date.
eg: psirt_details.2016071301.gz data file name represents data on 13th of July,2016, the last two digits are insignificant.

If you see the span of 1w considered in the above query, it is relative to todays date. It should be from "Sunday" to "Saturday" (details of week range in my first post). For instance, for the week range (July 3 to July 9), July 9 should be one of the data point(label in the chart) and the source to be considered for this week should be July 9th (the latest one which ever for that week).

Thanks again for your help.

[amoldesai]

Hi Somesh,

After adding earliest=-3w@w7 in the WHERE clause of tstats command, I got the expected result. Here is the complete query for a) Having week range from Sunday to Saturday a) Using the latest source of that week c) For the current week, it considers the latest data source and its date:

index ="XYZ" earliest=-12w@w6 latest=now sourcetype = v2 source="XYZ"
[| tstats max(_time) as maxTime WHERE index=XYZ earliest=-12w@w7 source="XYZ" by source _time span=1w | rex field=source "/(?\d+\/\w+).\d+"| sort -maxTime | stats first(source) as source by _time | fields source] | timechart span=1w dc(Id) by matchConfidence | eval _time=_time + (86400*7) | eval _time=if(_time>now(),relative_time(now(),"@d"),_time) | eval Time=strftime(_time,"%Y:%m:%d") | table Time,"Potentially Vulnerable",Vulnerable

Thanks a lot Somesh for all your help here. Really appreciate it.