Splunk Search

How do you search using a specific value found in a CSV column?

beetlegeuse
Path Finder

I am trying to put together a search that will incorporate two fields used in a CSV file ("RoleInstance" and "Environment") and find specific Windows metrics that are kept in an environment specific index.

The CSV file would look something like this:

    |------------------|-------------------|
    |RoleInstance      | Environment       |
    |------------------|-------------------|
    |    abc           |    Dev            |
    |    def           |    Dev            |
    |    ghi           |    Prod           |
    |    jkl           |    Sandbox        |
    |------------------|-------------------|

The search would need to look for all the hosts listed in the CSV file that matched a specific environment that's also listed in the CSV file.

So, for example, my base search for the Dev environment would be something like this:

    index=DevMetrics source="WindowsMetrics" CounterName="\\LogicalDisk(_Total)\\Free Megabytes" | table RoleInstance CounterName Average

Using this example and the table I provided, the expectation would be for the results to show the metrics for hosts "abc" and "def" only (as they belong to the "Dev" environment).

How would I go about accomplishing this? I know that I'll need to use something like an "inputlookup" to read the CSV records in, but I'm scratching my head with respect to the rest of the logic.

0 Karma

triest
Communicator

There are lots of ways to accomplish this.

The quickest way would be to make a lookup with your csv. If we then do:

| inputlookup | search Environment=Dev | fields RoleInstance | rename RoleInstance as host

We should get one column called host with two rows: abc and def

I'm assuming that in your base search, the name of the systems is in the host field, so that's why I renamed RoleInstance as host.

You can then turn that search into a subsearch by putting it between [ ] What that will automatically do is change the results into (essentially, but not exactly): ( host=abc OR host=def)

We can then do our base search

 index=DevMetrics source="WindowsMetrics" CounterName="\\LogicalDisk(_Total)\\Free Megabytes" [ | inputlookup | search Environment=Dev | fields RoleInstance | rename RoleInstance as host ]
| table RoleInstance CounterName Average

which becomes:

 index=DevMetrics source="WindowsMetrics" CounterName="\\LogicalDisk(_Total)\\Free Megabytes" ( host=abc OR host=def )
| table RoleInstance CounterName Average

The advantage is you just need to update the lookup and you can add or remove hosts and you can use that lookup in a number of searches.

The caveat is I am typing this from basic memory so there might be slight typos; I typically like to simulate data, but in this case I don't have the time right this second, so hopefully this gets you closer to your goal.

Long term, you could do an automatic lookup on the data so you can do a search like:

index=DevMetrics source="WindowsMetrics" CounterName="\\LogicalDisk(_Total)\\Free Megabytes"  Environment=Dev

Or you could add an event type or tag to denote the different environments.

0 Karma

beetlegeuse
Path Finder

Thank you for your response; I will try this out. The RoleInstance value in the lookup table may vary slightly from the RoleInstance value that's part of the source. For example: server "abc" in the lookup table may be known as "abc.com" in the source. I understand that using the square brackets around the subsearch implies an exact match (i.e. host=abc). How would I adjust this to account for the server having a FQDN? Can a wildcard be used somehow?

0 Karma

triest
Communicator

You're exactly right that you would introduce a wild card.

So the short answer is try:
index=DevMetrics source="WindowsMetrics" CounterName="\LogicalDisk(_Total)\Free Megabytes"
[
| inputlookup
| search Environment=Dev
| fields RoleInstance
| rename RoleInstance as host
| eval host=host."*"
]
| table host CounterName Average

Basically its just a simple eval inside the sub-search that adds a * to the end. Following the above logic of how this works that search turns into:

 index=DevMetrics source="WindowsMetrics" CounterName="\\LogicalDisk(_Total)\\Free Megabytes" **( host=abc* OR host=def* )**
 | table host CounterName Average

Assuming your familiar with Splunk searching you should then be comfortable saying it will match a FQDN like abc.example.com The obvious pitfall is if you have a production server like www and a development server like wwwdev, then this isn't going to work well was when searching for production www* will also match wwwdev

0 Karma

lakshman239
SplunkTrust
SplunkTrust

Did you try your base search | inputlookup <yourfilename.csv> RoleInstance OUTPUT Environment | search Environment=="Dev"

this will give you matched records between your events and lookup file and output env and filter only Dev

0 Karma

beetlegeuse
Path Finder

I will try this out. The RoleInstance in the lookup table may not be an exact match to the RoleInstance that's part of the source. In other words, server "abc" in the lookup table may be identified as "abc.com" in the source. How would I configure the search to account for this? Can I use a wildcard someplace?

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