Thank you @cusello ! this is exactly what I needed ... View more

Here's an example from a dashboard I'm trying to replicate in Splunk. ... View more

I'd like to be able to group all of the users sessions, session duration, and when they logged on and off of those sessions in a stats list or values table representation. ... View more

@renjith.nair Thank you! This is the query I'm using right now and I'm hoping you may know a tweak to be able to list each session by a user with that sessions duration time like a stats list or values to group that info index=network sourcetype=cisco:asa (eventype="cisco_vpn_start" OR eventtype="cisco_vpn_end") src_ip=* user=* | stats earliest(_time) as vpn_start,latest(_time) as vpn_end by user, src_ip | eval diff_in_minutes=(vpn_end-vpn_start)/60, duration = round(diff_in_minutes,2) | eval vpn_logon = strftime(vpn_start, "%m/%d/%Y:%H:%M:%S"), vpn_logoff = strftime(vpn_end, "%m/%d/%Y:%H:%M:%S") | strcat duration " mins" session_duration | fields - vpn_start, vpn_end, diff_in_minutes, duration | table user, src_ip, vpn_logon, vpn_logoff, session_duration | sort - _time ... View more

@HiroshiSatoh I'm trying to essentially reproduce the graph below that I have in an application called Mixpanel. I've onboarded the data to Splunk and the field I'm looking to visualization is called "event". In the event there are values called "App Opened" and "Product Checkout Began". I would like to know how you can calculate the % of Product Checkout Began from the total of App Opened and then timechart that % over time. For Example what is my conversion rate % per hour. ... View more

Thank you @Vijeta and @VatsalJagani !! I didn't even know that you could run an eval in a stats command like, that will be def useful to me. Last question if you don't mind.. I'm using the query as a drilldown from my main bar graph panel displaying total purchases by customer and passing in the value.name2 (selectCustomer) to this drilldown panel. My overall goal would be able to group the transactions by customer, _date AND most important broken out by rtl_loc_id aka store index=sales cust_party_id=$selectCustomer$ | stats first(rtl_loc_id) as rtl_loc_id, first(description) as description, sum(quantity) as quantity, first(unit_price) as unit_price by cust_party_id, business_date, item_id | eval unit_price = round(unit_price, 2) | stats list(rtl_loc_id) as Store_ID, list(item_id) as item_id, list(description) as Description, list(quantity) as quantity, list(unit_price) as unit_price, sum(eval(unit_price*quantity)) as Transaction_Total by cust_party_id, business_date | table _date, cust_party_id, Store_ID, item_id, Description, quantity, unit_price, Transaction_Total | sort - quantity | rename unit_price as Sales_Price ... View more

Thanks @VatsalJagani ! This query works for everything except it doesn't like the stats sum(unit_price*quantity) as Transaction_Total by _date, customer I tried to run an eval under the stats eval Transaction_Total = unit_price * quantity and that gives me the calculation total of say for example quantity was 3 items it multiply by the unit price for a single item and returns the sum in Transaction_Total but it is showing line by line and I need to group everything as a single transaction with the transaction_total showing the sum of all of their purchases ... View more

@pyro_wood do you see anything from my recent queries that can be tweaked to get the result I'm looking for? Thank you ... View more

Unfortunately, this is still not working to show the resolved column for just the month and year I'm passing in by tokens. ... View more

I've tested the following query and it looks like it's matching my ticket counts for open and resolved tickets. Any tips of how this can be written from efficiently would be helpful | multisearch [search index=test sourcetype=testUser=* HierarchyLevel=* GroupName="HelpDesk" | eval State=if(Closed!="0" OR Status="Closed" OR Status="*Reject*" OR Status="Abort*","Resolved","Open") | search State="Open"] [ search index=test sourcetype=test User=* GroupName="HelpDesk" | eval State=if(Closed!="0" OR Status="Closed" OR Status="*Reject*" OR Status="Abort*","Resolved","Open") | search State="Resolved"] | dedup Tickets | eval Time=strftime(_time, "%m/%d/%Y %I:%M:%S %p") | rex field=Time "(?<date_month>\d+)/" | rex field=Time "(?<date_year>\d{4})" | append [| lookup datemonth.csv date_month OUTPUT datemonth | search datemonth="August" date_year="2018" State="Resolved"] | lookup datemonth.csv date_month OUTPUT datemonth | strcat datemonth ", " date_year MonthAndYear | chart count by User State ... View more

I think that may be going down the right road but my results are showing Resolved Tickets by User using the "all time" time range using the query above and I'm trying to query only open tickets by user using "all time" but resolved tickets by user using the datemonth and date_year tokens index=test sourcetype="test*" User=* Group="HelpDesk" | dedup Tickets | eval State=if(Closed!="0" OR Status="Closed" OR Status="*Reject*" OR Status="Abort*","Resolved","Open") | eval Time=strftime(_time, "%m/%d/%Y %I:%M:%S %p") | rex field=Time "(?<date_month>\d+)/" | rex field=Time "(?<date_year>\d{4})" | append [| lookup datemonth.csv date_month OUTPUT datemonth | search datemonth=$Month$ date_year=$Year$] | chart count by User State ... View more

Thank you!! ... View more

@renjith.nair I can't get that format to work... My main panel shows fired alerts off a alert that I pipe in a | collect index=alerts so I'm able to save the details of my alerts. The alert timestamp isn't extracted automatically so I'm using a regex to extract to the field named "alert_time" with the format of 07/13/2018 06:19:30 I'm also using the following eval to convert the alert_time to epoch | eval alert_time_epoch = strptime('alert_time', "%m/%d/%Y %H:%M:%S") Here's my test XML trying to use the formatted method you suggested <label>Fired Alerts Drilldown</label> <row> <panel> <table> <title>Drilldown - $host$ Earliest :$formatted_earliest$ , Latest :$formatted_latest$</title> <search> <query>index=alerts source="High CPU Usage &gt; 95% - Infrastructure" | eval alert_time_epoch = strptime('alert_time', "%m/%d/%Y %H:%M:%S") | rename orig_host as host, avg_cpu_used_percent as "Avg % CPU Used" | table alert_time_epoch, alert_time, host, "Avg % CPU Used" | sort - alert_time</query> <earliest>0</earliest> <latest></latest> <refresh>10m</refresh> <refreshType>delay</refreshType> </search> <option name="count">15</option> <option name="dataOverlayMode">none</option> <option name="drilldown">cell</option> <drilldown> <set token="original">$click.value$</set> <eval token="drilldown_earliest">$click.value$-7200</eval> <eval token="drilldown_latest">$click.value$+300</eval> <eval token="host">$row.host$</eval> <set token="showAlertDD">1</set> </drilldown> </table> </panel> </row> <row> <panel> <table depends="$showAlertDD$"> <title>CPU</title> <search> <query>|multisearch [search index=os host=$host$ sourcetype=cpu cpu="all" earliest=$drilldown_earliest$ latest=$drilldown_latest$ |eval cpu_used_percent=100-PercentIdleTime] [search index=perfmon host=$host$ source="Perfmon:CPU" counter="% User Time" OR counter="% Processor Time" earliest=$drilldown_earliest$ latest=$drilldown_latest$ |eval cpu_user_percent=if(counter=="% User Time",round(Value,2),""), cpu_load_percent=if(counter=="% Processor Time",round(Value),""), cpu_used_percent=user_cpu+proc_cpu] |eval Earliest=strftime($drilldown_earliest$,"%m/%d/%Y %H:%M:%S")|eval Latest=strftime($drilldown_latest$,"%m/%d/%Y %H:%M:%S") |eval lhost=upper(host) |bin _time span=10m |stats avg(cpu_user_percent) as user_cpu, avg(cpu_load_percent) as proc_cpu by host, _time |stats avg(user_cpu) as user_cpu, avg(proc_cpu) as proc_cpu, sparkline(avg(user_cpu)) as "% User CPU Load", sparkline(avg(proc_cpu)) as "% Processor CPU Load" by host |eval user_cpu=round(user_cpu,2), proc_cpu=round(proc_cpu,2) |rename user_cpu as "% Average User CPU Load", proc_cpu as "% Average Processor Load" | sort - "% Average User CPU Load"</query> <earliest>$drilldown_earliest$</earliest> <latest>$drilldown_latest$</latest> <done> <set token="formatted_earliest">$result.Earliest$</set> <set token="formatted_latest">$result.Latest$</set> </done> </search> <option name="drilldown">none</option> </table> </panel> ... View more