topic Re: Adding data from multiple fields into a new field in Splunk Search

Adding data from multiple fields into a new field

shiv1593 — Wed, 17 Jan 2018 17:00:16 GMT

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.

Re: Adding data from multiple fields into a new field

somesoni2 — Wed, 17 Jan 2018 17:09:30 GMT

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

Re: Adding data from multiple fields into a new field

shiv1593 — Wed, 17 Jan 2018 17:23:29 GMT

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

Re: Adding data from multiple fields into a new field

somesoni2 — Wed, 17 Jan 2018 17:27:48 GMT

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

Re: Adding data from multiple fields into a new field

shiv1593 — Thu, 18 Jan 2018 10:36:34 GMT

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

Re: Adding data from multiple fields into a new field

493669 — Thu, 18 Jan 2018 11:33:20 GMT

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..

Re: Adding data from multiple fields into a new field

shiv1593 — Thu, 18 Jan 2018 11:51:28 GMT

Hello,

That is working like a charm. Thank you

Two quick questions.

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")
Can you please tell me how did you calculate these values? 26100,56700,57000 and 82800

Thanks

Re: Adding data from multiple fields into a new field

493669 — Tue, 29 Sep 2020 17:44:45 GMT

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
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

Re: Adding data from multiple fields into a new field

shiv1593 — Thu, 18 Jan 2018 12:39:05 GMT

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