Can we use custom time field in timechart to compare fields from multiple queries.

[ravicheepa87]

I am trying to compare Sales data per day for different locations indexed from different sources.

I have 3 different source from where events in below format are getting indexed.

<Date> , <Location>, <Sales> 

I want to plot a comparison graph between sales from different source for particular location.

Currently i am using Union to merge event from different source and then using timechart to plot comparison between sales data. I am able to plot 3 bars(each for a data source) for each location. But _time take here is event indexed timestamp but i want to plot that against <date> field which is there in event itself. How can i do that ? Please suggest.  

[niketn]

@ravicheepa87 you will have to analyze and understand your data. If you Date field in the event should be time of the event and not the index time, then you need to handle that in Timestamp extraction.

Following is the Splunk Documentation that you can refer: https://docs.splunk.com/Documentation/Splunk/latest/Data/Configuretimestamprecognition

Also if the event format and fields are the same across the three different sources you can create a single sourcetype which will identify timestamp, fields and events properly. Which in turn will allow pull data from various indexes (or same index depending on retention, security and volume), based on the sourcetype.

If you can provide some sample events from the three sources, after masking/anonymizing any sensitive information, the community can assist you better with these configurations that will improve search performance and will be better for long run.

[ravicheepa87]

Example: After putting union for searches from 3 different host i am getting below events

9/15/20                  15/09/2020,000123,2328896.34
11:47:45.000 PM  host = host1 AIXsource = /path1/file1.csv = type1.csv

9/15/20                  15/09/2020,000123,453784.34
8:00:00.000 PM    host = host2 AIXsource = /path2/file2.csv = type2.csv

9/15/20                  2020-09-14,123,1378088.93
1:56:41.000 AM   host = host3 AIXsource = /path3/file3.csv = type3.csv

But here now problem is currently _time is the common filed in all 3 searches but if i want to use date all 3 searches to compare on then issue is all 3 date fields have different name and format as they are from different host.  

[niketn]

@ravicheepa87 if your issue is resolved remember to accept the answer to mark this question as answered and assist others facing similar issue.

[ravicheepa87]

@niketn  Issue is not resolved yet. I have shared example events with you as you asked. Please help you got the issue i am facing.

[ITWhisperer]

-- your search
| rex "^(?<day>\d+)/(?<month>\d+)/(?<year>\d+),"
| rex "^(?<year>\d+)-(?<month>\d+)-(?<day>\d+),"
| eval _time=strptime(year."/".month."/".day, "%Y/%m/%d")

[ITWhisperer]

You can override the value of _time by parsing your date field before the timechart command

| eval _time=strptime(date, "...

[thambisetty]

can you share sample event with Date? I will give you query to pass Date value to _time, so that you can plot your chart based on Date values.

[ravicheepa87]

After putting union for searches from 3 different host i am getting below events

9/15/20                  15/09/2020,000123,2328896.34
11:47:45.000 PM  host = host1 AIXsource = /path1/file1.csv = type1.csv

9/15/20                  15/09/2020,000123,453784.34
8:00:00.000 PM    host = host2 AIXsource = /path2/file2.csv = type2.csv

9/15/20                  2020-09-14,123,1378088.93
1:56:41.000 AM   host = host3 AIXsource = /path3/file3.csv = type3.csv