Hello,
We have a query that shows the count of tickets and their open and closed times. What we would like to do is show over the last week the total tickets opened per day then show the number of tickets closed per day on the same graph. Is this possible with this search or does something else need changed? I imagine the charting function would be something like this | chart count over dayofweekvariableweset by status
The concern is how would we specify the "dayoftheweekvariable" when we are looking at OPEN_TIME
and CLOSE_TIME
?
| dbquery ServiceCenter limit=1000 "select NUMBER AS 'Ticket',
datediff(day,OPEN_TIME,GETDATE()) AS 'Total Days Open',
STATUS AS Status, ASSIGNMENT AS 'Assignment Group',
OPEN_TIME AS 'Open Time',
datename(weekday,OPEN_TIME) AS Day_Open,
OPENED_BY AS 'Opened By',
UPDATE_TIME AS 'Update Time',
CLOSE_TIME AS 'Close Time',
datename(weekday,CLOSE_TIME) AS Day_Closed,
CLOSED_BY AS Analyst,
BRIEF_DESCRIPTION AS 'Title'\nfrom dbo.PROBSUMMARYM1\nwhere ASSIGNMENT = 'TOOLS TEAM' AND (datediff(day,OPEN_TIME,getdate()) <=7 OR datediff(day,CLOSE_TIME,getdate()) <=7)\nORDER BY 'Total Days Open' desc"
| convert ctime("Update Time") ctime("Open Time") ctime("Close Time") |
In general to use the splunk time grouping, after the dbquery, use an eval to create a field _time
for each events.
You can populate it with an epoch, and splunk use it by default for the timecharts.
example :
<mydbdearch with a field named eventepochtime> | eval _time=eventepochtime | timechart span=1d count
If I understand things correctly, you have your timestamp information in epoch
prior to the final | convert ctime()
, right.
That would mean that you can create new fields from that, just prior to the conversion, e.g. use the field "Open Time"
to find out the day of the week. Adjust to use any other of the fields as needed.
db stuff here | eval wday = strftime("Open Time", "%a") | convert ctime ...
Now you will have a new field called wday
, which will have the values like Mon, Tue, Wed
etc
%a = Mon, Tue, Wed etc
%A = Monday, Tuesday etc
%w = 0 ,1 .. 6
See more options here: http://www.strftime.net/
EDIT: should also point to more info on the strftime/strptime
functions of eval
;
http://docs.splunk.com/Documentation/Splunk/5.0.2/SearchReference/CommonEvalFunctions
Hope this helps,
Kristian