Splunk Search

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

shakermaker
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 > 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!

0 Karma

richgalloway
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, Karma would be appreciated.
0 Karma

shakermaker
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

shakermaker
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

richgalloway
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, Karma would be appreciated.
0 Karma

shakermaker
Explorer

Yes, a timechart would do it.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...