Hi,
I am doing an analysis on malware infections in our company, more precisely per department. Working with total number of infections is not very representative, since one department may have more PCs than other departments. So I focus on the infection rate (Infected Assets / TotalAssets)
I have a (more or less) stable list of PCs assigned to each department > assetinfo.csv.
Asset, Department, Location
PC1, Hr, Houston
PC2, Finance, New York
…and a temporary table (updated once a month) with the total count of assets by department > count_of_assets_by_department.csv.
Department Count of Assets
HR, 1000
Finance, 2500
I do have a working search that shows me the infection rate for each department (at least I think it works properly)
| inputlookup count_of_assets_by_department.csv | appendcols [search index=infection | dedup shost | lookup assetinfo.csv Asset as shost OUTPUT Department | stats count as "InfectedAssets" by Department] | eval InfectionRate=(InfectedAssets/TotalAssets)*100 | table Department, TotalAssets, InfectedAssets, InfectionRate
The problem:
The search above is showing once-off results only. I would like to do trending (see below), but I do not know how to achieve this.
Week 36 Week 37 Week 38
HR 2% 3% 4%
Finance 5% 6% 3%
Appreciate your support!
Try this:
| inputlookup count_of_assets_by_department.csv | appendcols [search index=infection | dedup shost | lookup assetinfo.csv Asset as shost OUTPUT Department | stats count as "InfectedAssets" by Department] | eval InfectionRate=(InfectedAssets/TotalAssets)*100 | timechart span=1w list(InfectionRate)
Indeed a typo, but it still does not work. The query does not return results.
If I remove the timechart
piece, I do see results (for the selected timeperiod).
Department InfectedAssets InfectionRate TotalAsset
Finance 10 8,3310 120
HR 7 8,75 80
Doesn't seem to be right. I got the following error:
The specifier 'list' is invalid. It must be in form <func>(<field>). For example: max(size).
The list command is valid. Please check for typos. If it still doesn't work, use ... | timechart span=1w max(InfectionRate)
.
Yes, a timechart would do it.
Does the result have to be a table? Would timechart
do the job?