topic Re: How can i set different timeline for my search? in Splunk Search

How can i set different timeline for my search?

tamduong16 — Tue, 29 Sep 2020 17:15:42 GMT

I have the following search:

index="monthlycdr" "Call Duration"=* Name=\"***\" | eval "Call Duration"=replace('Call Duration',"\"","") | convert dur2sec("Call Duration") as "CDinsec" | eval "Name"=replace('Name',"\"","")

| eval "transporttype"=replace('Transport Type',"\"","") | eval "Voice_count"=case( match(transporttype, "(?i)voice") OR match(transporttype, "(?i)pstn"), CDinsec)
| eval "Video_count" =case(match(transporttype, "^(?i)h323$") OR match(transporttype, "^(?i)sip$"),CDinsec)
| stats avg("Voice_count") as Avg_Voice, avg("Video_count") as Avg_Video by Name

Which only bring up the name of my units within the time frame that I set. But I want to also view other units that haven't been use in the selected time frame. These units would exit in all time. How can I join them in my search? When I do a subsearch for all time, it only displays the name of units within the general time frame that I select. I want to bring up the name of those units that haven't been use as well.

Re: How can i set different timeline for my search?

somesoni2 — Fri, 15 Dec 2017 17:00:09 GMT

What's the full search that you used, which includes your all time subsearch? You should be using earliest=0 in the subsearch to override the timerange just for that subsearch.

Re: How can i set different timeline for my search?

tamduong16 — Tue, 29 Sep 2020 17:15:48 GMT

this is my full search which it only gives me the same result if I run the above search.

index="monthlycdr" "Call Duration"= Name=\"**\" | eval "Call Duration"=replace('Call Duration',"\"","") | convert dur2sec("Call Duration") as "CDinsec" | eval "Name"=replace('Name',"\"","")
| eval "transporttype"=replace('Transport Type',"\"","") | eval "Voice_count"=case( match(transporttype, "(?i)voice") OR match(transporttype, "(?i)pstn"), CDinsec)
| eval "Video_count" =case(match(transporttype, "^(?i)h323$") OR match(transporttype, "^(?i)sip$"),CDinsec)
| stats avg("Voice_count") as Avg_Voice, avg("Video_count") as Avg_Video by Name
| join Name
[search index=monthlycdr earliest=0
| eval "Name"=replace('Name',"\"","")
| table Name]

thanks!

Re: How can i set different timeline for my search?

somesoni2 — Fri, 15 Dec 2017 17:22:45 GMT

Try this

index="monthlycdr" "Call Duration"= Name=\"**\" | eval "Call Duration"=replace('Call Duration',"\"","") | convert dur2sec("Call Duration") as "CDinsec" | eval "Name"=replace('Name',"\"","") 
| eval "transporttype"=replace('Transport Type',"\"","") | eval "Voice_count"=case( match(transporttype, "(?i)voice") OR match(transporttype, "(?i)pstn"), CDinsec) 
| eval "Video_count" =case(match(transporttype, "^(?i)h323$") OR match(transporttype, "^(?i)sip$"),CDinsec) 
| stats avg("Voice_count") as Avg_Voice, avg("Video_count") as Avg_Video by Name
| append [search index=monthlycdr earliest=0 | eval "Name"=replace('Name',"\"","") | stats count by Name | table Name]
| stats values(*) as * by Name

Re: How can i set different timeline for my search?

tamduong16 — Tue, 29 Sep 2020 17:15:53 GMT

@someoni2,
Thanks for the answer. It worked but when I throw the rest of the search with it, I have error like expected AND. Here is the search:

index="monthlycdr" $result0sec$ $result$ | eval "Call Duration"=replace('Call Duration',"\"","") | convert dur2sec("Call Duration") as "CDinsec" | eval "Name"=replace('Name',"\"","")
| eval "Name" = upper(Name)
| eval "transporttype"=replace('Transport Type',"\"","") | eval "Voice_count"=case( match(transporttype, "(?i)voice") OR match(transporttype, "(?i)pstn"), CDinsec)
| eval "Video_count" =case(match(transporttype, "^(?i)h323$$") OR match(transporttype, "^(?i)sip$$"),CDinsec)
| stats avg("Voice_count") as Avg_Voice, avg("Video_count") as Avg_Video, sum("Voice_count") as Sum_Voice, sum("Video_count") as Sum_Video by Name
| eval "Avg_Voice"=tostring($$Avg_Voice$$,"duration") | eval Avg_Voice=if(isnull(Avg_Voice), "00:00:00", replace(Avg_Voice,"(\d+):(\d+):(\d+).(\d+)","\1:\2:\3"))
| eval "Avg_Video"=tostring($$Avg_Video$$,"duration") | eval Avg_Video=if(isnull(Avg_Video), "00:00:00", replace(Avg_Video,"(\d+):(\d+):(\d+).(\d+)","\1:\2:\3"))
| eval "Sum_Voice"=tostring($$Sum_Voice$$,"duration") | eval Sum_Voice=if(isnull(Sum_Voice), "00:00:00", replace(Sum_Voice,"(\d*)+(\d+):(\d+):(\d+)","\1D \2:\3:\4"))
| eval "Sum_Video"=tostring($$Sum_Video$$,"duration") | eval Sum_Video=if(isnull(Sum_Video), "00:00:00", replace(Sum_Video,"(\d)+(\d+):(\d+):(\d+)","\1D \2:\3:\4"))
| append [search index=monthlycdr earliest=0 | eval "Name"=replace('Name',"\"","") | stats count by Name | table Name]
| stats values() as * by Name

For simplicity, I left out the eval portion but I didn't know it would create problem. Sorry!

Re: How can i set different timeline for my search?

tamduong16 — Fri, 15 Dec 2017 17:51:57 GMT

please ignore $result0sec$ $result$. I left them out in the search

Re: How can i set different timeline for my search?

somesoni2 — Fri, 15 Dec 2017 18:48:28 GMT

Does it work find without the subsearch?

Re: How can i set different timeline for my search?

tamduong16 — Fri, 15 Dec 2017 19:13:57 GMT

yes, it works before the subsearch!
It also works with the subsearch if I delete all of the eval expression from the search.

Re: How can i set different timeline for my search?

somesoni2 — Fri, 15 Dec 2017 19:27:13 GMT

In the last stats command, are you missing a asterisk in values function?

Re: How can i set different timeline for my search?

tamduong16 — Fri, 15 Dec 2017 22:09:24 GMT

I just rerun it with the asterisk in values function but have the same result. Thanks again for helping me!