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.
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
@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!
please ignore $result0sec$ $result$. I left them out in the search
Does it work find without the subsearch?
yes, it works before the subsearch!
It also works with the subsearch if I delete all of the eval expression from the search.
In the last stats command, are you missing a asterisk in values function?
I just rerun it with the asterisk in values function but have the same result. Thanks again for helping me!
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.
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!