topic Re: _time not same as data timestamp in Splunk Search

_time not same as data timestamp

mdmaala — Sat, 18 May 2019 01:11:58 GMT

hi! I have to create an area chart where it shows the actual and the target part count of the machine. I am using timechart function to display results every hour, but the _time function is displaying a different time. how can I correct this?

this is my search:

|savedsearch rename1
|fields Date_Time Username Green Yellow Red
|rex field=Date_Time "(?P\d{4}\/\d{2}\/\d{2})\s(?P\d{2}\:\d{2}\:\d{2})" 
|sort 0 _time Username Green Yellow Red
|streamstats window=1 current=f list(_time) as prevTime list(Green) as RUN
|stats count(Green) as Green sum(RUN) as Count1 by Date

|appendcols[|savedsearch rename2
|fields Date_Time Username Green Yellow Red
|rex field=Date_Time "(?P\d{4}\/\d{2}\/\d{2})\s(?P\d{2}\:\d{2}\:\d{2})" 
|sort 0 _time Username Green Yellow Red
|streamstats window=1 current=f list(_time) as prevTime list(Green) as RUN2
|stats count(Green) as Green sum(RUN2) as Count2 by Date]

|appendcols[|savedsearch rename3
|fields Date_Time Username Green Yellow Red
|rex field=Date_Time "(?P\d{4}\/\d{2}\/\d{2})\s(?P\d{2}\:\d{2}\:\d{2})" 
|sort 0 _time Username Green Yellow Red
|streamstats window=1 current=f list(_time) as prevTime list(Green) as RUN3
|stats count(Green) as Green sum(RUN3) as Count3 by Date]

|appendcols[|savedsearch rename4
|fields Date_Time Username Green Yellow Red
|rex field=Date_Time "(?P\d{4}\/\d{2}\/\d{2})\s(?P\d{2}\:\d{2}\:\d{2})" 
|sort 0 _time Username Green Yellow Red
|streamstats window=1 current=f list(_time) as prevTime list(Green) as RUN4
|stats count(Green) as Green sum(RUN4) as Count4 by Date]

|eval PC = Count1+Count2+Count3+Count4
|accum PC
|eval Green = PC*3
|accum Green

|stats values(Green) as Target values(PC) as Actual  by Date

here is the screenshot of my chart

Re: _time not same as data timestamp

DavidHourani — Sat, 18 May 2019 09:50:25 GMT

Hi @mdmaala,

Step one is to simplify your problem. Run only one of the four parts of your search and see if the time displayed is the one from _time. Let me know what you get so we can work this out.

Cheers,
David

Re: _time not same as data timestamp

richgalloway — Sat, 18 May 2019 12:43:43 GMT

You say you're using the timechart function, but I don't see "timechart" anywhere in your query. Please clarify.

Re: _time not same as data timestamp

woodcock — Sat, 18 May 2019 16:32:45 GMT

Try this:

| savedsearch rename1
| fields Date_Time Username Green Yellow Red
| sort 0 _time Username Green Yellow Red
| streamstats window=1 current=f list(_time) AS prevTime list(Green) AS RUN
| eval _time = strptime(Date_Time, "%Y/%m/%d %H:%M:%S")
| bin _time span=1h
| stats count(Green) AS Green sum(RUN) AS Count1 BY _time

| appendcols[|savedsearch rename2
| fields Date_Time Username Green Yellow Red
| sort 0 _time Username Green Yellow Red
| streamstats window=1 current=f list(_time) AS prevTime list(Green) AS RUN2
| eval _time = strptime(Date_Time, "%Y/%m/%d %H:%M:%S")
| bin _time span=1h
| stats count(Green) AS Green sum(RUN2) AS Count2 BY _time]

| appendcols[|savedsearch rename3
| fields Date_Time Username Green Yellow Red
| sort 0 _time Username Green Yellow Red
| streamstats window=1 current=f list(_time) AS prevTime list(Green) AS RUN3
| eval _time = strptime(Date_Time, "%Y/%m/%d %H:%M:%S")
| bin _time span=1h
| stats count(Green) AS Green sum(RUN3) AS Count3 BY _time]

| appendcols[|savedsearch rename4
| fields Date_Time Username Green Yellow Red
| sort 0 _time Username Green Yellow Red
| streamstats window=1 current=f list(_time) AS prevTime list(Green) AS RUN4
| eval _time = strptime(Date_Time, "%Y/%m/%d %H:%M:%S")
| bin _time span=1h
| stats count(Green) AS Green sum(RUN4) AS Count4 BY _time]

| eval PC = Count1+Count2+Count3+Count4

| accum PC
| eval Green = PC*3
| accum Green

| timechart span=1h values(Green) AS Target values(PC) AS Actual

You probably should move the strptime line farther up (but I did not do that because it will change your data).