I need to create a trend chart between 2 lookups which shows a difference between total closed and total opened. I have written the querys independently which work fine but im struggling to join them together and then on top of that i need to use a date field to gather the totals to make a trend. for total closed i have the below:
| inputlookup test_case | search test_issue_url__c!=null if_parent_case__c="0" type!="test Migration" type!="testimplementation" system_area_1__c!="test1" system_area_1__c!="test2*" closeddate!=null | eval dateclosed=substr(closeddate,1,10) | where dateclosed>"2019-06-01 00:00:00.000" |stats count by dateclosed | sort dateclosed
the below is the total open
| inputlookup test_case | search test_latest_status__c!=Closed test_latest_status__c!=Approved test_latest_status__c!="Cancelled" (test_issue_url__c!="null" OR test_issue_id__c!="null") test_labels__c!="*test1*" test_labels__c!=*test2* test_issue_key__c=ip-* test_latest_status__c!=null system_area_1__c!=testing1 system_area_1__c!=testing2* owner_role__c=*support* if_parent_case__c<=0000001 | eval Product_Type=case(
io_system_area_1__c=="testing3"
OR system_area_1__c=="testing4"
OR system_area_1__c=="testing5"
OR system_area_1__c=="testing6"
OR system_area_1__c=="testing7"
OR system_area_1__c=="testing8"
OR system_area_1__c=="testing9"
OR system_area_1__c=="testing10"
OR system_area_1__c=="testing11"
OR system_area_1__c=="testing12"
OR system_area_1__c=="testing14"
OR system_area_1__c=="testing15"
OR system_area_1__c=="testing16"
OR system_area_1__c=="testing17"
OR system_area_1__c=="testing18"
OR system_area_1__c=="testing19"
OR system_area_1__c=="testing20"
OR system_area_1__c=="testing21"
OR system_area_1__c=="testing22"
OR system_area_1__c=="testing23"
OR system_area_1__c=="testing24","testcolumn"
,system_area_1__c=="testing1","test1", system_area_2__c=="testing2","test2" ) | eval closedtest=case(test_latest_status__c=="Closed","testClosed") | replace "Highest" with "1", High with 2, Medium with 3, Low with 4 in test_priority__c | rename sla_days_remaining__c as "SLA Days Remaining", test_priority__c as "test Priority", test_issue_url__c as "test URL", is_sla_case__c as "Is SLA test", test_issue_Key__c as "test Key" | table subject "test URL" "test Key" "test Priority" "SLA Days Remaining" "Is SLA test" | sort -"Is SLA test" "SLA Days Remaining" "test Priority" | stats count
I need to join them together and add a trend graph based on a date/time column that is not used with splunks time picker and break it down by total per day.
Any help on how i can do this would be appreciated
The trick that you are missing is to do this in a (hidden) base search that is not used anywhere but creates tokens for you like this:
Add this to your existing search:
|makeresults | addinfo
Then store Timepicker
details in tokens with XML like this:
<eval token="earliest">info_min_time</eval>
<eval token="latest">info_max_time</eval>
Then to modify your other data based on another date-ish field (like closeddate
), do this:
|inputlookup | eval _time = strptime(substr(closeddate,1,10), "%your%format%string%here%") | where (_time >= $info_min_time$ AND _time <= $info_max_time$)
Thanks @woodcock
This didnt work for me but i found another simple way to join them together by append but still doesnt give me exactly what i need. Ill place another question on the community for this as its separate to this