Time, Duration, not showing up on Dashboard

[harshparikhxlrd]

Those 3 fields are not showing up on my dashboard. I'm not sure if it's just because of a sequence of events. I am doing this by Day. So, I want the totals of all of my information to be totaled up and displayed by day.

Code:
hidden

[harshparikhxlrd · Accepted solution]

I managed to resolve the issue on my own. It ended up being due to how I was extracted the data. I took the "index=monitoring" line, deleted the departments and makes that a rex command. I rex search for department name and then manually input everything else. Test was successful.

[to4kawa]

index=monitoring sourcetype=PEGA:WinEventLog:Application ( SourceName="RoboticLogging" OR SourceName="Application" ) ("Department=" "HRSS_NEO" OR "Department=" "HRSS Daily NEO Report") ("Type=" "Information") 
| bucket _time span=1d 
| rex "Duration:\s*(?<dur1>[0-9:])\.(?<dur2>\d+)" 
| convert dur2sec(dur1) 
| eval duration=exact(dur1+(dur2*.0000001))
| stats sum(duration) as Duration by _time
| eval Duration=tostring(exact(Duration),"duration") 
| table _time, Duration 
| sort 0 - _time
| rename _time as Time
| fieldformat Time = strftime(Time, "%d %b, %Y") 

HI, @harshparikhxlrd
There may be days when there is no tally.
My query also calculates microseconds.

[efavreau]

@harshparikhxlrd I needed to mock up some data to give you a run anywhere example based on your screenshot. So all the lines until the last 5 lines, are to mock up some data. I varied the duration and the timestamps to make it easier to go line by line with the mocked up data, and see the sum of things near the end easier.

There's a lot there. I put an eval comment in there, which will not show in the final result. You can omit this, but it's because the code block in
- Line 15 is where I parse my mock timestamps into real timestamps. You will need to make sure your _time works for your data.
- Line 16 is my regular expression for your duration. In your code, you are excluding the milliseconds. My regular expression, "Duration:(?<duration>[0-9:]+)" doesn't capture the milliseconds.
- Line 17 is where I use the timechart command which gives us the stats by time. However the sum of the durations at this point shows it all in seconds. The time in not formatted the way you want it. But we're close! So the next two lines will adjust that.
- Line 18 is where I use tostring() to format your sums the way you showed in your screenshot.
- Line 19 is where I use strftime to format the time to be exactly as you showed
- Line 20 is to rename the fields as you have in your screenshot!

You can take this example and run it as is to see the result. Attached is a screenshot of a stats table showing timestamps and durations.

| makeresults 
| eval data="_time=2020-01-13 15:15:15,Duration:00:01:03.9286741;
     _time=2020-01-14 15:16:15,Duration:00:01:03.9286741;
     _time=2020-01-14 15:17:15,Duration:00:02:03.9286741;
     _time=2020-01-14 15:18:15,Duration:00:03:03.9286741;
     _time=2020-01-14 15:19:15,Duration:00:04:03.9286741;
     _time=2020-01-16 15:20:15,Duration:00:05:03.9286741;
     _time=2020-01-16 15:21:15,Duration:00:06:03.9286741;
     _time=2020-01-17 15:22:15,Duration:00:07:03.9286741;" 
| makemv data delim=";" 
| mvexpand data 
| rename data as _raw 
| extract pairdelim="," kvdelim="=" 
| eval _time=time 
| eval _time = strptime(_time, "%Y-%m-%d %H:%M:%S")
| rex "Duration:(?<duration>[0-9:]+)"
| timechart span=1d sum(duration) AS duration
| eval duration = tostring(duration, "duration")
| eval _time=strftime(_time, "%d %b, %Y")
| rename _time AS Time, duration AS dur

[jpolvino]

Check your lines 13 and 14. According to the docs, the way you're using it the function "Converts seconds X to the readable time format HH:MM:SS"

Later on, you try to sum dur and avghndl, which is not legal.

Here is something that shows that:

| makeresults
| eval hh=12 | eval mm=22 | eval ss=55
| eval dur = round(((hh * 3600) + (mm * 60) + ss),0)
| eval dur = tostring(dur,"duration")
| where dur != " "
| stats sum(dur)

I don't have a solution to the Time not coming through, but am not familiar with the way you're using strftime in line 15. Can you please explain that, and maybe provide what those field values look like prior to the stats command?

[harshparikhxlrd]

That strftime essentially just puts it in this format: 1/21/2020 at 09:00AM. I also decided to update the post since I thought breaking down the problem might be a little easier.

So, right now, I am simply trying to get one singular time. I am trying to add up all of those durs by day using the stats function. would go something like stats sum(dur) as Duration. But I get a blank result when I try.

[harshparikhxlrd]

Updated post breaking the problem down to just the duration problem. Also added data.

[efavreau]

Let's troubleshoot a little. How many lines of your query can stay in before you don't have the data you need being returned? So assuming your base query is right, I would start with running lines 1-10. Everything good? Then do lines 1-11. Everything good? You sure? Keep going until you identify the line where it's not doing what you want. Double check line by line and see.
Next, let us know which line is not doing what you would expect. Maybe someone else sees something jump out and say "fix this part", but I can't without the data, so I might need this query broken down more.

[harshparikhxlrd]

Updated post breaking the problem down to just the duration problem. Also added data.

[harshparikhxlrd]

Updated the post. Decided to break the problem down to make it a little easier to solve. So, right now, I am simply trying to get one singular time. I am trying to add up all of those durs using the stats function. would go something like stats sum(dur) as Duration. But I get a blank result when I try.