Hi. I am indexing data from a ticketing tool.

[aorkcreate]

I need to see what tickets were opened at end of each month. I've done a initial charge of the database, because of this, I can't use the _time indexed, otherwise I have to use open_date and close_date. Basically, the logic that I need to apply is: Make a count of all tickets that were opened before end of month and were closed after the end of that month. I need show like timechart with this info by month. Any idea about the way to get this info? Maybe could be useful the gentimes command?

[andreacorvini]

An example:

index=your_index sourcetype=your_sourcetype source=your_source
| dedup your_incident_unique_key
| eval _time=strptime(open_date,"%Y-%m-%d %H:%M:%S")
| bucket _time span=1mon
| stats count by _time

[aorkcreate]

the command that you given does work but I need a trend line of how many open this month and how many open last month and sooo on

[aorkcreate]

I need that comparing trend line ,no of open by the end of each month .

example :- if a 5 tickets are open by end of January then it needs to append with with feb data but if 2 of January tickets is closed in feb then it should not show in trend line of feb but should show in jan and too on
.

[andreacorvini]

I'm not sure I understand what you want.
Statistics of closed tickets "| append" statistics of tickets that are still open?
If you want to see all the tickets opened as if they were open in the current month, overwrite the opening date with eval....
But your goal is not clear to me.

[poete]

Hello @aorkcreate,

can you please share a sample of the data you are working with?

[aorkcreate]

This is my sample data :-
{
"Application": "",
"Data Source Status": "open",
"Days Open": "0",
"Director": “abcd”,
"Director ID": “12345”,
"Director Username": “dcbd”,
"Last Updated": "8/6/2018 9:00:16 AM",
"Number of Days Past Due": "-30",
"Reason for Closure": "",
"Request URL": “https://abcd.com”,
"Required Remediation Date": "9/5/2018",
"Source": “with”,
"Status": "Open",
"Threat Level": "High",
"Unit CIO": "",
"Vector ID": “123456789”,
"Vector Status": "Valid",
"Vector Status Justification": "",
"Vulnerability Closed Date": "",
"Vulnerability ID": “with-123-456”,
"Vulnerability Open Date": "8/6/2018",
"Vulnerability Risk": "High",
"WAVM Hosting Location": "External",
"WAVM Inventory Application(s)": “1234-abcde-1234”,
"With Vulnerability ID": "51817015"
}

[aorkcreate]

I need that comparing trend line ,no of open by the end of each month .

example :- if a 5 tickets are open by end of January then it needs to append with with feb data but if 2 of January tickets is closed in feb then it should not show in trend line of feb but should show in jan and too on
.