topic Re: Hi. I am indexing data from a ticketing tool. in All Apps and Add-ons

Hi. I am indexing data from a ticketing tool.

aorkcreate — Tue, 29 Sep 2020 20:49:23 GMT

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?

Re: Hi. I am indexing data from a ticketing tool.

poete — Thu, 09 Aug 2018 07:52:19 GMT

Hello @aorkcreate,

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

Re: Hi. I am indexing data from a ticketing tool.

andreacorvini — Tue, 29 Sep 2020 20:49:39 GMT

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

Re: Hi. I am indexing data from a ticketing tool.

aorkcreate — Thu, 09 Aug 2018 17:50:40 GMT

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

Re: Hi. I am indexing data from a ticketing tool.

aorkcreate — Thu, 09 Aug 2018 17:53:07 GMT

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

Re: Hi. I am indexing data from a ticketing tool.

aorkcreate — Thu, 09 Aug 2018 17:56:28 GMT

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
.

Re: Hi. I am indexing data from a ticketing tool.

aorkcreate — Thu, 09 Aug 2018 17:56:42 GMT

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

Re: Hi. I am indexing data from a ticketing tool.

andreacorvini — Fri, 10 Aug 2018 06:42:30 GMT

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.