About malmiran

malmiran · ‎11-21-2018

Figured it out. It was dumb mistake in my IF. Should have used -NOT (doh!). Anyway, working now perfectly now.

malmiran · ‎11-21-2018

I am using the Splunk add-on for Powershell to run T-SQL files. Sample stanza below: [powershell://GetSQLServerInfo] script=. "$SplunkHome/etc/apps/SQLServerBase_cog-tech-database/bin/GetSQLServerInfo.ps1" disabled = 0 schedule = * */5 * * * * sourcetype = sql_serverinfo index = cog-tech-database-test-nonprod It runs perfectly. However, when I add another "powershell" stanza in the same input.conf file, I get an error. For example: [powershell://GetSQLConfig] script=. "$SplunkHome/etc/apps/SQLServerBase_cog-tech-database/bin/GetSQLConfig.ps1" disabled = 0 schedule = * */5 * * * * sourcetype = sql_config index = cog-tech-database-test-nonprod Error in the splunk-powershell log file: ERROR User script exception: : An item with the same key has already been added. I suspect, it may have something to do with loading the SQLPS module - perhaps Splunk is doing so in the same run space? It's odd because I do have a check (IF CONDITION) in my PS script before I load the SQLPS module to ensure I don't do so if it's already there (see below). if (Get-Module -Name SQLPS -ListAvailable) { if ((Get-ExecutionPolicy) -ne 'Restricted') { Import-Module -Name SQLPS -DisableNameChecking -Verbose:$false } else { Write-Host 'The SQLPS PowerShell module cannot be loaded with an execution policy of restricted' } } Any ideas?

malmiran · ‎11-21-2018

You mean to test? Yeah, I can't impersonate the local service name account (I tried). At any rate, the error I get when I remove the privilege from the SYSTEM account indicates that is what the Splunkforwarder is using. I'll close this thread. For now, I'm OK with granting SYSTEM view access to SQL given the SQL login already exists.

malmiran · ‎11-21-2018

Yep, I did. Definitely not that. 🙂

malmiran · ‎11-18-2018

I am using a Splunk add-on for Powershell to connect to SQL Server and run a T-SQL script stored in a .sql file. (For reasons I don't wanna go into right now, SQL add-on using DBConnect is not an option). Anyway, my Powershell approach works perfectly fine if I grant the NT Service\System sysadmin to SQL Server. Otherwise, I get an error saying it doesn't have the right permission to run the query. For security reasons, I really don't want to grant sysadmin to the local system account so I thought that if I create a login for the service name of the SplunkForwarder (i.e. NT Service\SplunkForwarder), I could then grant just the Splunkforwarder service elevated privs. However, I can't seem to make it work. For some reason, it ignores the permissions I grant the SplunkForwarder service name, and continues to use the security context NT Service\System. Any ideas?

malmiran · ‎08-29-2018

I resorted to using an extracted field (for the alert time) with global perms, and I am now able to get the result I need, still using the JOIN command. | inputlookup "asset-list" | SEARCH PROD_CAT_2="Database" PROD_CAT_3="SQL Server" STATUS="Deployed" | rex field=NAME "(?<NAME>[^\\\]+)" | JOIN type=inner NAME [SEARCH sourcetype="alerts-list" | RENAME Hostname AS NAME] | table AlertName, AlertTime, AlertDescription, ServerName, ServerRole Where "AlertTime" is an extracted field in "alert-list". It feels like a bit of a hack but at this stage, unless folks here tell me there's a major caveat I haven't considered, I'll run with this for now. 🙂

malmiran · ‎08-29-2018

Hi @johnnyfrx . Maybe I just don't know the right way but I can't seem to make it work with append. Also, would append work if my intention is to join the two tables vertically, so I can pull up some of the columns from the secondary table?

malmiran · ‎08-29-2018

Hi @knielsen, not exactly. If I understand it correctly, a subsearch is used to limit the results of your main search using data obtained from a secondary search (your subsearch). However, what I'm trying to do is to literally join 2 distinct tables (sources) - (1) alert-list (my main event data) and (2) asset-list (my source data that contains meta-data about servers (e.g. server impact, role, organization, etc.), so I can report on alerts and the assets metadata. The only commonality between these two sources is the server name field ( in alerts-list and NAME in asset-list) However, there are two caveats I'm struggling with: 1) The values of the Hostname and NAME may not always be the same. The only way i can force the equation is to use my REX extraction above. Because of this necessity, I'm unable to use a simple Lookup command to join the two searches, which is, from my readings, should be the most suitable method given the source of my secondary data (asset-list) is virtually static. 2) The asset-list is actually a Lookup table, so I'm forced to use | inputlookup in my searches, which I find limiting when trying to do some form of joins.

malmiran · ‎08-29-2018

I have this search query: | inputlookup "asset-list" | SEARCH PROD_CAT_2="Database" PROD_CAT_3="SQL Server" STATUS="Deployed" | rex field=NAME "(?<NAME>[^\\\]+)" | JOIN type=inner NAME [SEARCH sourcetype="alerts-list" | RENAME Hostname AS NAME] | table AlertName, _time, AlertDescription, ServerName, ServerRole It's working except the _time values are NULL (empty). The _time is taken from "alert-list". if I use this form, it works (the _time values appear). sourcetype="alerts-list" | eval NAME = Hostname | lookup "asset-list" NAME OUTPUTNEW ServerName ServerRole | search PROD_CAT_2="Database" PROD_CAT_3="SQL Server" STATUS="Deployed" | table AlertName, _time, AlertDescription, ServerName, ServerRole However, I can't use the query above (i.e. using lookup instead of join ) because I need to do a REX against the NAME field in the asset-list that I use as the join/lookup key (NAME=Hostname), and I can't figure out how to do that with something other than using a JOIN. Any ideas on what I"m missing; any other approaches I can try? Thx!

malmiran · ‎08-23-2018

I've been pulling my hair out with this, but after 3 cups of coffee, I think I finally solved it. I used JOIN and switched the sequence of the search. Instead of searching the scom:alertlog first and using the "asset-list" in the lookup (where I needed to apply the REX), I searched the asset-list first, used REX so that the result at that stage already contains the substring, then did the lookup on teh scom:alertlog. | inputlookup "asset-list" | table NAME, STATUS, SYSTEM_ROLE | rex field=NAME "(?[^\]+)" | JOIN type=inner NAME [SEARCH sourcetype="scom:alertlog" | RENAME Hostname AS NAME] | table NAME, STATUS, SYSTEM_ROLE, Name, Description | RENAME Name as "Alert Name" (I guess my journey with Splunk has just started) 🙂

malmiran · ‎08-23-2018

Just to add... Here's my search: sourcetype="scom::alertlog" Hostname="NTHOSTSQL01" | lookup "asset-list" NAME as Hostname OUTPUT STATUS, SYSTEM_ROLE | table Hostname, Name, STATUS, SYSTEM_ROLE | RENAME Name AS "Alert Event" Notes: - The STATUS and SYSTEM_ROLE are those that I want to pull from the "asset-list" lookup definition. - The problem is that sometimes the value in the NAME field of the "asset-list" would be in the format HOST\INSTANCE e.g. (NTHOSTSQL01\INSTANCE01), while the value of the Hostname that matches the record in my events data (scome:alertlog) only has NTHOSTSQL01. - I can extract the Hostname portion in the NAME field using: rex field=NAME "(?[^\]+)". However, I don't know how to incorporate it in my lookup statement.

malmiran · ‎08-23-2018

Thanks Pyro! Hi Pyro/Daj, I just had a chance to try the rex command you gave and it worked in extracting the hostname part of the asset name (i.e. string before the backslash). However, you're right, I seem to still need help with how exactly to integrate in some sort of "join" search to get fields from both my main events data (these are SCOM alerts actually) found on one index, and data from my assets lookup table (it's actually a "lookup definition" - don't know if that matters). I've been playing around with using lookup and join but can't seem to get it to work. I simply can't figure out where in the search sequence do I place the Regex above. I'd appreciate any advice! 🙂

malmiran · ‎08-22-2018

Need to do a lookup using the hostname field from my events data and an asset name from my asset/cmdb data. However, the hostname would be something like Hostname="NTORGDBPXXX" while the asset would be AssetName="NTORGDBPXXX\MXXXCPRD01". So I simply need to extract the string until the backslash "\" (i.e. NTORGDBPXXX) so I can do a lookup using the Hostname=Assetname pair.

Posts	13
Solutions	1
Karma Given	1
Karma Received	1
Member Since	‎08-22-2018

Online Status	Offline
Date Last Visited	‎06-05-2020 02:04 AM

Error using the Powershell-Addon (modular input)

How to Grant SplunkForwarder Service Access to SQL...

Why is _time is NULL when using JOIN?

Extract string to use for lookup

Re: Error using the Powershell-Addon (modular inpu...

Error using the Powershell-Addon (modular input)

Re: How to Grant SplunkForwarder Service Access to...

Re: How to Grant SplunkForwarder Service Access to...

How to Grant SplunkForwarder Service Access to SQL...

Re: Why is _time is NULL when using JOIN?

Re: Why is _time is NULL when using JOIN?

Re: Why is _time is NULL when using JOIN?

Why is _time is NULL when using JOIN?

Re: Extract string to use for lookup

Re: Extract string to use for lookup

Re: Extract string to use for lookup

Extract string to use for lookup

Join the Conversation