Splunk Search

Adding data from multiple fields into a new field

shiv1593
Communicator

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)

alt text

How can I do the same.

Thanks in advance.

Tags (1)
0 Karma
1 Solution

493669
Super Champion

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

View solution in original post

493669
Super Champion

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
Communicator

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

0 Karma

493669
Super Champion
  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
Communicator

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

0 Karma

somesoni2
Revered Legend

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
Communicator

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

somesoni2
Revered Legend

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

shiv1593
Communicator

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
0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...