Adding data from multiple fields into a new field

[shiv1593]

Hi All,

Out of the many data fields, I have three fields "Created Time", "Number" and "Priority" (Image below). What I want to do is

1> Create three new separate fields named "Morning" where I want the timings between 7:15 AM-3:45 PM, "Afternoon" Where the time is from 3:50 PM to 11 PM and "Night" where the time is from 11:03 PM to 7 AM. (Picture Below)

2> Then count the number of tickets corresponding to those time periods and display their count under those three groups accordingly (Like in the picture below)

How can I do the same.

Thanks in advance.

[493669 · Accepted solution]

Hi @shiv1593,
Run below query... I have tested it

<your current search giving fields "Created Time", "Number" and "Priority" >
 | rename COMMENT as "Converting Created Time values to number of seconds past midnight"
 | eval CTime=strptime('Created Time',"%I:%M:%S %p")-relative_time(now(),"@d")
 | eval Period=case(CTime>=26100 AND CTime<56700,"Morning", CTime>=57000 AND CTime<82800,"Afternoon", true(),"Night")
 | chart count over Period by Priority

Hope this helps you..

[shiv1593]

Hello,

That is working like a charm. Thank you

Two quick questions.

1. I want to understand how the query worked. Can you explain me these two codes

   | eval CTime=strptime('Created Time',"%I:%M:%S %p")-relative_time(now(),"@d")

   | eval Period=case(CTime>=26100 AND CTime<56700,"Morning", CTime>=57000 AND CTime<82800,"Afternoon", true(),"Night")

2. Can you please tell me how did you calculate these values? 26100,56700,57000 and 82800

Thanks

[493669]

1. strptime command is used to convert timestamp into epoch(in seconds) so here 'Created Time' is converted into seconds then relative_time command here will determines the UNIX time value of the start of today, based on the value of now() i.e. it will bring today's time to today's starting time(12:00 AM) and convert into epoch
   So, after substraction cTime will contain today's time in seconds.
   refer http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/DateandTimeFunctions

2. 26100---> as you mentioned Morning time should be in between 7.15 am and 3:45 PM ..so I have converted time into seconds as (7*60*60)+(15*60)=26100 similarly calculated other time into seconds..
   Then checked if cTime is falls between which conditions and as per assign period.

Hope this helps you..Let me know in case of any query

[shiv1593]

Thanks a lot! It really has helped me a lot.

[somesoni2]

Try like this

your current search giving fields "Created Time", "Number" and "Priority" 
| rename COMMENT as "Converting Created Time values to number of seconds past midnight"
| eval CTime=strptime('Created Time',"%H:%M:%S %p")-relative_time(now(),"@d")
| eval Period=case(CTime>=26100 AND CTime<56700,"Morning", CTime>=57000 AND CTime<82800,"Afternoon", true(),"Night")
| chart count over Period by Priority

[shiv1593]

Hello,

I tried it. It is giving just the values for Morning and Night.

        Period    Priority 2    Priority 3  Priority 4
    1   Morning   177           6204            32
    2   Night     168           6272            24

[somesoni2]

Try this

your current search giving fields "Created Time", "Number" and "Priority" 
 | eval CTime=strptime('Created Time',"%H:%M:%S %p")
 | eval Period=case(CTime>=strptime("7:15 AM","%H:%M %p") AND CTime<strptime("3:45 PM","%H:%M %p"),"Morning", CTime>=strptime("3:50 PM","%H:%M %p")AND CTime<strptime("11:00 PM","%H:%M %p"),"Afternoon", true(),"Night")
 | chart count over Period by Priority

[shiv1593]

Tried it. It is now giving values of Afternoon and Night:

    Period         Priority 2        Priority 3   Priority 4
1   Afternoon     233                7592             20
2   Night          112               4884             36