graphing by day

[aaronkorn]

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

[yannK]

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

[kristian_kolb]

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