join between 2 lookups and using a date/time field that is not splunks default

Sfry1981 — Tue, 16 Jul 2019 10:41:39 GMT

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

Re: join between 2 lookups and using a date/time field that is not splunks default

woodcock — Tue, 16 Jul 2019 23:15:25 GMT

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

Re: join between 2 lookups and using a date/time field that is not splunks default

Sfry1981 — Wed, 24 Jul 2019 11:45:14 GMT

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

topic Re: join between 2 lookups and using a date/time field that is not splunks default in Splunk Search

join between 2 lookups and using a date/time field that is not splunks default

Re: join between 2 lookups and using a date/time field that is not splunks default

Re: join between 2 lookups and using a date/time field that is not splunks default