Splunk Search

How to edit my search to show the trending Malware Infection Rate in our company, not just one-time results?

Explorer

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 > countofassetsbydepartment.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!

0 Karma

SplunkTrust
SplunkTrust

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)
---
If this reply helps you, an upvote would be appreciated.
0 Karma

Explorer

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
0 Karma

Explorer

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).
0 Karma

SplunkTrust
SplunkTrust

The list command is valid. Please check for typos. If it still doesn't work, use ... | timechart span=1w max(InfectionRate).

---
If this reply helps you, an upvote would be appreciated.
0 Karma

Explorer

Yes, a timechart would do it.

0 Karma

SplunkTrust
SplunkTrust

Does the result have to be a table? Would timechart do the job?

---
If this reply helps you, an upvote would be appreciated.
0 Karma